Find the 5 lowest values

[jacko53]

Hi

I have created a spreadsheet showing a list of food items and the total cost lost in pounds. I am trying to figure out what formula I use to find the five lowest values and show the five names and total costs in a table of its own. I have attached a quick print screen of the spreadsheet. Any help would be grateful.

example.jpg

[AlKey]

Try this formula

=IF(ROWS($H$4:H4)>5,"",SMALL($H$4:$H$15,ROWS($H$4:H4)))

and pull formula down

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F	G	H
1	Item	Loss		Bottom	5		Item	Loss
2	Item1	47		Count	5		Item10	9
3	Item2	59					Item8	10
4	Item3	13					Item3	13
5	Item4	35					Item6	32
6	Item5	66					Item4	35
7	Item6	32
8	Item7	39
9	Item8	10
10	Item9	70
11	Item10	9
12	------	------	------	------	------	------	------	------

A top/bottom N list might have more than N records depending on ties.

Enter this formula in E2. This will return the count of records that are within the bottom 5.

=COUNTIF(B2:B11,"<="&SMALL(B2:B11,E1))

Enter this array formula** in G2:

=IF(H2="","",INDEX(A:A,SMALL(IF(B$2:B$11=H2,ROW(B$2:B$11)),COUNTIF(H$2:H2,H2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Enter this formula in H2:

=IF(ROWS(H$2:H2)>E$2,"",SMALL(B$2:B$11,ROWS(H$2:H2)))

Select G2:H2 and copy down until you get blanks.

[jacko53]

I keep getting #NUM! when the formula is entered

[micope21]

Hi

See the file!

Info are in the file!

Regard
micope21

[Tony Valko]

I keep getting #NUM! when the formula is entered

Did you try the technique in post #3?