how to remove the #div/0 from this formula?

  • Thread starter Thread starter David Schuler
  • Start date Start date
D

David Schuler

I'd like to either remove the #div/0 or change it to say "N/A" in this equation:

{=AVERAGE(IF(C102:C114<>0,C102:C114,FALSE))}

Can some excel guru help a poor man out?

Thanks,
Dave
 
I'd like to either remove the #div/0 or change it to say "N/A" in this
equation:

{=AVERAGE(IF(C102:C114<>0,C102:C114,FALSE))}
Click to expand...

This would only evaluate #DIV/0! if there were no nonzero numbers in the range.
If you want this situation to display nothing, try

=IF(COUNTIF(C102:C114,"<>0"),AVERAGE(IF(C102:C114<>0,C102:C114)),"")
 
Hi
one way:
=IF(COUNTIF(C102:C114,"<>")>0,AVERAGE(IF(C102:C114<>0,C102:C114,FALSE),
"N/A")
 
Harlan said:
...

This would only evaluate #DIV/0! if there were no nonzero numbers in
the range. If you want this situation to display nothing, try

=IF(COUNTIF(C102:C114,"<>0"),AVERAGE(IF(C102:C114<>0,C102:C114)),"")
Click to expand...

Hi Harlan
this won't work if you have only empty cells and/or zeros in this
range. This would cause the #DIV/0 error. It would only work if all
cells are '0'
One workaround:
=IF(COUNTIF(C102:C114,"<>"),AVERAGE(IF(C102:C114<>0,C102:C114)),"")

Regards
Frank
 
...
...
this won't work if you have only empty cells and/or zeros in this
range. This would cause the #DIV/0 error. It would only work if all
cells are '0'
One workaround:
=IF(COUNTIF(C102:C114,"<>"),AVERAGE(IF(C102:C114<>0,C102:C114)),"")
Click to expand...

You're right about the problems with my formula, but yours above gives #DIV/0!
when the only numbers in the range are zero. Looks like some trickery is needed.
Try the array formula

=IF(ISNUMBER(1/SUMSQ(C102:C114)),AVERAGE(IF(C102:C114<>0,C102:C114)),"")
 
Also:

Control+shift+enter...

=IF(COUNT(C102:C114)-COUNTIF(C102:C114,0),AVERAGE(IF(C102:C114,C102:C114)),"
")

or just enter...

=SUM(C102:C114)/MAX(1,COUNT(C102:C114)-COUNTIF(C102:C114,0))
 
...
..

You're right about the problems with my formula, but yours above
gives #DIV/0! when the only numbers in the range are zero. Looks like
some trickery is needed. Try the array formula
Click to expand...
=IF(ISNUMBER(1/SUMSQ(C102:C114)),AVERAGE(IF(C102:C114<>0,C102:C114)),""
)

Hi Harlan
That's a nice one!

Frank
 
...
...
or just enter...

=SUM(C102:C114)/MAX(1,COUNT(C102:C114)-COUNTIF(C102:C114,0))
Click to expand...
...

Brings up an interesting set of questions. First, what does the OP want to
display when there are non nonzero numbers in the range? The formula above
displays 0. If the OP's actual specs are to average only positive values, then a
zero result would clearly indicate that there was nothing to average. However,
the OP's specs leave the possibility that there may be negative numbers in the
range, so it's possible that the average of positive and negative numbers could
be 0, thus leaving no way to distinguish between an actual 0 result and 0
indicating no nonnegative numbers to average. Personally, I'd want to avoid
showing ambiguous results, but the OP may accept this.
 
Well, c102 to c114 is data composing the difference between two weeks
of mileages of a vehicles. Every week, new mileage will be put in and
C1xx will be updated. Until the end of the quarter, some of the cells
will be blank. I'm trying to get a running total of weekly averages
that a vehicle puts on. Let me give you an example.

C4 through c59 is the actual mileage per week, entered every week. So
until that actual week comes, the cell for that week remains blank.
Because I need to find the difference between one week and its
previous week (the total miles it put on that week), I need to create
another weekly chart showing the difference between the weeks. That
consists of c63 to c114. Split into quarters, I'm dealing with the 4th
quarter of the year (c102 to c114).

In order to get weekly averages, I need to average those weeks.
However, I run into a problem where on the cusp of figuring out how
many miles a vehicle put on, it takes data from a week with 0 miles
(the next week, which hasn't happened yet) and subtracts it from the
current week, leaving me with something like -167,548 miles for that
week. I need to remove that number in order to get an accurate
average. Likewise, if I don't get mileage for one week, but do get it
the next week, I'll end end up getting 167,548 miles for that one
week. Again, that screws up the average.

Hence, I need to check to make sure that there are no zeros being used
in the averaging function (creating those wildly high or negative
numbers).

Hope this makes sense.

Dave
 
Back
Top