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

SumIF and STDEVIF is there a such thing
 

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

--
Regards,
Tom Ogilvy

"Chris" wrote in message
...
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

SumIF and STDEVIF is there a such thing
 

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

SumIF and STDEVIF is there a such thing
 

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" wrote in message
...
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

SumIF and STDEVIF is there a such thing
 

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

--
Regards,
Tom Ogilvy

"K Dales" wrote in message
...
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

SumIF and STDEVIF is there a such thing
 

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.