Std deviation for multiple ifs

[T15K]

I have to following average if formula that calculates the average sales for a person (each name is in col C) for all the dates prior to and including the date in col G.

AVERAGEIFS([Sales],[Name],C2,[Date],"<="&G2)

I would like to re-create the exact same formula, but for standard deviation, such that the formula calculates the std deviation of sales for a person (each name is in col C) for all the dates prior to and including the date in col G.

I assume an array formula is required, although I'm not sure exactly of the set up.

Thanks in advance

[Crooza]

Can you upload your spreadsheet?

[T15K]

Please find attached the example spreadsheet

[Crooza]

Try this in column D and drag down

=STDEV.P(IF($A$2:A2=A2,$C$2:C2,""))

it should enter automatically as an array formula I think

[Crooza]

OH I should have said this is based on your data being in date order. saves having to put in another 'and' statement but if your data isn't in date order like your example let me know and I'll amend formula

[T15K]

Thank you for the response, however, that formula does not seem to provide the correct values for the std dev.

[Crooza]

The top ones will have a SD of 0 because there's only one value. Can you tell me what you think the standard deviation is for a few of the results?

[Crooza]

Further to above I just did the Sd for Steve on my scientific calculator and got 33.09 which I recall was what the formula gave so not sure what isn't working for you

[T15K]

You are right, it seems to work against the manual calculations with the exception of one row (which I have highlighted in yellow).

Thanks for your ongoing assistance here!

[Crooza]

I get the SD of 765, 607 and 606 as 74.7 which is what the excel formula gives me. What figures did you use for Harry to get 91.5 and if they were different to these three why?

[T15K]

I have found the problem, it was STDEV.S instead of STDEV.P for that harry case.

Thank's so much for your help. Outstanding!

[Crooza]

Great. Glad it worked