Muhammad Saleem asks:
I a column with over 400 entries. Most of them are 0s. I would like to list the 5 smallest numbers excluding 0s. What is the best possible formula?

Answer:

Array formula in cell C1:

=SMALL(IF($A$1:$A$400<>0, $A$1:$A$400, ""), ROW(A1))

How to create an array formula

  1. Select cell C1
  2. Type array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter once
  5. Release all keys

How to copy array formula

  1. Select cell C1
  2. Copy (Ctrl + c)
  3. Select cell C2:C5
  4. Paste (Ctrl + v)

Download excel 97-2003 *.xls file
list 5 smallest values excluding zeros.xls

What formula would you use?

Related posts:

Excel: Find the smallest value in a list but bigger than 10.

Highlight smallest duplicate value in a column using conditional formatting in excel

Excel: Find the smallest difference between the two closest values

Return row reference of largest to smallest

Excel: Average but exclude zeros