Weighted Conditional Standard Deviation

[jbecks0505]

Hi all,

I'm looking for a way to find a weighted standard deviation with a conditional contained within. The formula for for the conditional weighted average is =SUMPRODUCT(--(RM_Done=1),RoughMill,Balance)/SUMIFS(Balance,RM_Done,1) however when I try to turn this into standard deviation (which would be =SQRT(SUMPRODUCT((RoughMill-B2)^2,Balance)/SUMIFS(Balance,RM_Done,1)) ) i'm met with a #VALUE formula. Weights are in the named range "Balance", values are in "RoughMill".

Any help on resolving this would be greatly appreciated.

Thanks!

 

[AhoyNC]

Maybe (I assume B2 is the weighted mean in your formula).

SQRT(SUMPRODUCT((RM_Done=1)*(RoughMill-B2)^2,Balance)/SUMIFS(Balance,RM_Done,1))

 

[jbecks0505]

AhoyNC,

Thanks for the reply, yes B2 is the weighted mean. I inputted this and still comes back with #VALUE! error. I believe this is due to the fact that there are blanks in the RoughMill data range, any way around that issue?

 

[AhoyNC]

Are they actually blank or is there a formula in the cell? What about your Balance range, what is in it if the RoughMill data cell is blank?

 

[jbecks0505]

Are they actually blank or is there a formula in the cell? What about your Balance range, what is in it if the RoughMill data cell is blank?

Formula in cell so not actually blank. Balance range has values regardless of what's in RoughMill.

 

[AhoyNC]

What is the formula?
I assume you are looking for rows where RM_Done is equal to 1.
Can there be a blank (formula that returns a blank) in RoughMill where RM_Done=1? If so, wouldn't your SUMIFS(Balance,RM_Done,1) in your weighted average formula give you an erroneous answer by summing numbers in the Balance column when the RoughMill column is blank?
It would help if you could post a small sample of your data.