Std if

[Johnexcel]

Can someone help me out?

I'm comparing various sets of data in a table based on certain conditions. I must include these conditions so I can exclude data based on dates and how I categorize clients based on tier.

I am using this formula, =STDEV(IF(March[Go Live]<3/1/2010,IF(March[Tier]=5,March[Total VIO Visits]))) to evaluate my total vio visits for those clients who went live prior to march and are categorized in tier 5. I am confirming it with CTRL+SHIFT+ENTER

Is my formula incorrect?

 

[barry houdini]

Hello John, welcome to MrExcel

Your date won't be recognised in that format, try like this

=STDEV(IF(March[Go Live]< DATE(2010,3,1),IF(March[Tier]=5,March[Total VIO Visits])))

 

[Johnexcel]

wow thank you so much that worked perfectly. Does that format only need to be used for such an array? Because I also have date conditions on averageifs but they seem to come out fine

 

[barry houdini]

AVERAGEIFS, like the other IF functions, SUMIF, COUNTIF etc. accepts criteria as a text string e.g. "<3/1/2010" but that doesn't work outside of those functions. You don't have to use DATE, you could use <"3/1/2010"+0 amongst others.......but in my opinion DATE is better as the dates are unambiguous