calculating wtd number with conditional array formula
Hello, I have a data set that looks like this:
RATE UNITS
12.00% 0
0.00% 1500
10.00% 6000
20.00% 8000
and I need to calculate a weighted average rate. I need to exclude the row
if either rate or units contain a 0 value.
The result should show a wtd value of 15.71%
my formula is a mess:
={(SUM(IF(AND(UNITS<0,RATES<0),(RATES*UNITS)/(SUM(IF(UNITS<0,UNITS,0))),0)))}
If anyone can help straighten me out, I'd appreciate it. :)
Thank you,
Nacho
|