Ignore Errors

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a long list of values for which I need to calculate MAX MIN and
MEDIAN values. The problem I have is that the data set I have is calculated
from a formula. The data set is cumulative so in the cells yet to have data
input have the error #DIV/0! and when I try the above functions across the
entire range they return #DIV/0! as the result.

Currently I have to update the ranges whenever new data is entered.

Is there a formula I can use that will only calculate MAX, MIN and MEDIAN
for cells with real numbers > 0 in the range and not include the errors, so I
don't have to keep updating the ranges?

Hope this makes sense.

Thankyou in advance for your help.

Cheers!

Clint
 
You may be able to use something like:

=IF(COUNT(A1:A10,">"&0)=0,"No numbers!",
MAX(IF(ISNUMBER(A1:A10),IF(A1:A10>0,A1:A10))))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.
 
Hello Zoltan

I'd say that the easiest way to proceed is to try to eliminate the errors to
start with, then you can just use a regular MEDIAN, MIN or MAX formula.

What formula gives #DIV/0! ?

If you have a formula like =A1/B1 change to

=IF(B1,A1/B1,"")
 
Back
Top