Min value but not 0

[Frank]

I have the folowing data set. I need to find the max value, on problem
use max() but I also need to find the min value that is not 0 (i.e.
19). any way to do that ?

Thanks,

Frank


19
0
0
0
0
0
0
19
19
0
19
19
19
19
19
19
0
51
0
0
0
19
0
51
0
0
0
0
0
0

 

[Bob Phillips]

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

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have the folowing data set. I need to find the max value, on problem use max() but I also need to find the min value that is not 0 (i.e. 19). any way to do that ?

Thanks,

Frank


19
0
0
0
0
0
0
19
19
0
19
19
19
19
19
19
0
51
0
0
0
19
0
51
0
0
0
0
0
0

 

[Juan Sanchez]

Frank

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

this is an array formula so commit with CTRL+SHFT+ENTER

 

[Ken Wright]

=MIN(IF(A1:A100,A1:A100))

 

[Harlan Grove]

This is a multi-part message in MIME format.
--------------040805090601060207060502
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Post in plain text only.

I have the folowing data set. I need to find the max value, on problem
use max() but I also need to find the min value that is not 0 (i.e.
19). any way to do that ?

...

=SMALL(RangeRef,COUNTIF(RangeRef,"<=0")+1)

or

=MIN(IF(RangeRef>0,RangeRef))

The latter is an array formula.

 

[Frank]

doesn't seem to work, still get zero?

 

[Frank]

get a #value using this. Why is this not working?

 

[Frank]

I get a #value! using this. I cut and pasted it no no typo's what could
be happening?

 

[Frank]

using the

CTRL+SHFT+ENTER I get a decinal number? What in the world is goign on?

 

[Juan Sanchez]

you get the #value because its an array formula, it means
that once you finish typing it intead of just using
<Enter> you need to use <CTR+SHFT+ENTER> (same time all 3)

You'll know if you did it right if brackets apperar
enclosing the formula: {=min(if(a1:a14<>,a1:a14))}

Cheers
Juan

 

[Frank]

Thansk that did it, but the weird thing is that it displays 19.038 etc why?

 

[Bob Phillips]

That's because I forgot to tell you that it is an array formula, so commit
it with Ctrl-Shift-Enter rather than just Enter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Juan Sanchez]

Chances are that's the minimum value in your cells, but
decimals are not displaying, if you want only integers to
display then use the INT function:

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

Cheers
Juan