Getting around limitations with MIN function

  • Thread starter Thread starter paulkaye
  • Start date Start date
P

paulkaye

I have a range of calculated values and want to display the smallest
value in an adjacent cell. However, some of the cells (correctly)
return #DIV/0! errors and, as the help file explains, the MIN function
subsequently returns this same error. Can anyone think of a way of
getting around this problem? I imagine there must be a way to only
count valid numbers or something similar.

Thanks in advance!

Paul
 
It isn't correct to return errors! You can avoid the #DIV/0 error
(which comes about by trying to divide by zero) by adjusting your
formulae like this:

=IF(divisor=0,"error",your_current__formula)

where divisor is the part of your formula which is dividing into the
other part. You could replace "error" with "" to get a blank cell. In
both cases the MIN function would give you the correct minimum of the
numeric values.

Hope this helps.

Pete
 
Hi Pete,

Is this meant to replace the original formula which produces the
original #DIV/0! error, or the MIN formula which uses that error?

The original error is correct insomuch as the formula is present in a
row of cells and only certain source cells contain data. When the
formula refers to a blank cell, it returns the error and this is fine
with me (I've conditionally formatted the text white when this
happens).

I was hoping to have a situation where the MIN function in the second
formula would only take into account cells containing real numbers. Is
there any way to do this?

Paul
 
My solution called for you to amend the formulae you currently have
that are giving you errors. So if you have something like:

=A2/B2

and B2 is empty or 0, then change this to:

=IF(B2=0,"",A2/B2)

Then you will have a (proper) blank cell - no need for font changes -
and the MIN function over the range of these result cells will work
correctly.

Hope this helps.

Pete
 
Superb! Thank you - that's perfect.

My solution called for you to amend the formulae you currently have
that are giving you errors. So if you have something like:

=A2/B2

and B2 is empty or 0, then change this to:

=IF(B2=0,"",A2/B2)

Then you will have a (proper) blank cell - no need for font changes -
and the MIN function over the range of these result cells will work
correctly.

Hope this helps.

Pete
Click to expand...
 
Hi Paul

You could use an array formula

{=MIN(IF(ISNUMBER(C1:C14),C1:C14))}

Array formulas are created or edited using Control+Shift+Enter (CSE), not
just Enter.
When you use CSE, Excel will insert the curly braces { } around the
formula. Do not type them yourself.
 
Hi Roger,

Thanks. That deals with the problem at the other side of the first
equation. It's great to see how I could have done that too although
I've used an 'upstream' solution now. It blows my mind how many ways
there seem to be to do things!

Best regards,

Paul
 
Hi Paul

Pete gave you the best solution. It is always better to prevent #N/A results
in your equations.
However, as you had posted that you wanted to know if anything could be done
with the Min function itself, I posted this answer.

Always in Excel, there are "many ways to skin the cat"
 
Back
Top