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.

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" <bj@discussions.microsoft.com> wrote in message
news:3E0DA853-66A5-4639-8289-1F0899778F49@microsoft.com...
> 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.

Try this

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

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?

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

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

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.

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
>
>

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.

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?

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" <bj@discussions.microsoft.com> wrote in message
news:3E0DA853-66A5-4639-8289-1F0899778F49@microsoft.com...
> 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
>>
>>

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?

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
>
>

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
>
>

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.

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?

=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" <bj@discussions.microsoft.com> wrote in message
news:3E0DA853-66A5-4639-8289-1F0899778F49@microsoft.com...
> 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
>>
>>

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
>
>

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" <bj@discussions.microsoft.com> wrote in message
news:3E0DA853-66A5-4639-8289-1F0899778F49@microsoft.com...
> 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
>>
>>

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
>
>

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?

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" <bj@discussions.microsoft.com> wrote in message
news:3E0DA853-66A5-4639-8289-1F0899778F49@microsoft.com...
> 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
>>
>>

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
>
>

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?

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
>
>

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.

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?

=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" <bj@discussions.microsoft.com> wrote in message
news:3E0DA853-66A5-4639-8289-1F0899778F49@microsoft.com...
> 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
>>
>>

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
>
>

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
>
>

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.

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?

=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" <bj@discussions.microsoft.com> wrote in message
news:3E0DA853-66A5-4639-8289-1F0899778F49@microsoft.com...
> 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
>>
>>

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
>
>

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
>
>

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.

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?

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" <bj@discussions.microsoft.com> wrote in message
news:3E0DA853-66A5-4639-8289-1F0899778F49@microsoft.com...
> 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
>>
>>

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?

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" <bj@discussions.microsoft.com> wrote in message
news:3E0DA853-66A5-4639-8289-1F0899778F49@microsoft.com...
> 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
>>
>>

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
>
>

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?

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" <bj@discussions.microsoft.com> wrote in message
news:3E0DA853-66A5-4639-8289-1F0899778F49@microsoft.com...
> 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
>>
>>

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
>
>

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.

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" <bj@discussions.microsoft.com> wrote in message
news:3E0DA853-66A5-4639-8289-1F0899778F49@microsoft.com...
> 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
>>
>>

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?

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
>
>