SUMPRODUCT calc question
 

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


--
PerryClisbee
------------------------------------------------------------------------
PerryClisbee's Profile: http://www.excelforum.com/member.php...o&userid=37120
View this thread: http://www.excelforum.com/showthread...hreadid=568536

SUMPRODUCT calc question
 

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"
wrote in message
news:PerryClisbee.2c1e87_1154730611.2364@excelforu m-nospam.com...

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


--
PerryClisbee
------------------------------------------------------------------------
PerryClisbee's Profile:
http://www.excelforum.com/member.php...o&userid=37120
View this thread: http://www.excelforum.com/showthread...hreadid=568536