Non zero weighted average
 

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


--
jeffsfas
------------------------------------------------------------------------
jeffsfas's Profile: http://www.excelforum.com/member.php...o&userid=24462
View this thread: http://www.excelforum.com/showthread...hreadid=380573

what weighting do you want to use?

"jeffsfas" wrote:


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


--
jeffsfas
------------------------------------------------------------------------
jeffsfas's Profile: http://www.excelforum.com/member.php...o&userid=24462
View this thread: http://www.excelforum.com/showthread...hreadid=380573

=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


"bj" wrote in message
...
what weighting do you want to use?

"jeffsfas" wrote:


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


--
jeffsfas
------------------------------------------------------------------------
jeffsfas's Profile:
http://www.excelforum.com/member.php...o&userid=24462
View this thread:
http://www.excelforum.com/showthread...hreadid=380573

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.


--
jeffsfas
------------------------------------------------------------------------
jeffsfas's Profile: http://www.excelforum.com/member.php...o&userid=24462
View this thread: http://www.excelforum.com/showthread...hreadid=380573

Try this

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


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380573

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?


--
jeffsfas
------------------------------------------------------------------------
jeffsfas's Profile: http://www.excelforum.com/member.php...o&userid=24462
View this thread: http://www.excelforum.com/showthread...hreadid=380573

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

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


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380573

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?

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.


--
jeffsfas
------------------------------------------------------------------------
jeffsfas's Profile: http://www.excelforum.com/member.php...o&userid=24462
View this thread: http://www.excelforum.com/showthread...hreadid=380573

anilsolipuram wrote...
Try this

=3DSUMPRODUCT(--(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=AD:B6)"

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

=3DSUMPRODUCT(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.

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

"jeffsfas" wrote:


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.


--
jeffsfas
------------------------------------------------------------------------
jeffsfas's Profile: http://www.excelforum.com/member.php...o&userid=24462
View this thread: http://www.excelforum.com/showthread...hreadid=380573