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