Average+if command

[Traesmom]

I have a 2-part question. Can anyone tell me if there is a way to
return a zero in an average+if formula instead of an empty set? My
array formula is as follows:{=AVERAGE(IF(AT12:AT21<>0,AT12:AT21,""))}.
If the answer is actually a zero, the result is ###. The cell
containing the 1st average+if formula is also used in a subsequent
average+if formula. If the 1st formula returns pound signs (because
excel is trying to divide 0 by 0), then the subsequent formula won't
work either. I'm trying to average a series of cells but ignore the
cells that contain a zero.

 

[Frank Kabel]

Hi
try
=IF(COUNT(AT12:AT21),AVERAGE(IF(AT12:AT21<>0,AT12:AT21,"")),"")
also an array formula

 

[Traesmom]

Thank you for the suggestion but it still returns ###. Is it even
possible for excel to divide 0 by 0 and not return ###? Even a blank
cell would work because the subsequent formula will read that empty
cell as a zero but it won't work if there are pound signs in the cell.
AAAAHHHHHH!!!!

Lana

 

[Frank Kabel]

Hi
what is the exact formula you have tried and what is the eexact error
message 8make the column wider to see the error message). also are you
sure that widening the column would not solve your issue?

 

[Myrna Larson]

No, Excel cannot divide any number by 0. It doesn't have an internal
representation for infinity, I expect.

 

[Traesmom]

Hi again,
The exact error message returned is #DIV/0!; I'm not getting pound
signs because the column is too narrow. I'm trying to average
subtotals in an employee evaluation. Each category has a subtotal
that flows to another "summary" tab. Then all of the subtotals are
averaged for a final score. Not all categories apply to all employees
so I want Excel to ignore the subtotals that are zero and only average
those that have a score. Is there possibly another way to get the
result I'm looking for? Is there possibly a formula that looks at all
categories but only averages those with a score? In other words,
divide only by the quantity of cells that contain a number other than
zero. If there isn't a solution, I'll have to start from scratch :-(

Lana

 

[Frank Kabel]

Hi
post the formula which return this #DIV/0 error

 

[Traesmom]

Hi

The array formula that returns the #DIV/0 error is:
=AVERAGE(IF(AT12:AT21<>0,AT12:AT21,"")) where AT12:AT21 are subtotals
that flow from previous tabs within the workbook. In this situation,
AT12:AT21 are all zero because that portion of the workbook relates to
a section of my employee evaluation that doesn't apply to all
employees. Is there another way I can get Excel to ignore those cells
that sub-total to zero instead of trying to divide zero by zero?
Thanks Frank for all of your help!!!

Lana

 

[Traesmom]

I'm still stumped. Any and all help is GREATLY appreciated.

Lana

 

[Frank Kabel]

Hi:
=IF(COUNTIF(AT12:AT21,"<>0"),AVERAGE(IF(AT12:AT21<>0,AT12:AT21,"")),"")