# Multiple calculations, multiple variables, and a percentage

#### spectraflame

##### Well-known Member
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
ABCDEFGHI
1OCCUPATIONHOURLYRATENUMBEROFWORKERSREGULARHOURSOVERTIMEHOURSDOUBLETIMEHOURSTRAVELTIMEFRINGEBENEFITTOTAL
2Foreman\$24.7244%\$-
4Lineman\$23.7744%\$-
54thYearApp.\$20.5844%\$-
63rdYearAppr.\$19.4644%\$-
72ndYearAppr.\$18.2944%\$-
81stYearAppr.\$17.1644%\$-
Sheet1

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### MrActuary

##### Board Regular
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

##### Well-known Member
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

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
Re: Multiple calculations, multiple variables, and a percent

That is perfect. Thanks again for your assistance.

\Matthew

Replies
11
Views
247
Replies
1
Views
184
Replies
1
Views
232
Replies
4
Views
190
Replies
2
Views
255

1,181,614
Messages
5,930,929
Members
436,767
Latest member
Langaws

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back