CODEWITHSUNDEEP
excelformulamin
Dario
Dario

Reputation: 714

How to exclude 0 from MIN formula Excel

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.

Upvotes: 11

Views: 170234

Answers (9)

Mehmet Maraz
Mehmet Maraz

Reputation: 53

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

Upvotes: 1

learnAsWeGo
learnAsWeGo

Reputation: 2282

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

Upvotes: 1

Alex Jean
Alex Jean

Reputation: 682

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.

Upvotes: 2

user7982204
user7982204

Reputation: 1

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

Upvotes: -4

Orbay Chakar
Orbay Chakar

Reputation: 1

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

Upvotes: 0

Kitanski
Kitanski

Reputation: 49

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

Upvotes: 2

Bathsheba
Bathsheba

Reputation: 234695

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.

Upvotes: 3

barry houdini
barry houdini

Reputation: 46341

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.....

Upvotes: 10

Chief Wiggum
Chief Wiggum

Reputation: 2934

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

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

Upvotes: 13

Related Questions