Monday, April 25, 2016

Excel formula : Find Bottom 5 values excluding zero

Query:

I have list of numbers from that i want to extract smallest 5 numbers excluding zero.

Solution:

we can use combination of Small & count if to get the same. Copy below formula and paste down to cell B1 to B5

=SMALL($A$1:$A$100,COUNTIF($A$1:$A$100,0)+ROW(A1))



Here first count if formula will count for count of numbers equal to zero & will add 1 to it for finding lowest number greater than zero and so on.

Cheers!!