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

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"dominix" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> dominix
> ------------------------------------------------------------------------
> dominix's Profile:
http://www.excelforum.com/member.php...o&userid=27497
> View this thread: http://www.excelforum.com/showthread...hreadid=470126
>

Oops, pasted the from the wrong cell:

Line
> Put in E2: =IF(D11="","",D11+ROW()/10^10)

should read as:
Put in E2: =IF(D2="","",D2+ROW()/10^10)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--