excel newbie needs help, STDEV IF??

I read through the forum but no success with my own excel sheet

i have a large database i am looking at

in COULMN B i have customer ID (there is multiple rows for each costumers)
in COLUMN D i have true/false values (regarding whether someone bought something on the same day more than once, >1$)
in COLUMN Q i have the amount the customer spent in each visit

what I need to do is a formula for standard deviation of the spending... (col. Q) for each specific customer ID (Col B), if the value in column D is true.... but only to include those values in Q for which the corresponing D column is true,

can anybody help?

thanks in advance

Try this array formula

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter and not just Enter

Change the highlighted (in red) number for varying results based on Customer ID

Try this array formula

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter and not just Enter

Change the highlighted (in red) number for varying results based on Customer ID

Maybe because I'm not real good at using these array formula IF's like others. When I need a conditional standard deviation, I tend to go back to my stats text and use the formulas given for standard deviation. The key one is the relation:

sum((xi-mu)^2)=sum(x^2)-(sum(x))^2/n
variance s^2=sum((xi-mu)^2)/(n-1)=(sum(x^2)-(sum(x))^2/n)/(n-1)
stddev=sqrt(s^2)

When I do this, I add two columns to my data -- one column will compute a series of 1's and 0's to identify which points are included in the statistic. This might look something like (comma delimited):

Please Login or Register  to view this content.

note that the -- in column c is called the unary operator and is used to convert a boolean value to 1 or 0.
I do realize that I have not crammed everything into a single cell, and that I have not used the built in average and stdev functions -- I have only used sum(). Done correctly, this should be a robust spreadsheet for calculating averages and standard deviations of a subset of a larger set of data.

I have tried this

=STDEV(IF(AND(A:A=A1,B:B="TRUE"),C:C))

or

=IF(AND(A:A=A2,B:B=TRUE), STDEV(C:C,A:A=A2))

but for some reason it doesn,t work..can anyone comment

hmmm i dont get it :/

What part do you not understand? I'm not sure I'm in a position to give a full stats lecture on how to compute standard deviations, but I would expect any self-respecting intro to stats text to cover the topic adequately.

how do you put that into a formula/

As noted above, I'm not good at, neither am I fond of, putting these kind of array formulas together. I prefer to separate my computations out into several cells where each computation is more manageable for me. I'm sure someone here would be good enough to do it, or to help with the stdev(if()) array formulas that have been suggested.

FWIW, I'm not sure why ACE_XL's suggested formula did not work for you, it seemed to work just fine for me. All you should need to do is adapt the ranges and the conditions to your specific situation/spreadsheet layout.

Here's a sample spreadsheet that I put together.