How to exclude 0 from MIN formula Excel

Question

i need to know how can i exclude 0 from rows and get the MIN Value.

But also i need to exlude the F1 Cell.

Ex:

A  B  C    D  E   F
1  0  18  20  0  150  = 18

but if i do this In excel with =MIN(A1,B1,C1,D1,E1) return 0.

Any help is appreciated.

Tmesis

If you want the Minimum value in A1, C1 and E1 but excluding zeroes wouldn't the answer be 1? - see my answer for that result – Eames

Answer 1

Try this formula

=SMALL((A1,C1,E1),INDEX(FREQUENCY((A1,C1,E1),0),1)+1)

Both SMALL and FREQUENCY functions accept "unions" as arguments, i.e. single cell references separated by commas and enclosed in brackets like (A1,C1,E1).

So the formula uses FREQUENCY and INDEX to find the number of zeroes in a range and if you add 1 to that you get the k value such that the kth smallest is always the minimum value excluding zero.

I'm assuming you don't have negative numbers.....

Eames

Answer 2

Enter the following into the result cell and then press Ctrl & Shift while pushing ENTER:

=MIN(If(A1:E1>0,A1:E1))

Kaluga

Hi @JamesBlond, is ok with this formula, but i need to pick only A1,C1,E1. Thanks a lot. – Tmesis

That's a bit more complicated and I would use the DMIN() function then. Have a look here for e very detailed description: spreadsheets.about.com/od/Database-Function-Tutorials/ss/… – Kaluga

Answer 3

Not entirely sure what you want here, but if you want to discount blank cells in the range and pass over zeros then this would do it; if a little contrived:

=MIN(IF(A1:E1=0,MAX(A1:E1),A1:E1))

With Ctrl+Shift+Enter as an array.

What I'm doing here is replacing zeros with the maximum value in the list.

Catalog

Answer 4

if all your value are positive, you can do -max(-n)

Psychiatry

That SEEMS like it should work, and I thought it was genius at first, but it needs to be done on the range and in excel you can't do "-" then a range. It doesn't work. You would need a construct similar to the COUNTIF one suggested above. – Triaxial

Answer 5

Solutions listed did not exactly work for me. The closest was Chief Wiggum - I wanted to add a comment on his answer but lack the reputation to do so. So I post as separate answer:

=MIN(IF(A1:E1>0;A1:E1))

Then instead of pressing ENTER, press CTRL+SHIFT+ENTER and watch Excel add { and } to respectively the beginning and the end of the formula (to activate the formula on array).

The comma "," and "If" statement as proposed by Chief Wiggum did not work on Excel Home and Student 2013. Need a semicolon ";" as well as full cap "IF" did the trick. Small syntax difference but took me 1.5 hour to figure out why I was getting an error and #VALUE.

Housewife

Well, this actually is an answer — it’s what worked for you, it’s understandable, someone else may find it useful. So, good job. Here, have an upvote, get you started on having enough rep to comment when a comment is the right thing :) – Strode

Thanks for the upvote and the supportive attitude. Really appreciate! :) – Housewife

Answer 6

Throwing my hat in the ring:

1) First we execute the NOT function on a set of integers, evaluating non-zeros to 0 and zeros to 1

2) Then we search for the MAX in our original set of integers

3) Then we multiply each number in the set generated in step 1 by the MAX found in step 2, setting ones as 0 and zeros as MAX

4) Then we add the set generated in step 3 to our original set

5) Lastly we look for the MIN in the set generated in step 4

{=MIN((NOT(A1:A5000)* MAX(A1:A5000))+ A1:A5000)}

If you know the rough range of numbers, you can replace the MAX(RANGE) with a constant. This speeds things up slightly, still not enough to compete with the faster functions.

---

Also did a quick test run on data set of 5000 integers with formula being executed 5000 times.

{=SMALL(A1:A5000,COUNTIF(A1:A5000,0)+1)}

1.700859 Seconds Elapsed | 5,301,902 Ticks Elapsed

{=SMALL(A1:A5000,INDEX(FREQUENCY(A1:A5000,0),1)+1)}

1.935807 Seconds Elapsed | 6,034,279 Ticks Elapsed

{=MIN((NOT(A1:A5000)* MAX(A1:A5000))+ A1:A5000)}

3.127774 Seconds Elapsed | 9,749,865 Ticks Elapsed

{=MIN(If(A1:A5000>0,A1:A5000))}

3.287850 Seconds Elapsed | 10,248,852 Ticks Elapsed

{"=MIN(((A1:A5000=0)* MAX(A1:A5000))+ A1:A5000)"}

3.328824 Seconds Elapsed | 10,376,576 Ticks Elapsed

{=MIN(IF(A1:A5000=0,MAX(A1:A5000),A1:A5000))}

3.394730 Seconds Elapsed | 10,582,017 Ticks Elapsed

Rescission

Well I couldn't agree more! – Catalog

@Catalog found that using NOT instead of = improves things a bit...but still not enough to hang with the fastest methods – Rescission

Answer 7

In Microsoft 365 you can use the new function MINIFS

=MINIFS(A1:E1;A1:E1;">0")

gives 1

=MINIFS(A1:E1;A1:E1;">1")

gives 18

*replace ; with , if using english version

Exaggerate

Answer 8

min() fuction exlude BOOLEAN and STRING values. if you replace your zeroes with "" (empty string) - min() function will do its job as you like!

Wembley

Answer 9

All you have to do is to delete the "0" in the cells that contain just that and try again. That should work.

Herbage