calculating wtd number with conditional array formula

[Guest]

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

 

[Guest]

One way would be to create a third helper column in which you enter the
following formula (assumes your data starts in the 2nd row):
=IF(A2*B2=0,"Ignore","Include" and fill down as necessary.

Then you could filter out the "Ignore"s and run the weighted average.

 

[Guest]

Thank you Dave - I was able to use your suggestion in this formula that
solved the problem! :

=SUM(IF(ACFRATES*ACFUNITS<>0,ACFRATES*ACFUNITS/(SUM(IF(ACFUNITS*ACFRATES<>0,ACFUNITS,0)))))

 

[Guest]

Interesting. I take it that's an array formula?

 

[Guest]

yep.

Interesting. I take it that's an array formula?