Non zero weighted average

[jeffsfas]

I've used sumproduct and array formulas before, but does anyone know ho
to calculate a non-zero weigthted average in Excel for a range o
numbers? Thanks for the help

 

[Guest]

what weighting do you want to use?

 

[N Harkawat]

=AVERAGE(IF(A1:A6<>0,(B1:B6)*(A1:A6)))
array entered (ctrl+shift+enter)

where A1:A6 are the values from where non zeros are to excluded and
B1:b6are the weights

 

[jeffsfas]

I have 2 columns, first range is a set of numbers (which includes
zeroes) and the other is balances. I need to weight by the balance
column, excluding the zeroes in the first range of numbers, to get the
Non-zero weighted average of the first range of numbers.

Ex/ Columns
Row A B
1 0 200
2 4 1000
3 16 1400
4 7 1050
5 0 300
6 29 5000

A zero-weighted average would just be the following formula
sumproduct(A1:A6,B1:B6)/sum(B1:B6)
-- but I need to do this as nonzero.

Hope this clarifies, the help is greatly appreciated.

 

[anilsolipuram]

Try this

=SUMPRODUCT(--(A1:A6<>0),B1:B6,A1:A6)/(SUMPRODUCT(--(A1:A6<>0)))

 

[jeffsfas]

Not sure what this syntax "--" means in this formula:
SUMPRODUCT(--(A1:A6<>0),B1:B6,A1:A6)/(SUMPRODUCT(--(A1:A6<>0)))

Does this weight by the balances in column B?

 

[anilsolipuram]

To clarify the importance of -- go to mcgimpsey site at:

http://www.mcgimpsey.com/excel/formulae/doubleneg.htm

 

[Harlan Grove]

N Harkawat wrote...

=AVERAGE(IF(A1:A6<>0,(B1:B6)*(A1:A6)))
array entered (ctrl+shift+enter)

....

This is wrong. Given 1 and 9 in A1 and A2 with A3:A6 blank, 1 and 2 in
B1 and B2, B3:B6 irrelevant. The weighted average of 1 and 2 with
respective weights 1 and 9 should be 1.9 [(1 * 1 + 9 * 2) / (1 + 9)].
The formula above gives 9.5. Do you know what a weighted average is?

 

[jeffsfas]

Thanks everyone for all the help. I'm not an Excel expert but these
formulas seemed to work for me in calculating what I needed:

Array formula
{=SUMPRODUCT(IF(a1:a6<>0,(b1:b6)*(a1:a6)))/SUM(IF(a1:a6<>0,b1:b6))}

Does not need to be an array formula
=SUMPRODUCT(--(a1:a6<>0),b1:b6,a1:a6)/(SUMPRODUCT(--(a1:a6<>0),b1:b6))

This seems to work because I need to weight by what is in column B. I
think I copied these formulas into the new thread correctly. There may
be a better way to write this but these seem to work for now. I should
try this out on more than a few examples. Back to crunching numbers.

 

[Harlan Grove]

anilsolipuram wrote...

Try this

=SUMPRODUCT(--(A1:A6<>0),B1:B6,A1:A6)/(SUMPRODUCT(--(A1:A6<>0)))

WRONG! Try reading the OP's follow-up. Column *B* contains the weights.

"A zero-weighted average would just be the following formula
sumproduct(A1:A6,B1:B6)/sum(B1­:B6)"

There's no need for a conditional expression in the numerator, only the
denominator.

=SUMPRODUCT(A1:A6,B1:B6)/SUMIF(A1:A6,"<>0",B1:B6)

since the 0s in col A will effectively exclude the corresponding col B
values from the SUMPRODUCT result.

 

[Guest]

try
=sumproduct(A1:A6,B1:B6)/Sumproduct(--(A1:A6<>""),B1:B6)
the --( in the sumproduct makes the logical True-False response inside ther
parenthesis become a 1-0 value