Help to adapt Formula syntax to work with Visible Filtered Cells

[Sam via OfficeKB.com]

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.99999999999999E+307,Stock
!$R$71:$R$65536))

Assistance most appreciated.

Link to previous Thread:
http://www.officekb.com/Uwe/[email protected]


Thanks Sam

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

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

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

 

[Domenic]

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,

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

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

 

[Sam via OfficeKB.com]

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

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]

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you for additional advice.

Cheers,
Sam

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