getting the min and max of a list of numbers

  • Thread starter Thread starter dal_101
  • Start date Start date
D

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!
 
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
 
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
 
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
 
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)
 
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!!!!
 
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.
 
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
 
=MIN($AB$5,$AK$5,FALSE,FALSE,FALSE)
Click to expand...

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,{""}))
 
Back
Top