getting the min and max of a list of numbers

[dal_101]

I need to get the maximum and minimum values in a list of numbers, some of
the cells have 0 (suppressed), and I do not want to include those, and not
get the value output 0??? I can not delete the zero values either I just dont
want to include them.

in other words....ex: A1:A10 is 10,20,30,0,40,50,60,70,80,90. How do I get
the min and max of all the numbers except the zero?

Please help!

 

[Tom Hutchins]

Here's one way...

For the Minimum:
=IF(MIN(A1:A10)=0,SMALL(A1:A10,2),MIN(A1:A10))

For the Maximum:
=IF(MAX(A1:A10)=0,LARGE(A1:A10,2),MAX(A1:A10))

Hope this helps,

Hutch

 

[Pete_UK]

MAX won't be affected by the zero (unless you had negative numbers),
so you can do:

=MAX(A1:A10)

For the minimum, excluding zeros, you can use this array* formula:

=MIN(IF(A1:A10<>0,A1:A10,10E100)

10E100 is just a big number, and zeroes are replaced by this (in the
formula, not in the cells) so that they have no effect.

*An array formula must be committed using the key combination of CTRL-
SHIFT-ENTER (CSE) instead of the normal ENTER. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. You need
to use CSE again if you subsequently edit/amend the formula.

Hope this helps.

Pete

 

[ShaneDevenshire]

Hi,

=MIN(IF(A1:A10<>0,A1:A10,""))

and

=MAX(IF(A1:A10<>0,A1:A10,""))

Both of these formulas are arrays - to enter them you press Shift+Ctrl+Enter
rather than enter.

If this helps please click the Yes button

 

[T. Valko]

ex: A1:A10 is 10,20,30,0,40,50,60,70,80,90.

Not sure how much "robustness" you want/need but try these.

Assuming there are no negative numbers:

For the MIN:

=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)

For the MAX:

=MAX(A1:A10)

 

[dal_101]

Thank You. Could you explain to me what that formula means? I know the
=MIN(If(the range is less than or greater than 0, true will return the range
minimum, value_if_false???) Why would you use that ("")? Because there isnt
going to be a false agrument? Im just wondering for future reference, in
other basic formulas could I use the same things to not include zero values
and using "" to not have anything returned? Thank you sooooo much btw!!!!

 

[T. Valko]

Why would you use that ("")?

You don't need it. If the logical_test is FALSE then it evaluates as ""
which is an empty *TEXT* string and the MIN/MAX functions will ignore text.

You can leave it out and you will still get the same result:

=MIN(IF(A1:A10<>0,A1:A10))

In this version if the logical_test is FALSE and no value_if_false argument
is included then it defaults to FALSE and the MIN/MAX functions will also
ignore that.

 

[David Biddulph]

No good if there's more than one zero. Shane's formula would be better.

 

[Frenz]

Hi Biff,
I've a question..
My MIN formula is:
=MIN((IF(AF5="N",AB5)),(IF(AO5="N",AK5)),(IF(AX5="N",AT5)),(IF(BG5="N",BC5)),(IF(BP5="N",BL5)))

When i click on evaluate formula, the result is:
=MIN($AB$5,$AK$5,FALSE,FALSE,FALSE) --> Where my cell $AB$5 = 1.17 & cell
$AK$5 is 0.847, i get a result of 0.

Would you be able to enlighten me why this is so?
I thought text are not considered in MIN formula?

Frenz

 

[T. Valko]

=MIN($AB$5,$AK$5,FALSE,FALSE,FALSE)

MIN/MAX only ignore TEXT and logicals (TRUE, FALSE) when they are part of an
array or a range reference. Since you're referencing individual cells Excel
evaluates the result of the IF, logical FALSE, as 0 and that is the result
you're getting.

Try this simple test. Enter these formulas in some cells and you'll see what
I mean:

=MIN(10,"x")
=MIN(10,FALSE)
=MIN(10,TRUE)

Now, if we force the text and logicals into arrays then we'll get the
expected results:

=MIN(10,{"x"})
=MIN(10,{FALSE})
=MIN(10,{TRUE})

So, you need to do the same thing with your formula:

=MIN(IF(AF5="N",AB5,{""}),IF(AO5="N",AK5,{""}),IF(AX5="N",AT5,{""}),IF(BG5="N",BC5,{""}),IF(BP5="N",BL5,{""}))