Multiple calculations, multiple variables, and a percentage

[spectraflame]

I am trying to create an easy to use sheet that will assist my supervisors in calculation labor for specific positions.

The following sheet displays the layout that I am trying to use. My problems are the following:

1. I need to be able total the labor for each position based on the number of employees that worked at that position, the type or types of hours that each position accumulated, figure in travel time, and create the formula so that the fringe percentage can be changed if necessary without changing the formula.

The columns are broken down so that a single employee or multiple employees can work regular hours, overtime hours (1.5 times the regular pay rate), and double time (2 times the regular pay rate). Also if any one or all of the columns are populated, that amount needs to be multiplied by the fringe percentage and added to the total. This is where I am getting lost.

2. Travel time is only used for specific employees but for each job classification. The maximum pay that any employee receives is 1 hour of normal pay. Basically the only number that will be in this column if necessary is "1". I just need a way to increase the total amount by the regular pay rate when needed.

The formula that I have created in column "I" currently does not take into count the travel time column because I am not sure how to do this. In addition to that, I do not know how to calculate a value by the actual percentage in column "H". The users want the actual value to be displayed at 44% instead of 1.44 which is what I am doing in my formula. The only problem is that when the percentage is changed, I would have to change the formula each time.

Any suggestions would be greatly appreciated.

Matthew

Book1
	A	B	C	D	E	F	G	H	I
1	OCCUPATION	HOURLYRATE	NUMBEROFWORKERS	REGULARHOURS	OVERTIMEHOURS	DOUBLETIMEHOURS	TRAVELTIME	FRINGEBENEFIT	TOTAL
2	Foreman	$24.72						44%	$-
3	LeadLineman	$24.82						44%	$-
4	Lineman	$23.77						44%	$-
5	4thYearApp.	$20.58						44%	$-
6	3rdYearAppr.	$19.46						44%	$-
7	2ndYearAppr.	$18.29						44%	$-
8	1stYearAppr.	$17.16						44%	$-
Sheet1

 

[MrActuary]

Re: Multiple calculations, multiple variables, and a percent

Hmm not entirely sure I understand everything but here's how I see the formula in column I (which should solve your problems)

= B2*C2*(D2 + 1.5*E2 + 2*F2 + G2)*(1+H2)

Is travel time on an individual employee basis or occupation basis? If the only thing in column G is a 0 or 1, then my formula should work, assuming you want to give the +1 hour to ALL employees in a given occupation.

 

[spectraflame]

Re: Multiple calculations, multiple variables, and a percent

Your statement about the travel time is correct in that the only value in that column would be a 1. If there was not travel time necessary for this position, then the cell would be left blank. So if 3 employees requirement payment for travel time, I would have 3 in column C and the travel time column would have a 1 in it. This would mean that the total dollar amount for that particular position would have 1 hour of the regular pay rate added to it. Basically if there is 3 people, only one of those people are getting paid travel time. Does this make sense?

Matthew

 

[spectraflame]

Re: Multiple calculations, multiple variables, and a percent

With using the formula suggested by MrActuary and my little addition to take care of the travel time issue, is there a way that this formula can be condensed?

=IF(ISBLANK(G18),B18*C18*(D18+1.5*E18+2*F18)*(1+H18),B18*C18*(D18+1.5*E18+2*F18)*(1+H18)+B18)

This is the only way that I could think of doing it.

Matthew

 

[MrActuary]

Re: Multiple calculations, multiple variables, and a percent

Ok, so regardless of how many employees there are, travel time just gives 1 hour of regular pay?

simply the formula as:

B2*(C2*(D2 + 1.5*E2 + 2*F2)+ IF(ISBLANK(G2),0,G2) )*(1+H2)

That's assuming you want travel time multipled by the fringe benefit.

If not then use,

B2*(C2*(D2 + 1.5*E2 + 2*F2)*(1+H2) + IF(ISBLANK(G2),0,G2) )

 

[spectraflame]

Re: Multiple calculations, multiple variables, and a percent

That is perfect. Thanks again for your assistance.

\Matthew