If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
can I do this on excel?
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 wrote: 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 |
#3
|
|||
|
|||
can I do this on excel?
Thank you Pete_UK,
That makes sense, however can I temporarily merge two groups to determine the order then? Carl "Pete_UK" wrote: 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 wrote: 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 |
#4
|
|||
|
|||
can I do this on excel?
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" wrote: 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 wrote: 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 |
#5
|
|||
|
|||
can I do this on excel?
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 wrote: 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" wrote: 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 wrote: 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 |
#6
|
|||
|
|||
can I do this on excel?
Thank you again Pete,
I will check out that site. Carl "Pete_UK" wrote: 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 wrote: 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" wrote: 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 wrote: 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 |
#7
|
|||
|
|||
can I do this on excel?
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 ] 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can we import an Excel sheet to use as base for report? | StargateFan | New Users | 9 | November 4th, 2005 07:32 PM |
outputting memo fields to excel | John Baker | Running & Setting Up Queries | 7 | August 12th, 2004 01:23 PM |
Add-in functions when starting Excel programmatically | Steve K. | Worksheet Functions | 11 | July 2nd, 2004 06:38 PM |
Coverting Lotus 123 files to use with excel | Muffin1947 | General Discussion | 6 | June 20th, 2004 10:18 AM |