I'm looking for a formula similar to the following, but one that works.

=average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4))

Column A represent sales rep column, and I want to see the average sales for
all cells that have data entered in column C.

I've tried the ctrl+shift+enter, but it's still averaging with zeros, so
something is wrong with my formula.

Thank you.
--
Lori

substitute * for +
=average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4))
HTH,
Gary Brown


"Lori Hornick" wrote:

I'm looking for a formula similar to the following, but one that works.

=average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4))

Column A represent sales rep column, and I want to see the average sales for
all cells that have data entered in column C.

I've tried the ctrl+shift+enter, but it's still averaging with zeros, so
something is wrong with my formula.

Thank you.
--
Lori

I've swapped out * for + but the zeros are still being calculated in and
reducing the average I need to see.

"Gary Brown" wrote:

substitute * for +
=average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4))
HTH,
Gary Brown


"Lori Hornick" wrote:

I'm looking for a formula similar to the following, but one that works.

=average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4))

Column A represent sales rep column, and I want to see the average sales for
all cells that have data entered in column C.

I've tried the ctrl+shift+enter, but it's still averaging with zeros, so
something is wrong with my formula.

Thank you.
--
Lori

Did you remember to make it an array (ctrl-shift-enter)?
I tested it. It works.
HTH,
Gary Brown


"Lori Hornick" wrote:

I've swapped out * for + but the zeros are still being calculated in and
reducing the average I need to see.

"Gary Brown" wrote:

substitute * for +
=average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4))
HTH,
Gary Brown


"Lori Hornick" wrote:

I'm looking for a formula similar to the following, but one that works.

=average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4))

Column A represent sales rep column, and I want to see the average sales for
all cells that have data entered in column C.

I've tried the ctrl+shift+enter, but it's still averaging with zeros, so
something is wrong with my formula.

Thank you.
--
Lori

Yes, if I don't use array, I receive #DIV/0!
If I do use array, it results in an average including zeros. Sigh!!!

I've just thrown together a baby spreadsheet that looks like this:

B C

4 PW 20%
5 JC 30%
6 PW 0%

=AVERAGE(IF((B4:B6="PW")+(C4:C6"0"),C4:C6))

The result is 10%, not 20%


"Gary Brown" wrote:

Did you remember to make it an array (ctrl-shift-enter)?
I tested it. It works.
HTH,
Gary Brown


"Lori Hornick" wrote:

I've swapped out * for + but the zeros are still being calculated in and
reducing the average I need to see.

"Gary Brown" wrote:

substitute * for +
=average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4))
HTH,
Gary Brown


"Lori Hornick" wrote:

I'm looking for a formula similar to the following, but one that works.

=average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4))

Column A represent sales rep column, and I want to see the average sales for
all cells that have data entered in column C.

I've tried the ctrl+shift+enter, but it's still averaging with zeros, so
something is wrong with my formula.

Thank you.
--
Lori

Lori,

In array formulas, + acts as OR, * acts as AND. You want AND, so use

=AVERAGE(IF((B4:B6="PW")*(C4:C6"0"),C4:C6))

And since you are looking at numbers, get rid of the quotes

=AVERAGE(IF((B4:B6="PW")*(C4:C60),C4:C6))

And you may also be looking at rounded numbers, so try

=AVERAGE(IF((B4:B6="PW")*(C4:C6=0.005),C4:C6))

HTH,
Bernie
MS Excel MVP


"Lori Hornick" wrote in message
...
Yes, if I don't use array, I receive #DIV/0!
If I do use array, it results in an average including zeros. Sigh!!!

I've just thrown together a baby spreadsheet that looks like this:

B C

4 PW 20%
5 JC 30%
6 PW 0%

=AVERAGE(IF((B4:B6="PW")+(C4:C6"0"),C4:C6))

The result is 10%, not 20%


"Gary Brown" wrote:

Did you remember to make it an array (ctrl-shift-enter)?
I tested it. It works.
HTH,
Gary Brown


"Lori Hornick" wrote:

I've swapped out * for + but the zeros are still being calculated in
and
reducing the average I need to see.

"Gary Brown" wrote:

substitute * for +
=average(if((A1:A4="Jones")*(C1:C4"0"),C1:C4))
HTH,
Gary Brown


"Lori Hornick" wrote:

I'm looking for a formula similar to the following, but one that
works.

=average(if((A1:A4="Jones")+(C1:C4"0"),C1:C4))

Column A represent sales rep column, and I want to see the average
sales for
all cells that have data entered in column C.

I've tried the ctrl+shift+enter, but it's still averaging with
zeros, so
something is wrong with my formula.

Thank you.
--
Lori