can I do this on excel?

[fireman175]

I need to set up a spreadsheet that keeps track of the total hours worked of
three groups. Within those groups are 10 employees. The trick I need
addressed is that while each individual has a total number of hours worked
for the year, we call the person with the lowest number of total hours for
the year.

Sometimes we use only one group, sometimes all three, to find the person
with the lowest total hours. It depends on their class and which group has
qualified for the hours to be worked.

I would like to simply click on something, like the yellow and green group,
and the correct calling order from lowest to highest hours would be indicated
somehow. Thus, after entering a number of hours one employee worked, that
amount would be reflected in her/his total hours and that person would be
moved down the list.

Do you think this can be done on Excel or do you have any other suggestions?

I appreciate your assistance! CP

 

[Pete_UK]

Imagine that one of your groups of 10 is set up as follows:

Name Hours
Name_1 53
Name_2 21
Name_3 45
Name_4 67
Name_5 37
Name_6 68
Name_7 31
Name_8 25
Name_9 38
Name_10 41

with a header row and data occupying A2 to B11. You could put these two
formulae as follows:

B14: =SMALL(B$2:B$11,ROWS(A$14:A14))
A14: =INDEX(A$2:A$11,MATCH(B14,B$2:B$11,0),1)

B14 finds the smallest value in the range B2:B11, and A14 uses this to
return the name.
Copy these two formulae down to row 23, and you should get the
following:

Name_2 21
Name_8 25
Name_7 31
Name_5 37
Name_9 38
Name_10 41
Name_3 45
Name_1 53
Name_4 67
Name_6 68

i.e. the list has now been sorted with the smallest hours at the top -
change B3 to 27 and the names and values automatically adjust positions
in the lower table. Perhaps you can see how to apply this to your other
two groups - in my tests I put some sample data in D1:E11 and in G1:H11
with appropriate formulae below.

Hope this helps.

Pete

 

[fireman175]

Thank you Pete_UK,

That makes sense, however can I temporarily merge two groups to determine
the order then?

Carl

 

[fireman175]

Pete,

I tried your setup and it worked well until I used the same number of hours
for two names. In that instance, one of the name disappeared in the ranked
column and the other showed up twice. Any suggestion to a solution on that
one? Also, as i asked in my other reply, I would like to keep the three
groups separate, but would like to be able to select two or three groups
sometimes to get a ranked order of lowest to highest.

Do you have any thoughts on a formula for that request?

Thanks again for your help on this!

Carl

 

[Pete_UK]

I had thought that if you had the three ranked groups together side by
side then you wouldn't have to combine them, but you could easily see
the lowest across all three groups.

As for taking proper account of ties, you might find this link to Chip
Pearson's site helpful:

http://www.cpearson.com/excel/rank.htm

He shows various ways of ranking data, so you should be able to derive
a solution with one of his techniques.

Hope this helps.

Pete

 

[fireman175]

Thank you again Pete,

I will check out that site.

Carl

 

[Joseph R. Pottschmidt]

Dear Fireman175:

Since Excel is a great tool for calculating information and sorting flat
file information, it would be a lot easier for you in the long run as
the data in the excel spreadsheet grows, that you would put this
information into a MS Access database and then you can sort filter,
group ungroup the information in anyway or ways that you want.

Just a thought,

Joe P.


-----Original Message-----
From: fireman175 [mailto:[email protected]]
Posted At: Wednesday, May 31, 2006 7:24 PM
Posted To: microsoft.public.excel.setup
Conversation: can I do this on excel?
Subject: can I do this on excel?

I need to set up a spreadsheet that keeps track of the total hours
worked of
three groups. Within those groups are 10 employees. The trick I need
addressed is that while each individual has a total number of hours
worked
for the year, we call the person with the lowest number of total hours
for
the year.

Sometimes we use only one group, sometimes all three, to find the person

with the lowest total hours. It depends on their class and which group
has
qualified for the hours to be worked.

I would like to simply click on something, like the yellow and green
group,
and the correct calling order from lowest to highest hours would be
indicated
somehow. Thus, after entering a number of hours one employee worked,
that
amount would be reflected in her/his total hours and that person would
be
moved down the list.

Do you think this can be done on Excel or do you have any other
suggestions?

I appreciate your assistance! CP