Help to adapt Formula syntax to work with Visible Filtered Cells

Sam via OfficeKB.com · 09-15-2005, 08:05 AM

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/Forum.as...40OfficeKB.com

Thanks Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1

Domenic · 09-15-2005, 10:05 AM

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 <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> 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.99999999999999E+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

Sam via OfficeKB.com · 09-15-2005, 04:05 PM

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

Domenic · 09-15-2005, 05:05 PM

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 <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> 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

Sam via OfficeKB.com · 09-16-2005, 10:05 AM

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

Domenic · 09-16-2005, 11:05 AM

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> 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 <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> 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

Sam via OfficeKB.com · 09-16-2005, 07:05 PM

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