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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Enter the following into the result cell and then press Ctrl   amp  Shift while pushing ENTER    MIN If A1 E1 gt 0 A1 E1

User · Answer

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

User · Answer

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

User · Answer

By far the most efficient method is to use the SMALL and COUNTIF formula as shown below   SMALL Returns the k-th smallest value in a data set    SMALL A1 A100 COUNTIF  A 1  A 100 0  1   Where the countif is counting the zeros in the range   1  and is used to tell SMALL to return the k-th smallest value   Credit  link

[excel] How to exclude 0 from MIN formula Excel

Examples related to excel

Examples related to formula

Examples related to min