Getting around limitations with MIN function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paulkaye - 15 Oct 2007 07:52 GMT

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

Reply to this Message

Pete_UK - 15 Oct 2007 09:07 GMT

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

> I have a range of calculated values and want to display the smallest
> value in an adjacent cell. However, some of the cells (correctly)
[quoted text clipped - 6 lines]
>
> Paul

Reply to this Message

paulkaye - 15 Oct 2007 09:19 GMT

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

> 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
[quoted text clipped - 21 lines]
>
> > Paul

Reply to this Message

Pete_UK - 15 Oct 2007 09:29 GMT

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

> Hi Pete,
>
[quoted text clipped - 40 lines]
>
> - Show quoted text -

Reply to this Message

paulkaye - 15 Oct 2007 09:43 GMT

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:
[quoted text clipped - 57 lines]
>
> > - Show quoted text -

Reply to this Message

Pete_UK - 15 Oct 2007 10:28 GMT

Thanks for the feedback, Paul.

Pete

> Superb! Thank you - that's perfect.
>
[quoted text clipped - 61 lines]
>
> - Show quoted text -

Reply to this Message

Roger Govier - 15 Oct 2007 09:47 GMT

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.

Signature

Regards
Roger Govier

> Hi Pete,
>
[quoted text clipped - 38 lines]
>>
>> > Paul

Reply to this Message

paulkaye - 15 Oct 2007 11:02 GMT

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

On Oct 15, 10:47 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk>
wrote:

> Hi Paul
>
[quoted text clipped - 52 lines]
>
> >> > Paul

Reply to this Message

Roger Govier - 15 Oct 2007 11:31 GMT

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"

Signature

Regards
Roger Govier

> Hi Roger,
>
[quoted text clipped - 66 lines]
>>
>> >> > Paul

Reply to this Message