Calculating with filtered cells

[Jo Davis]

Hi

Can you have a formula that updates when you use a filter?

[bpeltzer]

I'm assuming you want your formulas to consider only the rows that pass the
filter. If so, check out the subtotal function; it honors the filter. Ex
=sum(a:a) will always give you the sum of the entire column.
=subtotal(9,a:a) will total those cells in column A that pass the filter.
See the help on the subtotal function to find the other calculations
available in subtotal (min, max, avg, count, etc).

"Jo Davis" wrote:

> Hi
>
> Can you have a formula that updates when you use a filter?
>
>

[Jo Davis]

Hi

The calculation i need is a st dev will this still be honoured

"bpeltzer" wrote:

> I'm assuming you want your formulas to consider only the rows that pass the
> filter. If so, check out the subtotal function; it honors the filter. Ex
> =sum(a:a) will always give you the sum of the entire column.
> =subtotal(9,a:a) will total those cells in column A that pass the filter.
> See the help on the subtotal function to find the other calculations
> available in subtotal (min, max, avg, count, etc).
>
> "Jo Davis" wrote:
>
> > Hi
> >
> > Can you have a formula that updates when you use a filter?
> >
> >

[Jo Davis]

Sorry

You can tell it is Monday, i want the St Deviation to update/change with the
new filtered information, is this possible?


"Jo Davis" wrote:

> Hi
>
> Can you have a formula that updates when you use a filter?
>
>

[Gord Dibben]

Jo

Have a look at Help under "subtotal function".

Shows the formulas to use.


Gord Dibben Excel MVP

On Mon, 14 Nov 2005 09:42:10 -0800, "Jo Davis"
<JoDavis@discussions.microsoft.com> wrote:

>Sorry
>
>You can tell it is Monday, i want the St Deviation to update/change with the
>new filtered information, is this possible?
>
>
>"Jo Davis" wrote:
>
>> Hi
>>
>> Can you have a formula that updates when you use a filter?
>>
>>

[bpeltzer]

Yes, if you check the subtotal function help, you'll see that function 7 is
stdev and 8 is stdevp. So, for example, you might calculate
=subtotal(7,a2:a200) to calculate the sample standard deviation of the cells
in a2:a200 that passed your filter.

"Jo Davis" wrote:

> Hi
>
> The calculation i need is a st dev will this still be honoured
>
> "bpeltzer" wrote:
>
> > I'm assuming you want your formulas to consider only the rows that pass the
> > filter. If so, check out the subtotal function; it honors the filter. Ex
> > =sum(a:a) will always give you the sum of the entire column.
> > =subtotal(9,a:a) will total those cells in column A that pass the filter.
> > See the help on the subtotal function to find the other calculations
> > available in subtotal (min, max, avg, count, etc).
> >
> > "Jo Davis" wrote:
> >
> > > Hi
> > >
> > > Can you have a formula that updates when you use a filter?
> > >
> > >