Standard Deviation formula with IF and with AND?

[sarahmcg]

Wondering if there is a way to write a formula to calculate standard deviation with 2 conditions - so using STDEV plus IF and AND function. I want to write a formula to calculate standard deviation for length of surgery for one surgeon (have a long list of surgeons) and only for those surgeries that are a specific type labeled as "Time-Based" (3 types but only want it calculated for one type).

This is how I wrote the formula

=STDEV(IF(DataSheet!C$4:C$147="Surgeon Jane Doe",DataSheet!J$4:J$147),AND(DataSheet!F$4:F$147="Time-Based"))

hitting CTRL+SHIFT+Enter instead of just hitting ENTER


however I do not think I am getting the correct standard deviation calculation - can anyone help?

 

[Luke M]

You were close. Try this, confirmed as an array:

=STDEV(IF((DataSheet!C$4:C$147="Surgeon Jane Doe")*(DataSheet!F$4:F$147="Time-Based"),DataSheet!J$4:J$147))

 

[Hui]

Sarahmcg


If you Edit (F2) Luke's formula and select the component inbetween the SD Brackets namely:


IF((DataSheet!C$4:C$147="Surgeon Jane Doe")*(DataSheet!F$4:F$147="Time-Based"),DataSheet!J$4:J$147)

Then press F9

You will see that Excel returns data like:

{FALSE;10;FALSE; ... ;14.5;FALSE;FALSE;FALSE}

I removed a few Falses to simplify the response


Excel is happy to evaluate the Standard Deviation of this array and ignores the False values