Weighted Average with NA

[Ceeyee]

Hi,

have two ranges R1 and R2. R1 is values (with some N/As) and R2 is weights.

How to make a formula to get the weighted average of R1?

I want it so that when the cell in R1 is N/A then we ignore the cell and the corresponding weight. For example if R1={1,N/A,3}, R2={4,5,6}, then I want formula to give me weighted average= (1*4+0+3*6)/(4+0+6)=2.2

Thanks.

 

[Marcelo Branco]

Maybe this Array-formula

=SUM(IF(ISNUMBER(Range1),Range1*Range2))/SUM(IF(ISNUMBER(Range1),Range2))

confirmed with Ctrl+Shift+Enter

M.

 

[shg]

Nice, Marcelo.

 

[Marcelo Branco]

Nice, Marcelo.

Tks

M.

 

[Ceeyee]

Fantastic! Working flawlessly.
Thanks.

Maybe this Array-formula

=SUM(IF(ISNUMBER(Range1),Range1*Range2))/SUM(IF(ISNUMBER(Range1),Range2))

confirmed with Ctrl+Shift+Enter

M.