Weighted average zeroes

[szita2000]

Hi guys.

I have a crazy file where weighted average being calculated.
Now, unfortunately it is a file made to a specific format so we couldn't use sumproduct on the two ranges.
Rather the formula looks something like this:
=((O99*$CM$74)+(O108*$CM$77)+(O113*$CM$78)+(O123*$CM$80)+(O128*$CM$82)+(O133*$CM$83)+(O137*$CM$84)+(O179*$CM$89))/SUM($CM$74,$CM$77:$CM$78,$CM$80:$CM$84,$CM$89)
My question is.
How can I exclude the corresponding weight (Column CM) if there is no data for the actual month (Column O above).
(Small clarification needed for the above: The values in column CM are for a whole month, but in some weeks we don't get any KPIs on some of the left hand side ranges (Lines are not running))

My initial reaction was that I can only do this with individual if formulas? (IF there is no KPI then volume = 0) so on so forth...

Am I missing something here?

Thanks

 

[steve the fish]

Unfortunately your clarification is wholly confusing. At least to me it is. I must be missing something as if either O99 or CM74 are zero then O99*$CM$74 also equals zero.

 

[RoryA]

You'd need to exclude it from the sum section using something like:

SUM(IF(O74=0,0,$CM$74),SUMIF(O77:O78,">0",$CM$77:$CM$78),SUMIF(O80:O84,">0",$CM$80:$CM$84),IF(O89=0,0,$CM$89))

depending on the actual layout of the sheet, this may be able to be shortened if there's an indicator of some sort as to which rows should be used.

 

[szita2000]

You'd need to exclude it from the sum section using something like:

SUM(IF(O74=0,0,$CM$74),SUMIF(O77:O78,">0",$CM$77:$CM$78),SUMIF(O80:O84,">0",$CM$80:$CM$84),IF(O89=0,0,$CM$89))

depending on the actual layout of the sheet, this may be able to be shortened if there's an indicator of some sort as to which rows should be used.

Thanks Rory.
This seems doable.
Unfortunately the whole sheet's layout has been made to a format I wouldn't have approved, but it is a sheet that stuck with us since someone put it together.
It is one of those One week one column, BUT we ensist on putting the month end on as a column, in to the week columns.
Oh well. Admin's life. What you gonna do. :D Thanks for the help though.