Search code examples
excelformulamin

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.

Solution

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