compare a list of data and have it listed from least to greatest in another column

  • Thread starter Thread starter dominix
  • Start date Start date
D

dominix

This is my first post here so bare with me.

I need help with a report I'm making. Each month we gather data on
safety items. We track which items are safe and which are at risk. From
these 2 numbers I calculate the percentage each item is being done
safe.

example:

Item |Safe|At Risk|% Safe
Gloves | 10 | 5 | 50.00%

The report is on one sheet in the workbook and the data I enter is on
another. The calculations are made on the data sheet and then linked to
the report sheet. There are 40 items that we track. On the bottom of the
report is a place we put the top 5 at risk items. This is the top 5
items with the lowest % safe. I would like to have a formula that would
compare the % safe of all 40 items and then list them from least to
greatest in another area of the data sheet, from which I can link the
top 5 to the report.
 
One way ..

(Link to sample file at: http://www.savefile.com/files/1097065
File: Compare_n_ListAscendingSort_dominix_newusers.xls )

Assume this table is in Sheet1,
in A1:D41, data from row2 down

Item Safe At Risk % Safe
Gloves 10 5 50.00%
Item1 18 7 61.11%
Item2 16 5 68.75%
Item3 14 6 57.14%
Item4 18 4 77.78%
etc

Using an empty col E
Put in E2: =IF(D11="","",D11+ROW()/10^10)
Copy down to E41
(Leave E1 empty)

In Sheet2
-------
With the same headers in A1:D1, i.e.: Item Safe At Risk % Safe

Put in A2:
=INDEX(Sheet1!A:A,
MATCH(SMALL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0))
Copy A2 across to D2, fill down to D41

Sheet2 will return the full ascending sort (by % Safe) of the table in
Sheet1
Just do a simple link to pick off the top 5 from Sheet2 elsewhere if desired
 
Back
Top