Getting around limitations with MIN function

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
 
P

Pete_UK

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
 
P

paulkaye

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
 
P

Pete_UK

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
 
P

paulkaye

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...
 
R

Roger Govier

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.
 
P

paulkaye

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
 
R

Roger Govier

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"
 
Top