Average only with specific critera

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


"Stephen" wrote:

> 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

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

Hope this helps,
Miguel.

"Stephen" wrote:

> 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

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!

Stephen


"Ron Coderre" wrote:

> Try this:
>
> =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1)
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Stephen" wrote:
>
> > 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:

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

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

XL2002, WinXP


"Stephen" wrote:

> 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!
>
> Stephen
>
>
> "Ron Coderre" wrote:
>
> > Try this:
> >
> > =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1)
> >
> > Does that help?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "Stephen" wrote:
> >
> > > 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

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!

Stephen


"Ron Coderre" wrote:

> 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, WinXP
>
>
> "Stephen" wrote:
>
> > 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!
> >
> > Stephen
> >
> >
> > "Ron Coderre" wrote:
> >
> > > Try this:
> > >
> > > =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1)
> > >
> > > Does that help?
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP
> > >
> > >
> > > "Stephen" wrote:
> > >
> > > > 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

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
[email protected]
"Stephen" <[email protected]> wrote in message
news:[email protected]...
> 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

Maybe one of these?:

=(SUMPRODUCT((B1:B100=1)*C1:C100)+SUMPRODUCT((B1:B100=2)*D1:D100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2}))

or

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

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

XL2002, WinXP


"Stephen" wrote:

> 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!
>
> Stephen
>
>
> "Ron Coderre" wrote:
>
> > 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, WinXP
> >
> >
> > "Stephen" wrote:
> >
> > > 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!
> > >
> > > Stephen
> > >
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > Try this:
> > > >
> > > > =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1)
> > > >
> > > > Does that help?
> > > > ***********
> > > > Regards,
> > > > Ron
> > > >
> > > > XL2002, WinXP
> > > >
> > > >
> > > > "Stephen" wrote:
> > > >
> > > > > 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

Yes, thanks everyone for the help!
--
Thanks!

Stephen


"Ron Coderre" wrote:

> Maybe one of these?:
>
> =(SUMPRODUCT((B1:B100=1)*C1:C100)+SUMPRODUCT((B1:B100=2)*D1:D100))/SUMPRODUCT(COUNTIF(B1:B100,{1,2}))
>
> or
>
> =SUMPRODUCT((B1:B10={1,2})*C1:D10)/SUMPRODUCT(COUNTIF(B1:B10,{1,2}))
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Stephen" wrote:
>
> > 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!
> >
> > Stephen
> >
> >
> > "Ron Coderre" wrote:
> >
> > > 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, WinXP
> > >
> > >
> > > "Stephen" wrote:
> > >
> > > > 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!
> > > >
> > > > Stephen
> > > >
> > > >
> > > > "Ron Coderre" wrote:
> > > >
> > > > > Try this:
> > > > >
> > > > > =SUMIF(B1:B100,1,C1:C100)/COUNTIF(B1:B100,1)
> > > > >
> > > > > Does that help?
> > > > > ***********
> > > > > Regards,
> > > > > Ron
> > > > >
> > > > > XL2002, WinXP
> > > > >
> > > > >
> > > > > "Stephen" wrote:
> > > > >
> > > > > > 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