Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Im trying to determine the function(s) that would allow me to do several
things. First I will give a simple example of what I have: ’ I have two sheets, one has salary data you will notice that an individual can be in two different departments with different rates. This can occur when an employee works part of the month in one department and in another department for the remainder of the month. The rates would also vary depending upon the location of the department (or if the employee gets a raise and moves to a different department) ’ The second sheet has the main tracking data. ’ The third sheet is a report that is sent by corporate hq. It would have everyone broken down by department and give totals for each (in excel 2007 format also). Now here is what Im trying to do: 1. I want to pull the hours available and hours worked from the Corporate Work Report and put it in the tracking sheet for each person. I have created a separate sheet that each department fills in with their projections for hours worked etc and the function looks like this (only using 1 lookup value (not 2): =(VLOOKUP(B20,Arlington!$A$4:$O$18,15)) 2. Salary based % and hours based % are going to be identical for an individual, but when you total an entire group, the salary based % is generally lower. (An individual making big money who doesnt work that many hours affects the salary based % more) Hours based is easy as I would add up the total hours worked and then divide by the total hours available. But to generate the salary based % for an entire group (150+ employees), I would have to take the hours worked X hourly rate and the hours available X the hourly salary rate for every individual in the group and get a total of the each $ figure (Hours worked and hours available) to perform the division and determine the %. 3. I have created a line in the salary sheet that shows the ID as 0 and for the name it is new hire. This allows me to just add an individual when needed and when I put the ID in the tracking sheet, information will automatically fill out the remaining data. So when I do the group totals in the tracking sheet, it will include extra blank lines (that have no value) to allow for new people that get added, without having to insert and work the formulas again. Salary Sheet A B C D 1 ID Department Name Hourly Salary 2 123456 0125 Smith,Bob 43.6879 3 123456 0126 Smith,Bob 46.2599 4 004689 1228 Jones,Darren 38.2145 Tracking Sheet A B C D E F G 1 ID Dept Name Hours Available Hours Worked Hours based % Salary based % 2 123456 0125 Smith,Bob 100 95 3 123456 0126 Smith,Bob 78 70 4 004689 1228 Jones,Darren 178 164 Group Total 356 329 Corporate Work Report Sheet A B C D E F G H 1 ID Dept Name Monthly Work Hours Holiday Time Off Available Hrs Hours Worked 2 123456 0125 Smith,Bob 108 8 8 92 88 3 006589 0125 Mack,Sarah 184 8 32 144 139.5 4 123456 0126 Smith,Bob 78 0 0 78 76.5 Group Total 370 16 40 314 304 I know this is alot of info, but I have used simple VLOOKUP formulas to pull in most of the data, but when I realized that someone could have different rates depending upon the department (or if there is a salary increase), the having the VLOOPUP reference one lookup value would not work. When I total for an entire group, I created a function with the VLOOKUP and for just 3 people, it was 2 Β½ lines long, so I I were to do this for 30 people or more, it would be so long that it seems that there would be an easier way. Here is the function: =((VLOOKUP(B6,'Salary Data'!$A$2:$E$90,5)*H6)+(VLOOKUP(B7,'Salary Data'!$A$2: $E$90,5)*H7)+(VLOOKUP(B8,'Salary Data'!$A$2:$E$90,5)*H8))/((VLOOKUP(B6, 'Salary Data'!$A$2:$E$90,5)*G6)+(VLOOKUP(B7,'Salary Data'!$A$2:$E$90,5)*G7)+ (VLOOKUP(B8,'Salary Data'!$A$2:$E$90,5)*G8)) If anyone can give me some tips, it sure would help. Steve |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I think the reason no one has replied to your is that your question is too
long. I think the way to do this would be to read the data into an array or two and then just write your report out after doing the calculations in the macro. I'd be happy to get you started but don't understand what you want to do. "ssolomon" wrote: Im trying to determine the function(s) that would allow me to do several things. First I will give a simple example of what I have: ’ I have two sheets, one has salary data you will notice that an individual can be in two different departments with different rates. This can occur when an employee works part of the month in one department and in another department for the remainder of the month. The rates would also vary depending upon the location of the department (or if the employee gets a raise and moves to a different department) ’ The second sheet has the main tracking data. ’ The third sheet is a report that is sent by corporate hq. It would have everyone broken down by department and give totals for each (in excel 2007 format also). Now here is what Im trying to do: 1. I want to pull the hours available and hours worked from the Corporate Work Report and put it in the tracking sheet for each person. I have created a separate sheet that each department fills in with their projections for hours worked etc and the function looks like this (only using 1 lookup value (not 2): =(VLOOKUP(B20,Arlington!$A$4:$O$18,15)) 2. Salary based % and hours based % are going to be identical for an individual, but when you total an entire group, the salary based % is generally lower. (An individual making big money who doesnt work that many hours affects the salary based % more) Hours based is easy as I would add up the total hours worked and then divide by the total hours available. But to generate the salary based % for an entire group (150+ employees), I would have to take the hours worked X hourly rate and the hours available X the hourly salary rate for every individual in the group and get a total of the each $ figure (Hours worked and hours available) to perform the division and determine the %. 3. I have created a line in the salary sheet that shows the ID as 0 and for the name it is new hire. This allows me to just add an individual when needed and when I put the ID in the tracking sheet, information will automatically fill out the remaining data. So when I do the group totals in the tracking sheet, it will include extra blank lines (that have no value) to allow for new people that get added, without having to insert and work the formulas again. Salary Sheet A B C D 1 ID Department Name Hourly Salary 2 123456 0125 Smith,Bob 43.6879 3 123456 0126 Smith,Bob 46.2599 4 004689 1228 Jones,Darren 38.2145 Tracking Sheet A B C D E F G 1 ID Dept Name Hours Available Hours Worked Hours based % Salary based % 2 123456 0125 Smith,Bob 100 95 3 123456 0126 Smith,Bob 78 70 4 004689 1228 Jones,Darren 178 164 Group Total 356 329 Corporate Work Report Sheet A B C D E F G H 1 ID Dept Name Monthly Work Hours Holiday Time Off Available Hrs Hours Worked 2 123456 0125 Smith,Bob 108 8 8 92 88 3 006589 0125 Mack,Sarah 184 8 32 144 139.5 4 123456 0126 Smith,Bob 78 0 0 78 76.5 Group Total 370 16 40 314 304 I know this is alot of info, but I have used simple VLOOKUP formulas to pull in most of the data, but when I realized that someone could have different rates depending upon the department (or if there is a salary increase), the having the VLOOPUP reference one lookup value would not work. When I total for an entire group, I created a function with the VLOOKUP and for just 3 people, it was 2 Β½ lines long, so I I were to do this for 30 people or more, it would be so long that it seems that there would be an easier way. Here is the function: =((VLOOKUP(B6,'Salary Data'!$A$2:$E$90,5)*H6)+(VLOOKUP(B7,'Salary Data'!$A$2: $E$90,5)*H7)+(VLOOKUP(B8,'Salary Data'!$A$2:$E$90,5)*H8))/((VLOOKUP(B6, 'Salary Data'!$A$2:$E$90,5)*G6)+(VLOOKUP(B7,'Salary Data'!$A$2:$E$90,5)*G7)+ (VLOOKUP(B8,'Salary Data'!$A$2:$E$90,5)*G8)) If anyone can give me some tips, it sure would help. Steve |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Okay, I think I figured out what you want to do. To do this the easy way
would be to create an array. The first element would be the concatenation of Employee & Dept. so that you create a unique "Employee" based on each combination. Then you would read all that the forecast data into an array, then add the actual data to the array, and then write out the report. In my opinion, this would be way easier in the long run to set up and maintain. When I have a bit more time, I'll work on designing this and send to you. |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Looking back at the message, your probably correct..way too long.
I'm going to have to start studying about array's as I'm new to this. I appreciate the help! Thanks, Steve Mike H. wrote: Okay, I think I figured out what you want to do. To do this the easy way would be to create an array. The first element would be the concatenation of Employee & Dept. so that you create a unique "Employee" based on each combination. Then you would read all that the forecast data into an array, then add the actual data to the array, and then write out the report. In my opinion, this would be way easier in the long run to set up and maintain. When I have a bit more time, I'll work on designing this and send to you. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200711/1 |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I have no idea what the salary base percentages are? I don't understand what
is in the tracking sheet. Please advice. "ssolomon" wrote: Im trying to determine the function(s) that would allow me to do several things. First I will give a simple example of what I have: ’ I have two sheets, one has salary data you will notice that an individual can be in two different departments with different rates. This can occur when an employee works part of the month in one department and in another department for the remainder of the month. The rates would also vary depending upon the location of the department (or if the employee gets a raise and moves to a different department) ’ The second sheet has the main tracking data. ’ The third sheet is a report that is sent by corporate hq. It would have everyone broken down by department and give totals for each (in excel 2007 format also). Now here is what Im trying to do: 1. I want to pull the hours available and hours worked from the Corporate Work Report and put it in the tracking sheet for each person. I have created a separate sheet that each department fills in with their projections for hours worked etc and the function looks like this (only using 1 lookup value (not 2): =(VLOOKUP(B20,Arlington!$A$4:$O$18,15)) 2. Salary based % and hours based % are going to be identical for an individual, but when you total an entire group, the salary based % is generally lower. (An individual making big money who doesnt work that many hours affects the salary based % more) Hours based is easy as I would add up the total hours worked and then divide by the total hours available. But to generate the salary based % for an entire group (150+ employees), I would have to take the hours worked X hourly rate and the hours available X the hourly salary rate for every individual in the group and get a total of the each $ figure (Hours worked and hours available) to perform the division and determine the %. 3. I have created a line in the salary sheet that shows the ID as 0 and for the name it is new hire. This allows me to just add an individual when needed and when I put the ID in the tracking sheet, information will automatically fill out the remaining data. So when I do the group totals in the tracking sheet, it will include extra blank lines (that have no value) to allow for new people that get added, without having to insert and work the formulas again. Salary Sheet A B C D 1 ID Department Name Hourly Salary 2 123456 0125 Smith,Bob 43.6879 3 123456 0126 Smith,Bob 46.2599 4 004689 1228 Jones,Darren 38.2145 Tracking Sheet A B C D E F G 1 ID Dept Name Hours Available Hours Worked Hours based % Salary based % 2 123456 0125 Smith,Bob 100 95 3 123456 0126 Smith,Bob 78 70 4 004689 1228 Jones,Darren 178 164 Group Total 356 329 Corporate Work Report Sheet A B C D E F G H 1 ID Dept Name Monthly Work Hours Holiday Time Off Available Hrs Hours Worked 2 123456 0125 Smith,Bob 108 8 8 92 88 3 006589 0125 Mack,Sarah 184 8 32 144 139.5 4 123456 0126 Smith,Bob 78 0 0 78 76.5 Group Total 370 16 40 314 304 I know this is alot of info, but I have used simple VLOOKUP formulas to pull in most of the data, but when I realized that someone could have different rates depending upon the department (or if there is a salary increase), the having the VLOOPUP reference one lookup value would not work. When I total for an entire group, I created a function with the VLOOKUP and for just 3 people, it was 2 Β½ lines long, so I I were to do this for 30 people or more, it would be so long that it seems that there would be an easier way. Here is the function: =((VLOOKUP(B6,'Salary Data'!$A$2:$E$90,5)*H6)+(VLOOKUP(B7,'Salary Data'!$A$2: $E$90,5)*H7)+(VLOOKUP(B8,'Salary Data'!$A$2:$E$90,5)*H8))/((VLOOKUP(B6, 'Salary Data'!$A$2:$E$90,5)*G6)+(VLOOKUP(B7,'Salary Data'!$A$2:$E$90,5)*G7)+ (VLOOKUP(B8,'Salary Data'!$A$2:$E$90,5)*G8)) If anyone can give me some tips, it sure would help. Steve |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Mike,
We have 2 types of % that we base our work on. The first is a 'salary based utilization' percentage and the second is an 'hours based utilization' percentage. When we calculate the hours based utilization, I just divide the hours worked by the hours available. The salary based utilization is calculated the same way. If you do these calculations for 1 person, the % are the same. When you add an entire column of hours worked and add the entire column of hours available, I use these totals and divide to get the utilization % for the entire set of employees. But if you do the same and it's salary based, then the utilization % is generally lower. (Our higher payed employees generally work less hours on specified projects (thus lowering their utilization) and more on business development (which does not count toward actual hours worked). I have been able to create the functions to pull an individual who works in different departments or with different rates. This is what I worked out with just 3 people (don't need for anyone to view another person's salary rate): =((VLOOKUP(B6,'Salary Data'!$A$2:$E$90,5)*H6)+(VLOOKUP(B7,'Salary Data'!$A$2: $E$90,5)*H7)+(VLOOKUP(B8,'Salary Data'!$A$2:$E$90,5)*H8))/((VLOOKUP(B6, 'Salary Data'!$A$2:$E$90,5)*G6)+(VLOOKUP(B7,'Salary Data'!$A$2:$E$90,5)*G7)+ (VLOOKUP(B8,'Salary Data'!$A$2:$E$90,5)*G8)) This is for our smallest group. There are 9 other rows that are filled in with 'new hire' for the name and their salary is all set to $0.00. I want the function to add up those that are in the group, but I get a #REF! error message in the cell. (I just added 9 more of one of the calculations to see if it would work). I guess the hardest part is that this is the smallest group. I have groups that contain 25 employees with room for 15 more new hires. Any ideas? Steve Now, I can't figure out how to come up with all the 'salary based' utilization totals for an entire group of people. Mike H. wrote: I have no idea what the salary base percentages are? I don't understand what is in the tracking sheet. Please advice. Im trying to determine the function(s) that would allow me to do several things. First I will give a simple example of what I have: [quoted text clipped - 79 lines] Steve -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200711/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Adding multiple values in one column based on multiple values of the same value (text) in another column | Excel Discussion (Misc queries) | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
Adding values for multiple worksheets | Excel Discussion (Misc queries) | |||
Adding Multiple low values | Excel Worksheet Functions |