SUMPRODUCT calc question

[PerryClisbee]

I have a SUMPRODUCT calculation set up to to a weighted avg of 3 months
of data, for a quarter summary.
[[=IF(W24="","",SUMPRODUCT(W24:Y24,J24:L24)/SUM(J24:L24))]]

My question is this: If one of my months has no data (empty cell),
does this mess up the avg? Does the sumproduct count that cell as a 0,
bringing down the avg? I know that AverageA takes that into account and
has a workaround, but I don't know if this function does. Help!

Thanks,

Perry

 

[Bernard Liengme]

I put some numbers into a worksheet and computed the weighted average by
hand (in my head - easy numbers). My answer agreed with
[[=IF(W24="","",SUMPRODUCT(W24:Y24,J24:L24)/SUM(J24:L24))]]


Then I deleted one month, did the math again and once more got the same
answers as with [[=IF(W24="","",SUMPRODUCT(W24:Y24,J24:L24)/SUM(J24:L24))]]


So your answer would seem to be: No, empty cells do not cause a problem.

I believe you are confused about the use of AVERAGEA. The simple AVERAGE
function ignores blanks and text while AVERAGEA counts text as 0

best wishes


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"PerryClisbee" <[email protected]>
wrote in message
news[email protected]...