Using MIN but weeding out 0 values

henway

Board Regular
Joined
Jul 19, 2006
Messages
85
I have 2 columns of prices. Some are filled in and some have never been quoted and are thus "0".

How do I create a formula that shows the smallest number except for 0?

Thanks!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Assume your range was A1:A10:
Code:
{=MIN(IF(A1:A10>0,A1:A10))}

Must be entered as an array formula - with Ctrl+Shift+Enter, not just Enter.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
If you will never be without a zero value then:

=SMALL(A1:A9,2)

Which picks the smallest number not = to 0 if zero is the smallest value in the range. [AKA the 2nd smallest number, zero being the 1st smallest number.] So, you cannot have negative numbers or these will be smaller than zero, giving a chance that zero now becomes the second smallest number!

So, your list must have at least one zero and cannot have any negative numbers.
 

henway

Board Regular
Joined
Jul 19, 2006
Messages
85
Thanks to all!
The CSE method wil work the best for me!

Regards,
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
You should note that the above link's formula [shown here] will also only work with positive numbers:

=SMALL(AJ2:AP2,COUNTIF(AJ2:AP2,0)+1)

If you have negative number(s) you can return a value of 0, if it is the next higher value!
 

Forum statistics

Threads
1,140,941
Messages
5,703,292
Members
421,290
Latest member
java

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top