Excel AVERAGE formula - Selective averaging in an array

[mdmonzon]

I have a sales spreadsheet column that I want to use an AVERAGE formul
on but with selective properties. Each cell in the column is a SUM o
sales for each person who was in attendance for that day. I want t
AVERAGE out this column but only average out those cells which contai
a number, that is to say, those columns with people in attendance. I
a sales person wasn't there for that day, there are no sales entries s
the SUM function will return a #DIV/0! for those people absent. I wan
the AVERAGE formula to search the columns for a number that is no
#DIV/0! and average only those numbers out.

Also, but not as important, if the #DIV/0! would go away if I type
"not here" in the attendance column would be great!

The spreadsheet is attached and it is the last column!

Hope this makes sense and thanks in advance

Attachment filename: pet outcall daily tally sheet.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=63181

 

[hgrove]

mdmonzon wrote...
...

Also, but not as important, if the #DIV/0! would go away if I
typed "not here" in the attendance column would be great!

Your priorities are exactly opposite what they should be. If you too
care of the #DIV/0! problem and returned text (such as "") when peopl
weren't in, you could just use AVERAGE on the range, and it'd ignor
the cells containing text. In other words, you'd solve your proble
with AVERAGE if you fixde this problem with your SUMs.

So what do these SUM formulas look like that they return #DIV/0! ?

The spreadsheet is attached and it is the last column!

No sensible person opens ANY Office document from ANYONE they don'
know. Too much risk for negligible or no benefit. Had you provide
samples of your formulas as PLAIN TEXT, it's likely that would hav
been sufficient for others to figure out what to do

 

[mdmonzon]

Thanks for the input. I am SUMming sales figures from 3 cells in eac
row and then dividing them by number of hours worked by that person t
get average sales per hour per person. Sorry I forgot to elaborate o
that. Here is an example where D11, E11, and F11 represent sales of
certain version of the base product we sell and G11 represents th
number of hours the person spent trying to sell.:

=(D11+E11+F11)/G11

So if they sold 4, 0, 2 respectively and worked 2 hours selling, th
average sales per hour would return the number: 3.

If a person was not in attendance that day, D11, E11, F11, and G1
would all be blank. The average sales per hour would return: #DIV/0!

When I average out the column, I want it to look for numbers like 3 an
not factor in the #DIV/0!'s.

Thanks

 

[Biff]

Hi mdmonzon!

replace your "sum" formula with this:

=IF(ISNUMBER(G11),SUM(D11:F11)/G11,"")

Biff

 

[Frank Kabel]

Hi Biff
this won't work if G11=0 (probably not a possible value in this case)
but I would use

=IF(N(G11)=0,"",SUM(D11:F11)/G11)

 

[Harlan Grove]

this won't work if G11=0 (probably not a possible value in this case)
but I would use

=IF(N(G11)=0,"",SUM(D11:F11)/G11)

....

I'd test for N(G11)>0, but that's just me.

 

[mdmonzon]

Thank you Frank this worked! BTW, G11 would not be zero because i
someone is absent G11 would be left blank instead of putting i
zeroes.

Thanks again to one and all