My apologies for not being clear at first. The formula I am using for the weighted average is in cell k3 of the first worksheet
Ranges are (L6:L18, M6:M18) on all worksheets ( sum products were performed on all) and divided by I2 (first worksheet). the above formula was placed in K3(first worksheet)
When all the ranges have values then the weighted average formula returns 167.256
However if you delete lets say M6 on the first worksheet the formula returns 162.21
which means it treated M6 as a zero which then brings down the weighted average and makes it incorrect.
What I want is for the formula to recalculate but disregard blank cells and don't treat them as 0. I uploaded a new much improved worksheet.
Thank you .
Bookmarks