|
Help to adapt Formula syntax to work with Visible Filtered Cells
Hi All,
I previously received assistance from Domenic with the great working Formula below BUT can the Formula below be adapted to return the results of Filtered Visible Cells? =SUM(N(OFFSET(A1,LARGE(IF(Cost<"",ROW(Cost)),{1,2 ,3,4,5})-ROW(INDEX(Cost,1)), 0))) ....confirmed with CONTROL+SHIFT+ENTER. The above Formula Sums the LAST 5 numeric values in a single column Dynamic named Range - "Cost" . The column also contains valid zero’s and invalid blanks (empty cells). In Define Name Refers To box "Cost" defined as... =Stock!$R$71:INDEX(Stock!$R$71:$R$65536,MATCH(9.99 999999999999E+307,Stock !$R$71:$R$65536)) Assistance most appreciated. Link to previous Thread: http://www.officekb.com/Uwe/Forum.as...OfficeKB. com Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200509/1 |
First, define the following reference...
Insert Name Define Name: Last5 (or any other name you prefer) Refer to: =LARGE(IF(SUBTOTAL(3,OFFSET(Cost,ROW(Cost)-MIN(ROW(Cost)),0,1)),ROW(Cost) -MIN(ROW(Cost))),{1,2,3,4,5}) Click Ok Then, use the following formula... =SUMPRODUCT(SUBTOTAL(9,OFFSET(Cost,Last5,0,1))) Hope this helps! In article , "Sam via OfficeKB.com" wrote: Hi All, I previously received assistance from Domenic with the great working Formula below BUT can the Formula below be adapted to return the results of Filtered Visible Cells? =SUM(N(OFFSET(A1,LARGE(IF(Cost<"",ROW(Cost)),{1,2 ,3,4,5})-ROW(INDEX(Cost,1)), 0))) ...confirmed with CONTROL+SHIFT+ENTER. The above Formula Sums the LAST 5 numeric values in a single column Dynamic named Range - "Cost" . The column also contains valid zero’s and invalid blanks (empty cells). In Define Name Refers To box "Cost" defined as... =Stock!$R$71:INDEX(Stock!$R$71:$R$65536,MATCH(9.99 999999999999E+307,Stock !$R$71:$R$65536)) Assistance most appreciated. Link to previous Thread: http://www.officekb.com/Uwe/Forum.as...p-to-adapt-For mula-syntax-to-work-with-Dynamic-Named#2faa1ecd66b74b338cdc9c754a29c239%40Offi ceKB.com Thanks Sam |
Hi Domenic,
Thank you very much for solution - Formula works great. Could you possibly help me out with a similar problem... I tried to utilise the Formula you provided with another Function - the the Standard Deviation Population SUBTOTAL Function 108 (STDEVP) - for the Last 5 values in my named range, "Cost" . I thought this might work , but I get zero? =SUMPRODUCT(SUBTOTAL(108,OFFSET(Cost,Last5,0,1))) =SUM(SUBTOTAL(108,OFFSET(Cost,Last5,0,1))) However, this works, but I'm not sure why and not sure why the above Formulas return zero? =STDEVP(SUBTOTAL(109,OFFSET(Cost,Last5,0,1))) ....confirmed with CONTROL+SHIFT+ENTER. Appreciate help. Cheers, Sam Domenic wrote: First, define the following reference... Insert Name Define Name: Last5 (or any other name you prefer) Refer to: =LARGE(IF(SUBTOTAL(3,OFFSET(Cost,ROW(Cost)-MIN(ROW(Cost)),0,1)),ROW(Cost) -MIN(ROW(Cost))),{1,2,3,4,5}) Click Ok Then, use the following formula... =SUMPRODUCT(SUBTOTAL(9,OFFSET(Cost,Last5,0,1))) Hope this helps! Hi All, [quoted text clipped - 23 lines] Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200509/1 |
Define the following reference...
Insert Name Define Name: Last5Values Refers to: =SUBTOTAL(9,OFFSET(Cost,Last5,0,1)) Click Ok Then use the following formula... =STDEVP(Last5Values) ....confirmed with just ENTER. =STDEVP(SUBTOTAL(109,OFFSET(Cost,Last5,0,1))) ...confirmed with CONTROL+SHIFT+ENTER. Actually, I don't have the 'Function Number' 109 available for the SUBTOTAL function in my Mac version of Excel. If I use 9 instead, the formula works fine. Is that a typo on your part or does that 'Function Number' actually exist? In article , "Sam via OfficeKB.com" wrote: Hi Domenic, Thank you very much for solution - Formula works great. Could you possibly help me out with a similar problem... I tried to utilise the Formula you provided with another Function - the the Standard Deviation Population SUBTOTAL Function 108 (STDEVP) - for the Last 5 values in my named range, "Cost" . I thought this might work , but I get zero? =SUMPRODUCT(SUBTOTAL(108,OFFSET(Cost,Last5,0,1))) =SUM(SUBTOTAL(108,OFFSET(Cost,Last5,0,1))) However, this works, but I'm not sure why and not sure why the above Formulas return zero? =STDEVP(SUBTOTAL(109,OFFSET(Cost,Last5,0,1))) ...confirmed with CONTROL+SHIFT+ENTER. Appreciate help. Cheers, Sam Domenic wrote: First, define the following reference... Insert Name Define Name: Last5 (or any other name you prefer) Refer to: =LARGE(IF(SUBTOTAL(3,OFFSET(Cost,ROW(Cost)-MIN(ROW(Cost)),0,1)),ROW(Cost) -MIN(ROW(Cost))),{1,2,3,4,5}) Click Ok Then, use the following formula... =SUMPRODUCT(SUBTOTAL(9,OFFSET(Cost,Last5,0,1))) Hope this helps! Hi All, [quoted text clipped - 23 lines] Thanks Sam |
Hi Domenic,
That's great; thank you. In Excel 2003 for Windows SUBTOTAL Function has: Syntax SUBTOTAL(function_num, ref1, ref2, ...) Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list. Function_num (includes hidden values) Function_num (ignores hidden values) Function 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP Cheers Sam Domenic wrote: Define the following reference... Insert Name Define Name: Last5Values Refers to: =SUBTOTAL(9,OFFSET(Cost,Last5,0,1)) Click Ok Then use the following formula... =STDEVP(Last5Values) ...confirmed with just ENTER. =STDEVP(SUBTOTAL(109,OFFSET(Cost,Last5,0,1))) ...confirmed with CONTROL+SHIFT+ENTER. Actually, I don't have the 'Function Number' 109 available for the SUBTOTAL function in my Mac version of Excel. If I use 9 instead, the formula works fine. Is that a typo on your part or does that 'Function Number' actually exist? Hi Domenic, [quoted text clipped - 44 lines] Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200509/1 |
In article ,
"Sam via OfficeKB.com" wrote: That's great; thank you. You're very welcome! Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list. Interesting! Thanks Sam! By the way, your original formula -- the one that doesn't take into account filtered data -- can be replaced with the following formula which eliminates the volatile function OFFSET... =SUM(INDEX(Cost,LARGE(IF(Cost<"",ROW(Cost)-ROW(INDEX(Cost,1))+1),5)):IND EX(Cost,MATCH(9.99999999999999E+307,Cost))) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Sam via OfficeKB.com" wrote: Hi Domenic, That's great; thank you. In Excel 2003 for Windows SUBTOTAL Function has: Syntax SUBTOTAL(function_num, ref1, ref2, ...) Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list. Function_num (includes hidden values) Function_num (ignores hidden values) Function 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP Cheers Sam |
Hi Domenic,
Thank you for additional advice. Cheers, Sam Domenic wrote: That's great; thank you. You're very welcome! Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list. Interesting! Thanks Sam! By the way, your original formula -- the one that doesn't take into account filtered data -- can be replaced with the following formula which eliminates the volatile function OFFSET... =SUM(INDEX(Cost,LARGE(IF(Cost<"",ROW(Cost)-ROW(INDEX(Cost,1))+1),5)):IND EX(Cost,MATCH(9.99999999999999E+307,Cost))) ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! Hi Domenic, [quoted text clipped - 26 lines] Cheers Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200509/1 |
| All times are GMT +1. The time now is 05:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com