Minimum Number

[woodpecker2]

Is it possible to use the MIN function to exclude zero's?

If I use =MIN(A1:A100) and there is a zero then the result shows zero.

Any help would be appreciated.

 

[Richard Schollar]

Hi

You can use an array formula to do this:

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

Confirmed with Ctrl+Shift+Enter

 

[woodpecker2]

Thankyou, that's solved my problem.

 

[Aladin Akyurek]

Is it possible to use the MIN function to exclude zero's?

If I use =MIN(A1:A100) and there is a zero then the result shows zero.

Any help would be appreciated.

For a range housing values >=0, also:

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

 

[woodpecker2]

I need further help with this problem.

I've discovered that I need to find the minimum value greater than zero but within different ranges. Example I may need the minimum for ranges A1:E5 and H1:L5 and N1:R5

Despite having done a search on the topic I'm unable to find exactly what I need.

Could someone help further.

Thanks

 

[Expiry]

Repeat the above array formula for each range, then do a =min on all of your results.

 

[woodpecker2]

Cracked it, I was forgetting the Ctrl, Shift and Enter to form the array.


=MIN((IF(A1:A369>0,A1:A369)),(IF(C1:C369>0,C1:C369)),(IF(E1:E369>0,E1:E369)))

 

[mortgageman]

I need further help with this problem.

I've discovered that I need to find the minimum value greater than zero but within different ranges. Example I may need the minimum for ranges A1:E5 and H1:L5 and N1:R5

Despite having done a search on the topic I'm unable to find exactly what I need.

Could someone help further.

Thanks

I see that you have "cracked it", but it you want, you could also define a non contiguous range and then continue to use either Richard's or Alladin's formula.

 

[Aladin Akyurek]

Cracked it, I was forgetting the Ctrl, Shift and Enter to form the array.


=MIN((IF(A1:A369>0,A1:A369)),(IF(C1:C369>0,C1:C369)),(IF(E1:E369>0,E1:E369)))

Also...

Control+shift+enter:

=MIN(IF(CHOOSE({1,2,3},A1:A369,C1:C369,E1:E369)>0,CHOOSE({1,2,3},A1:A369,C1:C369,E1:E369)))