From 9f4e1983f6edae8f5ce8ee2e319410d4e1e39084 Mon Sep 17 00:00:00 2001 From: Olivier Hallot Date: Tue, 26 Mar 2024 13:11:59 -0300 Subject: Refactor help pages for some Calc functions Change-Id: I06a4b2da852cac91b41ac551c4f08ad78b7e3e47 Reviewed-on: https://gerrit.libreoffice.org/c/help/+/165409 Tested-by: Jenkins Reviewed-by: Olivier Hallot --- source/text/scalc/01/ex_data_stat_func.xhp | 77 +++++++++++++++--------------- source/text/scalc/01/func_averageif.xhp | 70 ++++++++++++++------------- source/text/scalc/01/func_averageifs.xhp | 40 ++++++++-------- source/text/scalc/01/func_countifs.xhp | 43 +++++++++-------- source/text/scalc/01/func_filter.xhp | 1 - source/text/scalc/01/func_maxifs.xhp | 41 ++++++++-------- source/text/scalc/01/func_minifs.xhp | 41 ++++++++-------- source/text/scalc/01/func_sumif.xhp | 1 + source/text/scalc/01/func_sumifs.xhp | 1 + 9 files changed, 160 insertions(+), 155 deletions(-) diff --git a/source/text/scalc/01/ex_data_stat_func.xhp b/source/text/scalc/01/ex_data_stat_func.xhp index a05fad017f..fef3ee0545 100644 --- a/source/text/scalc/01/ex_data_stat_func.xhp +++ b/source/text/scalc/01/ex_data_stat_func.xhp @@ -11,135 +11,134 @@ - Examples Dataset for Statistical Functions + Examples Dataset for Statistical Functions /text/scalc/01/ex_data_stat_func.xhp
- Consider the following table - A + A - B + B - C + C - 1 + 1 - Product Name + Product Name - Sales + Sales - Revenue + Revenue - 2 + 2 - pencil + pencil - 20 + 20 - 65 + 65 - 3 + 3 - pen + pen - 35 + 35 - 85 + 85 - 4 + 4 - notebook + notebook - 20 + 20 - 190 + 190 - 5 + 5 - book + book - 17 + 17 - 180 + 180 - 6 + 6 - pencil-case + pencil-case - not + not - not + not
In all examples below, ranges for calculation contain the row #6, which is ignored because it contains text.
-Func_Range; Range1; Criterion[; Range2; Criterion2][; … ; [Range127; Criterion127]] +Func_Range; Range1; Criterion[; Range2; Criterion2][; … ; [Range127; Criterion127]]
The logical relation between criteria can be defined as logical AND (conjunction). In other words, if and only if all given criteria are met, a value from the corresponding cell of the given Func_Range is taken into calculation.
- Func_Range and Range1, Range2... must have the same size, otherwise the function returns err:502 - Invalid argument. + Func_Range and Range1, Range2... must have the same size, otherwise the function returns err:502 - Invalid argument.
- Range1 – required argument. It is a range of cells, a name of a named range, or a label of a column or a row, to which the corresponding criterion is to be applied. + Range1 – required argument. It is a range of cells, a name of a named range, or a label of a column or a row, to which the corresponding criterion is to be applied.
Criterion: A criterion is a single cell Reference, Number or Text. It is used in comparisons with cell contents. A reference to an empty cell is interpreted as the numeric value 0. A matching expression can be: - A Number or Logical value. A matching cell content equals the Number or Logical value. + A Number or Logical value. A matching cell content equals the Number or Logical value. - A value beginning with a comparator (<, <=, =, >, >=, <>). + A value beginning with a comparator (<, <=, =, >, >=, <>). For =, if the value is empty it matches empty cells. For <>, if the value is empty it matches non-empty cells. For <>, if the value is not empty it matches any cell content except the value, including empty cells. @@ -147,16 +146,16 @@ For = and <>, if the value is not empty and can not be interpreted as a Number type or one of its subtypes and the property Search criteria = and <> must apply to whole cells is checked, comparison is against the entire cell contents, if unchecked, comparison is against any subpart of the field that matches the criteria. For = and <>, if the value is not empty and can not be interpreted as a Number type or one of its subtypes applies. - Other Text value. If the property Search criteria = and <> must apply to whole cells is true, the comparison is against the entire cell contents, if false, comparison is against any subpart of the field that matches the criteria. The expression can contain text, numbers, regular expressions or wildcards (if enabled in calculation options). + Other Text value. If the property Search criteria = and <> must apply to whole cells is true, the comparison is against the entire cell contents, if false, comparison is against any subpart of the field that matches the criteria. The expression can contain text, numbers, regular expressions or wildcards (if enabled in calculation options).
- Range2 – Optional. Range2 and all the following mean the same as Range1. - Criterion2 – Optional. Criterion2 and all the following mean the same as Criterion. + Range2 – Optional. Range2 and all the following mean the same as Range1. + Criterion2 – Optional. Criterion2 and all the following mean the same as Criterion.
- The function can have up to 255 arguments, meaning that you can specify 127 criteria ranges and criteria for them. - If a cell contains TRUE, it is treated as 1, if a cell contains FALSE – as 0 (zero). + The function can have up to 255 arguments, meaning that you can specify 127 criteria ranges and criteria for them. + If a cell contains TRUE, it is treated as 1, if a cell contains FALSE – as 0 (zero).
diff --git a/source/text/scalc/01/func_averageif.xhp b/source/text/scalc/01/func_averageif.xhp index a54ef2ec52..9334626f56 100644 --- a/source/text/scalc/01/func_averageif.xhp +++ b/source/text/scalc/01/func_averageif.xhp @@ -11,75 +11,77 @@ - AVERAGEIF function + AVERAGEIF function /text/scalc/01/func_averageif.xhp
- - + + AVERAGEIF function arithmetic mean;satisfying condition

AVERAGEIF function

-Returns the arithmetic mean of all cells in a range that satisfy a given condition. The AVERAGEIF function sums up all the results that match the logical test and divides this sum by the quantity of selected values. +Returns the arithmetic mean of all cells in a range that satisfy a given condition. The AVERAGEIF function sums up all the results that match the logical test and divides this sum by the quantity of selected values.
-AVERAGEIF(Range; Criterion [; Average_Range ]) -Range – required argument. An array, a name of named range or a label of a column or a row containing numbers for averaging or numbers or text for the condition. +AVERAGEIF(Range; Criterion [; Average_Range ]) +Range – required argument. An array, a name of named range or a label of a column or a row containing numbers for averaging or numbers or text for the condition. -Average_Range – optional. It is a range of values for calculating the mean. +Average_Range – optional. It is a range of values for calculating the mean. If the Average_Range is not specified, Range is used for both, the calculation of the mean and the search according to the condition. If Average_Range is specified, the Range is used only for the condition test, while Average_Range is used for the average calculation. - + If a cell in a range of values for calculating the mean is empty or contains text, function AVERAGEIF ignores this cell.
If the whole range is empty, contains only text or all values of the range do not satisfy the condition (or any combination of those), the function returns the #DIV/0! error. +

Simple usage

-=AVERAGEIF(B2:B6;"<35") -Calculates the average for values of the range B2:B6 that are less than 35. Returns 19, because the second row does not participate in the calculation. -=AVERAGEIF(B2:B6;"<"&MAX(B2:B6)) -Calculates the average for values of the same range that are less than the maximum value of this range. Returns 19, because the largest value (the second row) does not participate in the calculation. -=AVERAGEIF(B2:B6;">"&SMALL(B2:B6;1)) -Calculates the average for values of the same range that are greater than the first smallest value of this range. Returns 25, because the first smallest value (the fourth row) does not participate in the calculation. +=AVERAGEIF(B2:B6;"<35") +Calculates the average for values of the range B2:B6 that are less than 35. Returns 19, because the second row does not participate in the calculation. +=AVERAGEIF(B2:B6;"<"&MAX(B2:B6)) +Calculates the average for values of the same range that are less than the maximum value of this range. Returns 19, because the largest value (the second row) does not participate in the calculation. +=AVERAGEIF(B2:B6;">"&SMALL(B2:B6;1)) +Calculates the average for values of the same range that are greater than the first smallest value of this range. Returns 25, because the first smallest value (the fourth row) does not participate in the calculation.

Using the Average_Range

-=AVERAGEIF(B2:B6;"<35";C2:C6) -The function searches what values are less than 35 in the B2:B6 range, and calculates the average of corresponding values from the C2:C6 range. Returns 145, because the second row does not participate in the calculation. -=AVERAGEIF(B2:B6;">"&MIN(B2:B6);C2:C6) -The function searches what values from the range B2:B6 are greater than the least value in the B2:B6 range, and calculates the average of corresponding values from the C2:C6 range. Returns 113.3, because the fourth row (where there is the least value in the range B2:B6) does not participate in the calculation. -=AVERAGEIF(B2:B6;"<"&LARGE(B2:B6;2);C2:C6) -The function searches what values from the range B2:B6 are less than the second large value in the B2:B6 range, and calculates the average of corresponding values from the C2:C6 range. Returns 180, because only the fourth row participates in the calculation. +=AVERAGEIF(B2:B6;"<35";C2:C6) +The function searches what values are less than 35 in the B2:B6 range, and calculates the average of corresponding values from the C2:C6 range. Returns 145, because the second row does not participate in the calculation. +=AVERAGEIF(B2:B6;">"&MIN(B2:B6);C2:C6) +The function searches what values from the range B2:B6 are greater than the least value in the B2:B6 range, and calculates the average of corresponding values from the C2:C6 range. Returns 113.3, because the fourth row (where there is the least value in the range B2:B6) does not participate in the calculation. +=AVERAGEIF(B2:B6;"<"&LARGE(B2:B6;2);C2:C6) +The function searches what values from the range B2:B6 are less than the second large value in the B2:B6 range, and calculates the average of corresponding values from the C2:C6 range. Returns 180, because only the fourth row participates in the calculation.

Using regular expressions

-=AVERAGEIF(A2:A6;"pen";B2:B6) -The function searches what cells from the range A2:A6 contain only the word “pen”, and calculates the average of corresponding values from the B2:B6 range. Returns 35, because only the second row participates in the calculation. The search is performed in the A2:A6 range, but the values are returned from the B2:B6 range. -=AVERAGEIF(A2:A6;"pen.*";B2:B6) -The function searches what cells from the range A2:A6 begin with “pen” ending with any quantity of other characters, and calculates the average of corresponding values from the B2:B6 range. Returns 27.5, because now also “pencil” satisfies the condition, and both, first and second rows participate in the calculation. -=AVERAGEIF(A2:A6;".*book.*";B2:B6) -The function searches what cells from the range A2:A6 contain “book” starting and ending with any quantity of other characters, and calculates the average of corresponding values from the B2:B6 range. Returns 18.5, because only third and fourth rows participate in the calculation. +=AVERAGEIF(A2:A6;"pen";B2:B6) +The function searches what cells from the range A2:A6 contain only the word “pen”, and calculates the average of corresponding values from the B2:B6 range. Returns 35, because only the second row participates in the calculation. The search is performed in the A2:A6 range, but the values are returned from the B2:B6 range. +=AVERAGEIF(A2:A6;"pen.*";B2:B6) +The function searches what cells from the range A2:A6 begin with “pen” ending with any quantity of other characters, and calculates the average of corresponding values from the B2:B6 range. Returns 27.5, because now also “pencil” satisfies the condition, and both, first and second rows participate in the calculation. +=AVERAGEIF(A2:A6;".*book.*";B2:B6) +The function searches what cells from the range A2:A6 contain “book” starting and ending with any quantity of other characters, and calculates the average of corresponding values from the B2:B6 range. Returns 18.5, because only third and fourth rows participate in the calculation.

Reference to a cell as a criterion

-If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of AVERAGEIF function. -=AVERAGEIF(A2:A6;".*"&E2&".*";B2:B6) -The function searches what cells from the range A2:A6 contain a combination of characters specified in E2 starting and ending with any quantity of other characters, and calculates the average of corresponding values from the B2:B6 range. If E2 = book, the function returns 18.5. -=AVERAGEIF(B2:B6;"<"&E2;C2:C6) -The function searches what cells from the range B2:B6 are less than the value specified in E2, and calculates the average of corresponding values from the C2:C6 range. If E2 = 35, the function returns 145. +If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of AVERAGEIF function. +=AVERAGEIF(A2:A6;".*"&E2&".*";B2:B6) +The function searches what cells from the range A2:A6 contain a combination of characters specified in E2 starting and ending with any quantity of other characters, and calculates the average of corresponding values from the B2:B6 range. If E2 = book, the function returns 18.5. +=AVERAGEIF(B2:B6;"<"&E2;C2:C6) +The function searches what cells from the range B2:B6 are less than the value specified in E2, and calculates the average of corresponding values from the C2:C6 range. If E2 = 35, the function returns 145. +
AVERAGEIF wiki page. - + , , - + , , , , - +
diff --git a/source/text/scalc/01/func_averageifs.xhp b/source/text/scalc/01/func_averageifs.xhp index 5ae669c6d8..0da2629284 100644 --- a/source/text/scalc/01/func_averageifs.xhp +++ b/source/text/scalc/01/func_averageifs.xhp @@ -11,25 +11,25 @@ - AVERAGEIFS function + AVERAGEIFS function /text/scalc/01/func_averageifs.xhp
- - + + AVERAGEIFS function arithmetic mean;satisfying conditions

AVERAGEIFS function

-Returns the arithmetic mean of all cells in a range that satisfy given multiple criteria. The AVERAGEIFS function sums up all the results that match the logical tests and divides this sum by the quantity of selected values. +Returns the arithmetic mean of all cells in a range that satisfy given multiple criteria. The AVERAGEIFS function sums up all the results that match the logical tests and divides this sum by the quantity of selected values.
-AVERAGEIFS() -Func_range – required argument. It is a range of cells, a name of a named range or a label of a column or a row containing values for calculating the mean. +AVERAGEIFS() +Func_range – required argument. It is a range of cells, a name of a named range or a label of a column or a row containing values for calculating the mean. @@ -40,36 +40,36 @@

Simple usage

-=AVERAGEIFS(B2:B6;B2:B6;">=20") -Calculates the average for values of the range B2:B6 that are greater than or equal to 20. Returns 25, because the fifth row does not meet the criterion. -=AVERAGEIFS(C2:C6;B2:B6;">=20";C2:C6;">70") -Calculates the average for values of the range C2:C6 that are greater than 70 and correspond to cells of B2:B6 with values greater than or equal to 20. Returns 137.5, because the second and fifth rows do not meet at least one criterion. +=AVERAGEIFS(B2:B6;B2:B6;">=20") +Calculates the average for values of the range B2:B6 that are greater than or equal to 20. Returns 25, because the fifth row does not meet the criterion. +=AVERAGEIFS(C2:C6;B2:B6;">=20";C2:C6;">70") +Calculates the average for values of the range C2:C6 that are greater than 70 and correspond to cells of B2:B6 with values greater than or equal to 20. Returns 137.5, because the second and fifth rows do not meet at least one criterion.

Using regular expressions and nested functions

-=AVERAGEIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6)) -Calculates the average for values of the range C2:C6 that correspond to all values of the range B2:B6 except its minimum and maximum. Returns 127.5, because the third and fifth rows do not meet at least one criterion. -=AVERAGEIFS(C2:C6;A2:A6;"pen.*";B2:B6;"<"&MAX(B2:B6)) -Calculates the average for values of the range C2:C6 that correspond to all cells of the A2:A6 range starting with "pen" and to all cells of the B2:B6 range except its maximum. Returns 65, because only second row meets all criteria. +=AVERAGEIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6)) +Calculates the average for values of the range C2:C6 that correspond to all values of the range B2:B6 except its minimum and maximum. Returns 127.5, because the third and fifth rows do not meet at least one criterion. +=AVERAGEIFS(C2:C6;A2:A6;"pen.*";B2:B6;"<"&MAX(B2:B6)) +Calculates the average for values of the range C2:C6 that correspond to all cells of the A2:A6 range starting with "pen" and to all cells of the B2:B6 range except its maximum. Returns 65, because only second row meets all criteria.

Reference to a cell as a criterion

-If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of AVERAGEIFS function. For example, the above function can be rewritten as follows: -=AVERAGEIFS(C2:C6;A2:A6;E2&".*";B2:B6;"<"&MAX(B2:B6)) -If E2 = pen, the function returns 65, because the link to the cell is substituted with its content. +If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of AVERAGEIFS function. For example, the above function can be rewritten as follows: +=AVERAGEIFS(C2:C6;A2:A6;E2&".*";B2:B6;"<"&MAX(B2:B6)) +If E2 = pen, the function returns 65, because the link to the cell is substituted with its content.
AVERAGEIFS wiki page. - + , , , - + , , , , - +
diff --git a/source/text/scalc/01/func_countifs.xhp b/source/text/scalc/01/func_countifs.xhp index 5cec8c2aaa..02a0c07151 100644 --- a/source/text/scalc/01/func_countifs.xhp +++ b/source/text/scalc/01/func_countifs.xhp @@ -11,67 +11,68 @@ - COUNTIFS function + COUNTIFS function /text/scalc/01/func_countifs.xhp
- - + + COUNTIFS function counting row;satisfying criteria counting column;satisfying criteria

COUNTIFS

-Returns the count of cells that meet criteria in multiple ranges. +Returns the count of cells that meet criteria in multiple ranges.
-COUNTIFS(Range; Criterion[; Range2; Criterion2][; ... ; [Range127; Criterion127]]) +COUNTIFS(Range; Criterion[; Range2; Criterion2][; ... ; [Range127; Criterion127]]) Range, Range2, ... and Criterion, Criterion2, ... must have the same size, otherwise the function returns err:502 - Invalid argument. +

Simple usage

-=COUNTIFS(B2:B6;">=20") -Counts the amount of rows of the range B2:B6 with values greater than or equal to 20. Returns 3, because the fifth and the sixth rows do not meet the criterion. -=COUNTIFS(B2:B6;">=20";C2:C6;">70") -Counts the amount of rows that contain simultaneously values greater than 70 in the C2:C6 range and values greater than or equal to 20 in the B2:B6 range. Returns 2, because the second, the fifth and the sixth rows do not meet at least one criterion. +=COUNTIFS(B2:B6;">=20") +Counts the amount of rows of the range B2:B6 with values greater than or equal to 20. Returns 3, because the fifth and the sixth rows do not meet the criterion. +=COUNTIFS(B2:B6;">=20";C2:C6;">70") +Counts the amount of rows that contain simultaneously values greater than 70 in the C2:C6 range and values greater than or equal to 20 in the B2:B6 range. Returns 2, because the second, the fifth and the sixth rows do not meet at least one criterion.

Using regular expressions and nested functions

For these examples to work as described, make sure that Enable regular expressions in formulas is selected in %PRODUCTNAME - PreferencesTools - Options - $[officename] Calc - Calculate. -=COUNTIFS(B2:B6;"[:alpha:]*") -Counts the amount of rows of the B2:B6 range that contain only alphabet symbols. Returns 1, because only sixth row meets the criterion. -=COUNTIFS(B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6)) -Counts the amount of rows of the B2:B6 range excluding rows with minimum and maximum values of this range. Returns 2, because the third, the fifth and the sixth rows do not meet at least one criterion. -=COUNTIFS(A2:A6;"pen.*";B2:B6;"<"&MAX(B2:B6)) -Counts the amount of rows that correspond to all cells of the A2:A6 range starting with "pen" and to all cells of the B2:B6 range with exception of its maximum. Returns 1, because only second row meets all criteria. +=COUNTIFS(B2:B6;"[:alpha:]*") +Counts the amount of rows of the B2:B6 range that contain only alphabet symbols. Returns 1, because only sixth row meets the criterion. +=COUNTIFS(B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6)) +Counts the amount of rows of the B2:B6 range excluding rows with minimum and maximum values of this range. Returns 2, because the third, the fifth and the sixth rows do not meet at least one criterion. +=COUNTIFS(A2:A6;"pen.*";B2:B6;"<"&MAX(B2:B6)) +Counts the amount of rows that correspond to all cells of the A2:A6 range starting with "pen" and to all cells of the B2:B6 range with exception of its maximum. Returns 1, because only second row meets all criteria.

Reference to a cell as a criterion

-If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of the COUNTIFS function. For example, the above function can be rewritten as follows: -=COUNTIFS(A2:A6;E2&".*";B2:B6;"<"&MAX(B2:B6)) -If E2 = pen, the function returns 1, because the link to the cell is substituted with its content and it works as a function above. +If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of the COUNTIFS function. For example, the above function can be rewritten as follows: +=COUNTIFS(A2:A6;E2&".*";B2:B6;"<"&MAX(B2:B6)) +If E2 = pen, the function returns 1, because the link to the cell is substituted with its content and it works as a function above.
COUNTIFS wiki page. - + , , , - + , , , , - +
diff --git a/source/text/scalc/01/func_filter.xhp b/source/text/scalc/01/func_filter.xhp index b1e1d53a34..35a6622ab4 100644 --- a/source/text/scalc/01/func_filter.xhp +++ b/source/text/scalc/01/func_filter.xhp @@ -29,7 +29,6 @@ Range: The array or range to filter. Criteria: A boolean array whose height (filtering by columns) or width (filtering by rows) is the same as the array, used to select data from the Range. Return if empty: the value to return if all values in the Include array are empty (filter return nothing). - {=FILTER(A2:C13,A2:A13>50)} returns the array {57,49,12|56,33,60|57,,} containing all grades with Maths grade above 50. Note that this is an array formula. {=FILTER(A2:C13,B2:B13>90,"No results")} Returns the string "No results", since no grade on Physics is above 90. diff --git a/source/text/scalc/01/func_maxifs.xhp b/source/text/scalc/01/func_maxifs.xhp index 79f35edc96..1da5e07ac2 100644 --- a/source/text/scalc/01/func_maxifs.xhp +++ b/source/text/scalc/01/func_maxifs.xhp @@ -11,47 +11,48 @@ - MAXIFS function + MAXIFS function /text/scalc/01/func_maxifs.xhp
- - + + MAXIFS function maximum;satisfying conditions

MAXIFS function

-Returns the maximum of the values of cells in a range that meets multiple criteria in multiple ranges. +Returns the maximum of the values of cells in a range that meets multiple criteria in multiple ranges.
-MAXIFS() +MAXIFS() -Func_Range – required argument. A range of cells, a name of a named range or a label of a column or a row containing values for calculating the maximum. +Func_Range – required argument. A range of cells, a name of a named range or a label of a column or a row containing values for calculating the maximum. +

Simple usage

-=MAXIFS(B2:B6;B2:B6;"<35") -Calculates the maximum of values of the range B2:B6 that are greater than or equal to 20. Returns 35. The fifth row does not meet the criterion. -=MAXIFS(C2:C6;B2:B6;">=20";C2:C6;"<90") -Calculates the maximum of values of the range C2:C6 that are lower than 90 and correspond to cells of the B2:B6 range with values greater than or equal to 20. Returns 85, because the fourth and fifth rows do not meet at least one criterion. +=MAXIFS(B2:B6;B2:B6;"<35") +Calculates the maximum of values of the range B2:B6 that are greater than or equal to 20. Returns 35. The fifth row does not meet the criterion. +=MAXIFS(C2:C6;B2:B6;">=20";C2:C6;"<90") +Calculates the maximum of values of the range C2:C6 that are lower than 90 and correspond to cells of the B2:B6 range with values greater than or equal to 20. Returns 85, because the fourth and fifth rows do not meet at least one criterion.

Using regular expressions and nested functions

-=MAXIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6)) -Calculates the maximum of values of the range C2:C6 that correspond to all values of the range B2:B6 except its minimum and maximum. Returns 190, because only the fourth row meet the criteria. -=MAXIFS(C2:C6;A2:A6;"pen.*";B2:B6;"<="&MAX(B2:B6)) -Calculates the maximum of values of the range C2:C6 that correspond to all cells of the A2:A6 range starting with "pen" and to all cells of the B2:B6 range except its maximum. Returns 85, because only the third row meets all criteria. +=MAXIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6)) +Calculates the maximum of values of the range C2:C6 that correspond to all values of the range B2:B6 except its minimum and maximum. Returns 190, because only the fourth row meet the criteria. +=MAXIFS(C2:C6;A2:A6;"pen.*";B2:B6;"<="&MAX(B2:B6)) +Calculates the maximum of values of the range C2:C6 that correspond to all cells of the A2:A6 range starting with "pen" and to all cells of the B2:B6 range except its maximum. Returns 85, because only the third row meets all criteria.

Reference to a cell as a criterion

-If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of the MAXIFS function. For example, the above function can be rewritten as follows: -=MAXIFS(C2:C6;A2:A6;E2&".*";B2:B6;"<"&MAX(B2:B6)) -If E2 = "pen", the function returns 65, because the reference to the cell is substituted with its content. +If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of the MAXIFS function. For example, the above function can be rewritten as follows: +=MAXIFS(C2:C6;A2:A6;E2&".*";B2:B6;"<"&MAX(B2:B6)) +If E2 = "pen", the function returns 65, because the reference to the cell is substituted with its content. @@ -59,20 +60,20 @@ COM.MICROSOFT.MAXIFS
MAXIFS wiki page. - + , , , , - + , , , , - +
diff --git a/source/text/scalc/01/func_minifs.xhp b/source/text/scalc/01/func_minifs.xhp index 25a95b3e2b..4f0fc46e66 100644 --- a/source/text/scalc/01/func_minifs.xhp +++ b/source/text/scalc/01/func_minifs.xhp @@ -11,47 +11,48 @@ - MINIFS function + MINIFS function /text/scalc/01/func_minifs.xhp
- - + + MINIFS function minimum;satisfying conditions

MINIFS function

-Returns the minimum of the values of cells in a range that meets multiple criteria in multiple ranges. +Returns the minimum of the values of cells in a range that meets multiple criteria in multiple ranges.
-MINIFS() +MINIFS() -Func_Range – required argument. A range of cells, a name of a named range or a label of a column or a row containing values for calculating the minimum. +Func_Range – required argument. A range of cells, a name of a named range or a label of a column or a row containing values for calculating the minimum. +

Simple usage

-=MINIFS(B2:B6;B2:B6;"<35") -Calculates the minimum of values of the range B2:B6 that are lower than or equal to 20. Returns 17. -=MINIFS(C2:C6;B2:B6;">=20";C2:C6;">90") -Calculates the minimum of values of the range C2:C6 that are lower than 90 and correspond to cells of the B2:B6 range with values greater than or equal to 20. Returns 190. +=MINIFS(B2:B6;B2:B6;"<35") +Calculates the minimum of values of the range B2:B6 that are lower than or equal to 20. Returns 17. +=MINIFS(C2:C6;B2:B6;">=20";C2:C6;">90") +Calculates the minimum of values of the range C2:C6 that are lower than 90 and correspond to cells of the B2:B6 range with values greater than or equal to 20. Returns 190.

Using regular expressions and nested functions

-=MINIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6)) -Calculates the minimum of values of the range C2:C6 that correspond to all values of the range B2:B6 except its minimum and maximum. Returns 65. -=MINIFS(C2:C6;A2:A6;".*book";B2:B6;">"&MIN(B2:B6)) -Calculates the minimum of values of the range C2:C6 that correspond to all cells of the A2:A6 range ending with "book" and to all cells of the B2:B6 range except its minimum. Returns 190. +=MINIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6)) +Calculates the minimum of values of the range C2:C6 that correspond to all values of the range B2:B6 except its minimum and maximum. Returns 65. +=MINIFS(C2:C6;A2:A6;".*book";B2:B6;">"&MIN(B2:B6)) +Calculates the minimum of values of the range C2:C6 that correspond to all cells of the A2:A6 range ending with "book" and to all cells of the B2:B6 range except its minimum. Returns 190.

Reference to a cell as a criterion

-If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of the MINIFS function. For example, the above function can be rewritten as follows: -=MINIFS(C2:C6;A2:A6;".*"&E2;B2:B6;"<"&MAX(B2:B6)) -If E2 = "book", the function returns 180, because the reference to the cell is substituted with its content. +If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of the MINIFS function. For example, the above function can be rewritten as follows: +=MINIFS(C2:C6;A2:A6;".*"&E2;B2:B6;"<"&MAX(B2:B6)) +If E2 = "book", the function returns 180, because the reference to the cell is substituted with its content. @@ -59,13 +60,13 @@ COM.MICROSOFT.MINIFS
MINIFS wiki page. - + , , , , - + , , , @@ -73,7 +74,7 @@ - +
diff --git a/source/text/scalc/01/func_sumif.xhp b/source/text/scalc/01/func_sumif.xhp index 5f723e6183..5b8ac7767f 100644 --- a/source/text/scalc/01/func_sumif.xhp +++ b/source/text/scalc/01/func_sumif.xhp @@ -37,6 +37,7 @@ SumRange: Optional. The range of cells from which values are summed. If no SumRange is given, the values in the Range are summed.
+

Simple usage

=SUMIF(C2:C6,">=100") diff --git a/source/text/scalc/01/func_sumifs.xhp b/source/text/scalc/01/func_sumifs.xhp index 039d1d81bc..4e7bc391d3 100644 --- a/source/text/scalc/01/func_sumifs.xhp +++ b/source/text/scalc/01/func_sumifs.xhp @@ -38,6 +38,7 @@ +

Simple usage

-- cgit v1.2.3