Min/Max to ignore error values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Graham Tritton - 05 Feb 2008 01:19 GMT

I have a series that contains #N/A errors results from a formula. I want to
calculate the min/max of the actuals values ignoring the error values. Any
ideas? I have tried min() & mina() but returning errors.

Reply to this Message

RagDyer - 05 Feb 2008 02:04 GMT

It's always advisable to correct the error in the original formula.

Why not post the formula that's returning the error so that a suggestion can
be made to eliminate it.

If you insist on keeping the errors visible in the range, you can try
something like this *array* formula:

=MIN(IF(NOT(ISNA(A1:A5)),A1:A5))

Signature

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

>I have a series that contains #N/A errors results from a formula. I want to
> calculate the min/max of the actuals values ignoring the error values. Any
> ideas? I have tried min() & mina() but returning errors.

Reply to this Message

Dave Peterson - 05 Feb 2008 02:12 GMT

Another:

=if(count(a1:a10)=0,"No numbers",min(if(isnumber(a1:a10),a1:a10)))

Still array-entered.

> I have a series that contains #N/A errors results from a formula. I want to
> calculate the min/max of the actuals values ignoring the error values. Any
> ideas? I have tried min() & mina() but returning errors.

Signature

Dave Peterson

Reply to this Message