Excel 2007 : standard deviation and if (FORMULA)

[emfgirl]

Hi,

I have several columns of data showing the results of income during specific events but across 8 days. Each day is represented by several columns; ABCD hold data for Monday, EFGH for Tuesday, etc. Each row represents an individual event. The event occured on one day only and is being compared to the other days at the same time.

So, column C2 (G2, etc) contains the $ amount I want to use in the STD calculation and column A2 (E2, etc) is used to determine whether the amount is C2 is to be included. If A2 is 0 then the amount in C2 is included in the SDTEVP calculation. How do I write this formula?

I tried this,

Please Login or Register  to view this content.

Math wiz/Excel guru I am not so any help would be greatly appreciated!

Thanks so much!
emfgirl

[dilipandey]

Hi emfgirl,

Welcome to the forum.

Try using below formula:-

=STDEVP(IF(AND($A2=0,$E2=0,$I2=0),(C2,G2,K2),""))

Regards,
DILIPandey

<click on below 'star' if this helps>

[emfgirl]

Hi dilipandey,

Thanks for such a quick response.

I've tried your formula and it returns the #VALUE! error. I've triple checked the formula to make sure it's applied correctly to my data but it still returns the error.

Ideas?

Thanks!
emfgirl

[dilipandey]

Hi emfgirl,

No worries.. check both the formulas in yellow and green cell in attached workbook.. thanks.

StdDev with If function.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[emfgirl]

That does it. Thanks!

[Ace_XL]

Probably with a helper column each ...See attached

[dilipandey]

you are welcome emfgirl. . .

Cheers

Regards,
DILIPandey

<click on below star if this helps>