From 12f5fcacd219532e748c24b96c3be143e1fec99f Mon Sep 17 00:00:00 2001 From: Olivier Hallot Date: Sun, 20 Aug 2017 08:23:59 -0300 Subject: tdf#107040 Calc: help for SUBTOTAL indexes 101-111 Change-Id: I90ad83a574748597553bb58c5436343b84947395 Reviewed-on: https://gerrit.libreoffice.org/41359 Reviewed-by: Olivier Hallot Tested-by: Olivier Hallot --- source/text/scalc/01/04060106.xhp | 185 +++++++++++++++++++++++++++++++------- 1 file changed, 155 insertions(+), 30 deletions(-) (limited to 'source/text/scalc') diff --git a/source/text/scalc/01/04060106.xhp b/source/text/scalc/01/04060106.xhp index 8c30ead077..f89d93a11b 100644 --- a/source/text/scalc/01/04060106.xhp +++ b/source/text/scalc/01/04060106.xhp @@ -495,7 +495,7 @@ GCD_EXCEL2003 - The result is the greatest common divisor of a list of numbers. +The result is the greatest common divisor of a list of numbers. Syntax @@ -534,7 +534,7 @@ LCM_EXCEL2003 - The result is the lowest common multiple of a list of numbers. +The result is the lowest common multiple of a list of numbers. Syntax @@ -551,8 +551,8 @@ number of combinations mw added one entry - -should be statistical-->add a link there +should be statistical-->add a link there + COMBIN Returns the number of combinations for elements without repetition. @@ -573,8 +573,8 @@ number of combinations with repetitions mw added one entry - -should be statistical-->add a link there +should be statistical-->add a link there + COMBINA Returns the number of combinations of a subset of items including repetitions. @@ -604,7 +604,7 @@ TRUNC(Number; Count) Returns Number with at most Count decimal places. Excess decimal places are simply removed, irrespective of sign. TRUNC(Number; 0) behaves as INT(Number) for positive numbers, but effectively rounds towards zero for negative numbers. -The visible decimal places of the result are specified in %PRODUCTNAME - Preferences +The visible decimal places of the result are specified in %PRODUCTNAME - Preferences Tools - Options - %PRODUCTNAME Calc - Calculate. Example @@ -761,7 +761,7 @@ MULTINOMIAL - Returns the factorial of the sum of the arguments divided by the product of the factorials of the arguments. +Returns the factorial of the sum of the arguments divided by the product of the factorials of the arguments. Syntax MULTINOMIAL(Number(s)) @@ -913,8 +913,9 @@
- +
+
ROUND function @@ -1081,7 +1082,8 @@ Example assumption: You have entered invoices into a table. Column A contains the date value of the invoice, column B the amounts. You want to find a formula that you can use to return the total of all amounts only for a specific month, e.g. only the amount for the period >=2008-01-01 to <2008-02-01. The range with the date values covers A1:A40, the range containing the amounts to be totaled is B1:B40. C1 contains the start date, 2008-01-01, of the invoices to be included and C2 the date, 2008-02-01, that is no longer included. Enter the following formula as an array formula: =SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40) -In order to enter this as an array formula, you must press the Shift+Command+ Ctrl+ Enter keys instead of simply pressing the Enter key to close the formula. The formula will then be shown in the Formula bar enclosed in braces. +In order to enter this as an array formula, you must press the Shift+Command ++ Ctrl+ Enter keys instead of simply pressing the Enter key to close the formula. The formula will then be shown in the Formula bar enclosed in braces. {=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)} The formula is based on the fact that the result of a comparison is 1 if the criterion is met and 0 if it is not met. The individual comparison results will be treated as an array and used in matrix multiplication, and at the end the individual values will be totaled to give the result matrix.
@@ -1112,12 +1114,13 @@
- +
- - + + +
@@ -1178,15 +1181,23 @@ - Function index + Function index + (includes hidden values) + + + Function index + (ignores hidden values) - Function + Function - 1 + 1 + + + 101 AVERAGE @@ -1194,7 +1205,10 @@ - 2 + 2 + + + 102 COUNT @@ -1202,7 +1216,10 @@ - 3 + 3 + + + 103 COUNTA @@ -1210,7 +1227,10 @@ - 4 + 4 + + + 104 MAX @@ -1218,7 +1238,10 @@ - 5 + 5 + + + 105 MIN @@ -1226,7 +1249,10 @@ - 6 + 6 + + + 106 PRODUCT @@ -1234,7 +1260,10 @@ - 7 + 7 + + + 107 STDEV @@ -1242,7 +1271,10 @@ - 8 + 8 + + + 108 STDEVP @@ -1250,7 +1282,10 @@ - 9 + 9 + + + 109 SUM @@ -1258,7 +1293,10 @@ - 10 + 10 + + + 110 VAR @@ -1266,7 +1304,10 @@ - 11 + 11 + + + 111 VARP @@ -1274,11 +1315,94 @@
+Use numbers 1-11 to include manually hidden rows or 101-111 to exclude them; filtered-out cells are always excluded. Range is the range whose cells are included. Example -You have a table in the cell range A1:B5 containing cities in column A and accompanying figures in column B. You have used an AutoFilter so that you only see rows containing the city Hamburg. You want to see the sum of the figures that are displayed; that is, just the subtotal for the filtered rows. In this case the correct formula would be: -=SUBTOTAL(9;B2:B5) +You have a table in the cell range A1:B6 containing a bill of material for 10 students. Row 2 (Pen) is manually hidden. You want to see the sum of the figures that are displayed; that is, just the subtotal for the filtered rows. In this case the correct formula would be: + + + + + + + + A + + + B + + + + + 1 + + + ITEM + + + QUANTITY + + + + + 2 + + + Pen + + + 10 + + + + + 3 + + + Pencil + + + 10 + + + + + 4 + + + Notebook + + + 10 + + + + + 5 + + + Rubber + + + 10 + + + + + 6 + + + Sharpener + + + 10 + + +
+ +=SUBTOTAL(9;B2:B6) returns 50. +=SUBTOTAL(109;B2:B6) returns 40.
@@ -1486,7 +1610,8 @@ Syntax RANDBETWEEN(Bottom; Top) Returns an integer random number between integers Bottom and Top (both inclusive). -This function produces a new random number each time Calc recalculates. To force Calc to recalculate manually press Shift+CommandCtrl+F9. +This function produces a new random number each time Calc recalculates. To force Calc to recalculate manually press Shift+Command +Ctrl+F9. To generate random numbers which never recalculate, copy cells containing this function, and use Edit - Paste Special (with Paste All and Formulas not marked and Numbers marked). Example @@ -1519,4 +1644,4 @@
- + \ No newline at end of file -- cgit