software

Hi. I am trying to average a set of numbers but only if the number in the
column to the left is equal to 1. For example:

b1:b100 I have either the number 1, 2, 3, or 4,
c1:c100 I have percentages

I want to average only the percentages that have a 1 to the left of it in
the b column.

Thanks in advance...
--
Thanks!

Stephen

Try this:

=SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1)

Does that help?
***********
Regards,
Ron

XL2002, WinXPquot;Stephenquot; wrote:

gt; Hi. I am trying to average a set of numbers but only if the number in the
gt; column to the left is equal to 1. For example:
gt;
gt; b1:b100 I have either the number 1, 2, 3, or 4,
gt; c1:c100 I have percentages
gt;
gt; I want to average only the percentages that have a 1 to the left of it in
gt; the b column.
gt;
gt; Thanks in advance...
gt; --
gt; Thanks!
gt;
gt; Stephen

You can use the following:
=SUMPRODUCT(--(B1:B100=1),C1:C100)/COUNTIF(B1:B100,1)

Hope this helps,
Miguel.

quot;Stephenquot; wrote:

gt; Hi. I am trying to average a set of numbers but only if the number in the
gt; column to the left is equal to 1. For example:
gt;
gt; b1:b100 I have either the number 1, 2, 3, or 4,
gt; c1:c100 I have percentages
gt;
gt; I want to average only the percentages that have a 1 to the left of it in
gt; the b column.
gt;
gt; Thanks in advance...
gt; --
gt; Thanks!
gt;
gt; Stephen

Thank you, I did get it to work.

How would I do it if I want to average the percentages if either 1 or 2 were
in the b column?

Thanks in advance.
--
Thanks!

Stephenquot;Ron Coderrequot; wrote:

gt; Try this:
gt;
gt; =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1)
gt;
gt; Does that help?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;Stephenquot; wrote:
gt;
gt; gt; Hi. I am trying to average a set of numbers but only if the number in the
gt; gt; column to the left is equal to 1. For example:
gt; gt;
gt; gt; b1:b100 I have either the number 1, 2, 3, or 4,
gt; gt; c1:c100 I have percentages
gt; gt;
gt; gt; I want to average only the percentages that have a 1 to the left of it in
gt; gt; the b column.
gt; gt;
gt; gt; Thanks in advance...
gt; gt; --
gt; gt; Thanks!
gt; gt;
gt; gt; Stephen

Try this:

=SUMPRODUCT(SUMIF(B1:B100,{1,2},C1:C100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2}))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXPquot;Stephenquot; wrote:

gt; Thank you, I did get it to work.
gt;
gt; How would I do it if I want to average the percentages if either 1 or 2 were
gt; in the b column?
gt;
gt; Thanks in advance.
gt; --
gt; Thanks!
gt;
gt; Stephen
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try this:
gt; gt;
gt; gt; =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1)
gt; gt;
gt; gt; Does that help?
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;Stephenquot; wrote:
gt; gt;
gt; gt; gt; Hi. I am trying to average a set of numbers but only if the number in the
gt; gt; gt; column to the left is equal to 1. For example:
gt; gt; gt;
gt; gt; gt; b1:b100 I have either the number 1, 2, 3, or 4,
gt; gt; gt; c1:c100 I have percentages
gt; gt; gt;
gt; gt; gt; I want to average only the percentages that have a 1 to the left of it in
gt; gt; gt; the b column.
gt; gt; gt;
gt; gt; gt; Thanks in advance...
gt; gt; gt; --
gt; gt; gt; Thanks!
gt; gt; gt;
gt; gt; gt; Stephen

Hi. The problem is that I have a column for each number on the left side.
For example:

b1:b100 - 1, 2, 3, or 4
c1:c100 - percentages that work with 1 or 3
d1:d100 - percentages that work with 2 or 4

I want to be able to average the percentages that correlate with 1 and 2. So
when I did the formula I used (c1:d100) and when C was 1, it would average C
and D, instead of just c. As D would have been blank it averaged C and 0. Is
there a way for the formula to ignore blanks?

Thanks again.
--
Thanks!

Stephenquot;Ron Coderrequot; wrote:

gt; Try this:
gt;
gt; =SUMPRODUCT(SUMIF(B1:B100,{1,2},C1:C100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2}))
gt;
gt; Is that something you can work with?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;Stephenquot; wrote:
gt;
gt; gt; Thank you, I did get it to work.
gt; gt;
gt; gt; How would I do it if I want to average the percentages if either 1 or 2 were
gt; gt; in the b column?
gt; gt;
gt; gt; Thanks in advance.
gt; gt; --
gt; gt; Thanks!
gt; gt;
gt; gt; Stephen
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try this:
gt; gt; gt;
gt; gt; gt; =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1)
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Stephenquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi. I am trying to average a set of numbers but only if the number in the
gt; gt; gt; gt; column to the left is equal to 1. For example:
gt; gt; gt; gt;
gt; gt; gt; gt; b1:b100 I have either the number 1, 2, 3, or 4,
gt; gt; gt; gt; c1:c100 I have percentages
gt; gt; gt; gt;
gt; gt; gt; gt; I want to average only the percentages that have a 1 to the left of it in
gt; gt; gt; gt; the b column.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks in advance...
gt; gt; gt; gt; --
gt; gt; gt; gt; Thanks!
gt; gt; gt; gt;
gt; gt; gt; gt; Stephen

Maybe one of these?:

=(SUMPRODUCT((B1:B100=1)*C1:C100) SUMPRODUCT((B1:B 100=2)*D1100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2}))

or

=SUMPRODUCT((B1:B10={1,2})*C110)/SUMPRODUCT(COUNTIF(B1:B10,{1,2}))

Does that help?
***********
Regards,
Ron

XL2002, WinXPquot;Stephenquot; wrote:

gt; Hi. The problem is that I have a column for each number on the left side.
gt; For example:
gt;
gt; b1:b100 - 1, 2, 3, or 4
gt; c1:c100 - percentages that work with 1 or 3
gt; d1:d100 - percentages that work with 2 or 4
gt;
gt; I want to be able to average the percentages that correlate with 1 and 2. So
gt; when I did the formula I used (c1:d100) and when C was 1, it would average C
gt; and D, instead of just c. As D would have been blank it averaged C and 0. Is
gt; there a way for the formula to ignore blanks?
gt;
gt; Thanks again.
gt; --
gt; Thanks!
gt;
gt; Stephen
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try this:
gt; gt;
gt; gt; =SUMPRODUCT(SUMIF(B1:B100,{1,2},C1:C100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2}))
gt; gt;
gt; gt; Is that something you can work with?
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;Stephenquot; wrote:
gt; gt;
gt; gt; gt; Thank you, I did get it to work.
gt; gt; gt;
gt; gt; gt; How would I do it if I want to average the percentages if either 1 or 2 were
gt; gt; gt; in the b column?
gt; gt; gt;
gt; gt; gt; Thanks in advance.
gt; gt; gt; --
gt; gt; gt; Thanks!
gt; gt; gt;
gt; gt; gt; Stephen
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Try this:
gt; gt; gt; gt;
gt; gt; gt; gt; =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1)
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Stephenquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi. I am trying to average a set of numbers but only if the number in the
gt; gt; gt; gt; gt; column to the left is equal to 1. For example:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; b1:b100 I have either the number 1, 2, 3, or 4,
gt; gt; gt; gt; gt; c1:c100 I have percentages
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I want to average only the percentages that have a 1 to the left of it in
gt; gt; gt; gt; gt; the b column.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks in advance...
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; Thanks!
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Stephen

You can also use the ARRAY formula which must be entered with
ctrl shift enter vs just enter

=AVERAGE(IF(B2:B100=1,C2:C100))
--
Don Guillett
SalesAid Software

quot;Stephenquot; gt; wrote in message
...
gt; Hi. I am trying to average a set of numbers but only if the number in the
gt; column to the left is equal to 1. For example:
gt;
gt; b1:b100 I have either the number 1, 2, 3, or 4,
gt; c1:c100 I have percentages
gt;
gt; I want to average only the percentages that have a 1 to the left of it in
gt; the b column.
gt;
gt; Thanks in advance...
gt; --
gt; Thanks!
gt;
gt; Stephen
Yes, thanks everyone for the help!
--
Thanks!

Stephenquot;Ron Coderrequot; wrote:

gt; Maybe one of these?:
gt;
gt; =(SUMPRODUCT((B1:B100=1)*C1:C100) SUMPRODUCT((B1:B 100=2)*D1100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2}))
gt;
gt; or
gt;
gt; =SUMPRODUCT((B1:B10={1,2})*C110)/SUMPRODUCT(COUNTIF(B1:B10,{1,2}))
gt;
gt; Does that help?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;Stephenquot; wrote:
gt;
gt; gt; Hi. The problem is that I have a column for each number on the left side.
gt; gt; For example:
gt; gt;
gt; gt; b1:b100 - 1, 2, 3, or 4
gt; gt; c1:c100 - percentages that work with 1 or 3
gt; gt; d1:d100 - percentages that work with 2 or 4
gt; gt;
gt; gt; I want to be able to average the percentages that correlate with 1 and 2. So
gt; gt; when I did the formula I used (c1:d100) and when C was 1, it would average C
gt; gt; and D, instead of just c. As D would have been blank it averaged C and 0. Is
gt; gt; there a way for the formula to ignore blanks?
gt; gt;
gt; gt; Thanks again.
gt; gt; --
gt; gt; Thanks!
gt; gt;
gt; gt; Stephen
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try this:
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT(SUMIF(B1:B100,{1,2},C1:C100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2}))
gt; gt; gt;
gt; gt; gt; Is that something you can work with?
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Stephenquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Thank you, I did get it to work.
gt; gt; gt; gt;
gt; gt; gt; gt; How would I do it if I want to average the percentages if either 1 or 2 were
gt; gt; gt; gt; in the b column?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks in advance.
gt; gt; gt; gt; --
gt; gt; gt; gt; Thanks!
gt; gt; gt; gt;
gt; gt; gt; gt; Stephen
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Try this:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; gt; ***********
gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; Ron
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; XL2002, WinXP
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Stephenquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Hi. I am trying to average a set of numbers but only if the number in the
gt; gt; gt; gt; gt; gt; column to the left is equal to 1. For example:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; b1:b100 I have either the number 1, 2, 3, or 4,
gt; gt; gt; gt; gt; gt; c1:c100 I have percentages
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I want to average only the percentages that have a 1 to the left of it in
gt; gt; gt; gt; gt; gt; the b column.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks in advance...
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; Thanks!
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Stephen