Min value but not 0

  • Thread starter Thread starter Frank
  • Start date Start date
F

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
 
=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
 
Frank

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

this is an array formula so commit with CTRL+SHFT+ENTER
 
This is a multi-part message in MIME format.
--------------040805090601060207060502
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Click to expand...

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 ?
Click to expand...
...

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

or

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

The latter is an array formula.
 
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
 
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)
 
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
 
Back
Top