SumIF and STDEVIF is there a such thing

Hey guys,

I am working on a spreadsheet that I have some arrays with SumIF and
CountIF. I am looking for a way to do a Standard Deviation through an
STDEVIF. I built the following formula, but Excel did not like it one bit.

{=STDEV(IF($F$3:$F$25=$F35,IF(ISNUMBER(H$3:H$25)*(H$3:H$25),0)))}

If I can not do a STDEV IF. Then I thought maybe I could perform a CountIF
in cell H36 and then try to perform an offset type of formula to tell Excel
which cells I would want the STDEV performed on. When I tried this:
STDEV(h36,x) I do not get the correct answer either. Essentially I was
thinking by using multiple countIF's I could perform a STDEV calc.

Let me know your thoughts.

thanks.

Chris

{=STDEV(IF($F$3:$F$25=$F35,IF(ISNUMBER(H$3:H$25),(H$3:H$25))))}

--
Regards,
Tom Ogilvy

"Chris" <Chris@discussions.microsoft.com> wrote in message
news:EC2AF7D4-9039-4302-9239-6C4990BE333F@microsoft.com...
> Hey guys,
>
> I am working on a spreadsheet that I have some arrays with SumIF and
> CountIF. I am looking for a way to do a Standard Deviation through an
> STDEVIF. I built the following formula, but Excel did not like it one
bit.
>
> {=STDEV(IF($F$3:$F$25=$F35,IF(ISNUMBER(H$3:H$25)*(H$3:H$25),0)))}
>
> If I can not do a STDEV IF. Then I thought maybe I could perform a
CountIF
> in cell H36 and then try to perform an offset type of formula to tell
Excel
> which cells I would want the STDEV performed on. When I tried this:
> STDEV(h36,x) I do not get the correct answer either. Essentially I was
> thinking by using multiple countIF's I could perform a STDEV calc.
>
> Let me know your thoughts.
>
> thanks.
>
> Chris
>
>

I also often need to do stats based on subsets of a data list in Excel. Even
if you can set up an array formula to do an "if" type condition the problem
with taking the standard deviation is that the cells that don't meet your
condition will be equal to zero, and that is no good since they get included
in the calculation of the standard deviation when, in reality, they should be
ignored (not counted as zeros).

The best way I have found to deal with this (if you can have some calculated
columns) is to add a column to the list that tests the "IF" condition and
gives the cell value if the condition is true or a blank if it is false, e.g.
=IF(AND(A1=Today(),B1="Widgets"),C1,"")
Then take the sum, count, mean, std dev, etc from the values in C using the
standard Excel fomulas. The formulas will ignore all the spaces so the
calculations will be valid for just those data values that meet your
conditions. Also: note that you can use multiple conditions in your IF
statement, which is an improvement over the SUMIF() or COUNTIF() functions.

"Chris" wrote:

> Hey guys,
>
> I am working on a spreadsheet that I have some arrays with SumIF and
> CountIF. I am looking for a way to do a Standard Deviation through an
> STDEVIF. I built the following formula, but Excel did not like it one bit.
>
> {=STDEV(IF($F$3:$F$25=$F35,IF(ISNUMBER(H$3:H$25)*(H$3:H$25),0)))}
>
> If I can not do a STDEV IF. Then I thought maybe I could perform a CountIF
> in cell H36 and then try to perform an offset type of formula to tell Excel
> which cells I would want the STDEV performed on. When I tried this:
> STDEV(h36,x) I do not get the correct answer either. Essentially I was
> thinking by using multiple countIF's I could perform a STDEV calc.
>
> Let me know your thoughts.
>
> thanks.
>
> Chris
>
>

Thanks for the posts. I had already taken into account for blank cells in a
prior formula.

Tom, Once again you have solved my problem.

Chris

"Tom Ogilvy" wrote:

> {=STDEV(IF($F$3:$F$25=$F35,IF(ISNUMBER(H$3:H$25),(H$3:H$25))))}
>
> --
> Regards,
> Tom Ogilvy
>
> "Chris" <Chris@discussions.microsoft.com> wrote in message
> news:EC2AF7D4-9039-4302-9239-6C4990BE333F@microsoft.com...
> > Hey guys,
> >
> > I am working on a spreadsheet that I have some arrays with SumIF and
> > CountIF. I am looking for a way to do a Standard Deviation through an
> > STDEVIF. I built the following formula, but Excel did not like it one
> bit.
> >
> > {=STDEV(IF($F$3:$F$25=$F35,IF(ISNUMBER(H$3:H$25)*(H$3:H$25),0)))}
> >
> > If I can not do a STDEV IF. Then I thought maybe I could perform a
> CountIF
> > in cell H36 and then try to perform an offset type of formula to tell
> Excel
> > which cells I would want the STDEV performed on. When I tried this:
> > STDEV(h36,x) I do not get the correct answer either. Essentially I was
> > thinking by using multiple countIF's I could perform a STDEV calc.
> >
> > Let me know your thoughts.
> >
> > thanks.
> >
> > Chris
> >
> >
>
>
>

Hi Chris for these purposes there's DSTDEV function designed. It's much
faster then range formulas. You can find DVAR, DSUM, DPRODUCT functions
too in excel.

See my suggestion for a way to avoid this problem without using a helper
column.

--
Regards,
Tom Ogilvy

"K Dales" <KDales@discussions.microsoft.com> wrote in message
news:991FD552-7BA3-43FB-B2D8-219925225A63@microsoft.com...
> I also often need to do stats based on subsets of a data list in Excel.
Even
> if you can set up an array formula to do an "if" type condition the
problem
> with taking the standard deviation is that the cells that don't meet your
> condition will be equal to zero, and that is no good since they get
included
> in the calculation of the standard deviation when, in reality, they should
be
> ignored (not counted as zeros).
>
> The best way I have found to deal with this (if you can have some
calculated
> columns) is to add a column to the list that tests the "IF" condition and
> gives the cell value if the condition is true or a blank if it is false,
e.g.
> =IF(AND(A1=Today(),B1="Widgets"),C1,"")
> Then take the sum, count, mean, std dev, etc from the values in C using
the
> standard Excel fomulas. The formulas will ignore all the spaces so the
> calculations will be valid for just those data values that meet your
> conditions. Also: note that you can use multiple conditions in your IF
> statement, which is an improvement over the SUMIF() or COUNTIF()
functions.
>
> "Chris" wrote:
>
> > Hey guys,
> >
> > I am working on a spreadsheet that I have some arrays with SumIF and
> > CountIF. I am looking for a way to do a Standard Deviation through an
> > STDEVIF. I built the following formula, but Excel did not like it one
bit.
> >
> > {=STDEV(IF($F$3:$F$25=$F35,IF(ISNUMBER(H$3:H$25)*(H$3:H$25),0)))}
> >
> > If I can not do a STDEV IF. Then I thought maybe I could perform a
CountIF
> > in cell H36 and then try to perform an offset type of formula to tell
Excel
> > which cells I would want the STDEV performed on. When I tried this:
> > STDEV(h36,x) I do not get the correct answer either. Essentially I was
> > thinking by using multiple countIF's I could perform a STDEV calc.
> >
> > Let me know your thoughts.
> >
> > thanks.
> >
> > Chris
> >
> >