Multiple calculations, multiple variables, and a percentage

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
821
Office Version
  1. 365
Platform
  1. Windows
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%$-
3LeadLineman$24.8244%$-
4Lineman$23.7744%$-
54thYearApp.$20.5844%$-
63rdYearAppr.$19.4644%$-
72ndYearAppr.$18.2944%$-
81stYearAppr.$17.1644%$-
Sheet1
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

MrActuary

Board Regular
Joined
Jun 18, 2004
Messages
73
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
Joined
Dec 18, 2002
Messages
821
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 18, 2002
Messages
821
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 18, 2004
Messages
73
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
Joined
Dec 18, 2002
Messages
821
Office Version
  1. 365
Platform
  1. Windows
Re: Multiple calculations, multiple variables, and a percent

That is perfect. Thanks again for your assistance.

\Matthew
 

Forum statistics

Threads
1,147,665
Messages
5,742,502
Members
423,734
Latest member
123hmMission

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top