average if zero
 

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

This isn't working although it looks like it should. What does the asterisk
represent?

I'm getting a DIV/0 when entered as an array
And a zero when simply entered with enter

Any other ideas?

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

Perhaps your have strings, not numbers, where your numbers are supposed to
be. Try array-entering (using Ctrl-Shift-Enter)

=AVERAGE(IF((A1:A4="Jones")*(C1:C4<"0"),VALUE(C1: C4)))

--
HTH,
Bernie
MS Excel MVP


"Lori Hornick" wrote in message
...
This isn't working although it looks like it should. What does the
asterisk
represent?

I'm getting a DIV/0 when entered as an array
And a zero when simply entered with enter

Any other ideas?

"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

Nope, this didn't work either. I've reduced my information just for
simplicity. There are two values for Jones, one is blank and the other is 58
and it keeps returning a value of 29 which leads me to believe the 0
argument is being ignored.

"Bernie Deitrick" wrote:

Lori,

Perhaps your have strings, not numbers, where your numbers are supposed to
be. Try array-entering (using Ctrl-Shift-Enter)

=AVERAGE(IF((A1:A4="Jones")*(C1:C4<"0"),VALUE(C1: C4)))

--
HTH,
Bernie
MS Excel MVP


"Lori Hornick" wrote in message
...
This isn't working although it looks like it should. What does the
asterisk
represent?

I'm getting a DIV/0 when entered as an array
And a zero when simply entered with enter

Any other ideas?

"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