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

Have a look in the help index for ISNA

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zoltan" wrote in message
...
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:A100,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.



Zoltan wrote:

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

--

Dave Peterson

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

"Dave Peterson" wrote:

You may be able to use something like:

=IF(COUNT(A1:A10,""&0)=0,"No numbers!",
MAX(IF(ISNUMBER(A1:A10),IF(A1:A100,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.



Zoltan wrote:

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

--

Dave Peterson