Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Labor cost formulas

Using MS Excel 97.

Need help with writing a formula.

I have the following three (3) columns in a worksheet used to calculate labor-cost estimates for construction.

Emp Type

Hours

Amt

There are twenty (20) employee types (Emp Type). Each has a different hourly pay rate. Three (3) examples are shown. The employee types are designated using a coded combination of letters and numbers. This "code" is necessary for employee privacy purposes.

GF = General Forman @ 41.06 per hourly rate.
FM = Forman @ 39.51 per hourly rate.
LAB = Laborer @ 17.11 per hourly rate.

I wish to simply enter in the first column the >>Emp Type<<. Into the second column the >>Hours<<. The >>Amt<< colunm will calculate and display the result.

It seems to me that giving the >>Emp Type<< a "name" that refers to the hourly rate is the simplest result. How can I do this? I have tried the Insert, Name, Define menu options to no avail. I continue to get a #VALUE! error message.

Thank you,
BWray


Post your answer or comment

comments powered by Disqus
I am having trouble trying to calculate cost for a specific task. I know this is something simple and I am going to kick myself when it gets solved, but I have total brain lock right now! Here is the example of what I am trying to do.

A B C D E F
# of people start finish time man hours labor cost
3 1:35 2:05 :30 1.5 $15.00

I am entering the values in A, B and C, with B & C formatted as TIME. D is calculated by =(C3-B3), but I am lost trying to calculate E and F.

Any help would be greatly appreciated!

Hi guys,

I am working on a cost projection formula that will give me an exact cost per person based on a curve with 6 points marking the decrease in costs as employee population increases.

The formula has to yield a valid cost per person for any variable (from 1 to 120000 employees)

Now for the hard part - No VB can be involved in the formula...I can use the Analysis Toolpacks but thats it.

the base data is like this
Population points are
Column A Column B
5000 $32.00
10000 $24.50
25000 $9.00
50000 $7.50
100k $5.12

I need to be able to show what the cost should be for any number of empoyees based on this data. The yield should be linear, and can be rounded up.

The biggest problem was not using VB for it.. LOL...but thats the rule for my project.

Thanks for any help you may give,

Tony Hunt

Hello: I need to create a formula that will calculate retainage based on the percentage of work completed. In other words:

The project has a contracted labor cost (i.e., $100,000);
8% Retainage is held on first 50% of Labor Cost for the project;
0% Retainage is held on 51-100% of Labor Cost for the project.

Contractors will submit payment requests at various points in the project, and I need to accurately calculate the retainage withheld from each request.

This is probably a simple formula, but not for my rudimentary understanding of Excel! Your help is greatly appreciated. Thanks!

Hi Guys,

I was wondering if anyone could help me with a tricky lookup formula?? I posted it a while ago but it was a bit complicated to explain what I'm trying to acheive, so I've now done some of the hard work first.

Ok I have an Excel workbook with 2 worksheets, labelled as "codes" and "data"

The "codes" worksheet contains three columns of data.

Column A shows Country names
Column B shows Country dialling codes
Column C shows Cost per minute to the country

For example

Vatican City 39 0.28
Romania 40 0.22
Switzerland 41 0.06
Austria 43 0.06
United Kingdom 44 0.18
Denmark 45 0.06
Sweden 46 0.23
Norway 47 0.06
Morocco 212 0.34
Algeria 213 0.45
Tunisia 216 0.34
Jamaica 1876 0.34
St. Vincent 1784 0.55
Trinidad 1868 0.65

PLEASE NOTE: There are alot more than this, I am just using this as an example. Also, note that some area codes are 2-digits, some are 3-digits, and some are 4-digits.

Ok, I also have a worksheet called "data" and this worksheet is the worksheet which tells us The telephone numbers of the calls we've made. The telephone numbers are found in column D.

Column F contains the number of minutes the call lasted for.

So leading up to my question (finally!).....

We would like to calculate with a formula, the cost of each of the calls made. So essentially, the formula would have to do the following:

1) Lookup column D of the "data" worksheet to find out the telephone number.
2) Match against column B of the "codes" worksheet, to determine the country code and what country the call was made to.
3) Once we know what country we called, we can now check the adjacent cell in column C to tell us the cost per minute of the call.
4) Now we have the cost per minute of the call, we can then multiply this by the value in column D of "data" to get the cost of the call.
5) The final value then goes in adjacent column G of "data"

So hopefully we then have column G telling us the cost of the call!!

I hope this makes sense, but I will offer an example.

Lets say we are looking in cell D1 of "data" ..... the cell contains the following

D1= 61292224623

i.e. this is a number we called.

F1 = 127

i.e. we called this number for 127 minutes.

Ok so I now look at my column B of the "codes" worksheet to find out which country this was. I look down the column and I find it in cell B77

B77= 61 (Which is Australia)

So I now know that the call was to Australia.

Now I look in cell C77

C77=0.14

So I now know that cell C77 is the value of the cost per minute to Australia.

i.e. This call would have cost us 14p per minute.

I now multiply cell F1 of "data" worksheet by this value.

i.e. 127x0.14 = 17.78

So G1 of "data" would display £17.78 and this is the cost of the call!

Wow this was a long one!!

I hope one of you geniuses can help with this!

Thanks in advance!

I am entering production time and labor cost to get a running sum for the year. I would like this total to transfer to the end of each quarter, which is every 3 mths, but not have the quarters keep accumulating from the running sum. I have tried different formuls'a with no luck. Maybe it can't be done.
Example: running sum is column V31 and quarterly columns are A14 to D14. Is there a formula to transfer the sum of V31 to A14 for the 1st quarter and then not have A14 accumulate any more cost from V31 and so on for the rest of the quarters? I tried =Sum(V31)-(B14:D14) for the first quarter with no luck. Do I have to separate the running sum V31 in quarters? Please help!

I am working an employee schedule and I want to find the formula to make a
cell add the amount of hours used or assigned for a given day and give me the
results in hours. Such as I schedule someone to work 1100 (11am) - 1800 (6pm)
how can I get excel to see that there are 7 hours that were scheduled and
give me the results back as 7 hours or such. I then want to take that and add
all the cells with the time and tell me the number of hours that a individual
was scheduled to work for a given week to compute my labor cost

Hello.

Does anyone know how to copy a formula while maintaining a constant value?
Ex.

A1 = 1000 SF (my constant value)
B1 = Material cost (currently blank)
C1 = Labor cost (currently blank)
D1 = Total cost (currently blank)

on another sheet (Rates) I have the pricing for the above blank cells.
I would like to enter the formula for just one of these (materials for ex.) and copy the formula to the rest of the cells (labor and total) while maintaining a1 as the constant value.

ex.
Formula for b1 would be:
=A1*Rates!B1

when I copy this formula to the next to cells on the right i get the following

for C1:
=C1*Rates!C1

for D1:
=D1*Rates!D1.

as you can see the first variable is changing. i need it to keep A1, the total SF.

Hope any one can help.

Thanks,

I confused about creating formula using if function for the condition below.

Labor costs for items with material costs of at least $100 but less than
$500 are estimated at 50% of material costs.

I tried everything but did not work please help

(for example A1 contains a material cost) =if(A1=100<500,A1*0.50) isn't
that correct?

Does Labor day fall on the first Monday of September? If so how would you
write that formula? Thanks in advance!!!

I have a list of data that i need to pull some information from and summarize. The list has many rows of data, i have attached a portion of it (sheet 2). The summary sheet pulls different cost information from the list and sums it. There are 3 different costs i would need to sum, internal labor cost, parts cost, and external labor cost. Each unit has a worksheet that would show each total cost per month (sheet 1 in example). Any help with the formulas for this would be greatly appreciated. I am currently putting the list into a pivot table and manually copying the amounts for each unit into a sheet for each cost type that feeds the summary sheet(example is on sheet 3)

Thanks!!

I need to calculate the total hours worked during the week for one employee. Then take those hours and calculate the labor percentage against the sales. What formula do I use for the hours and for the labor?

How can I calculate/project payroll costs based on the employee weekly
schedule. I'm not sure what formula to use.

I would like to see my total labor for multiple employees in 15 minute increments.

What I would like to be able to do is to enter in my employees' hourly rate (once) and when my employees clock in/clock out (daily). From this I would like to know what my total labor cost is every 15 mins of each day.

Currently my spread sheet shows me what my sales are every 15 mins and I would like to know what my total labor for all employees is every 15 mins. From this I can chart labor and sales on the same graph.

Please help me! THANK YOU!

I'm simply trying to use a command button to copy and paste a value from a formula that is a locked cell on a protected worksheet.

Private Sub Retrieve_Click()

Sheets("Labor Costs").Select
Range("J25").Select
Selection.Copy
Range("I8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

The code stops at my first range selection.
Please help
Thanks

It's less of a question, more of a cry for help! I have set up a datasheet with information to be used in generating several pivot tables:

Column A - Our Invoice Number
Comunn B - Vendor's Invoice Date
Column C - Type of Vendor (Labor, PM, Subcontractor, Material, or Equipment)
Column D - Vendor Name
Column E - Vendor Invoice Number/Time Classification (Column C is Labor, PM, this is either Regular Time or Overtime. If Column C is Subcontractor, Material, or Equipment, the Vendor's Invoice Number is enetered.)
Column F - Labor Hours Worked (Column C is Labor or PM, a value is entered, if not, formula enters "N/A")
Column G - Labor Rate ((Column C is Labor or PM, vlookup value is entered, if not, formula enters "N/A")
Column H - Amount Billed ((Column C is Labor or PM, formula multiplies rate by hours, if not, enter the amount of the Vendor's invoice.)
Column I - Mark Up Percentage
Column J - Mark Up Amount
Column K - Total Amount Billed (Column H + Column J)

I need to set up a daily job sheet (like and invoice) for each date listed under Column B. For each day, I need to generate a daily job sheet showing all of that day's information. The location of the information is based on the value in Column C - Subcontractors's data go in one spot on the sheet and Labor costs go in another place.

Is this possible in Excel through formulas?

I am woefully inept at macros.

Any help would be greatly appreciated!

Thank you!
Marcy

Ok, I have a bit of an odd issue I need to solve. I use an Excel spreadsheet to track daily income for the store over a months' period. I have each day set to give me a rough estimate of profitability for that day. I also want to create a cell that tells me how much we've made so far, based on the number of business days so far in the current month. For example:

Day 1, $1500, $800 in expenses (monthly expenses averaged over the month, plus labor costs for the day), so a total of $700 profit.

Day 2, $1700, $749 in expenses (same as before), so a total of $951 profit.

The formula I'm using to calculate that isn't an issue, but I have the same formula for every single day, and each day has it's own cell with the estimated profitability for that particular day. In two other cells, I put the total number of business days in that month (in this case, 26), and in another cell I put the current number of business days we've had this month (in this case, 4), which is used partially to calculate the other cells. I want to use them to create another cell to contain the estimated profitability based solely on the number of business days out of the month we've had.

So E3 through E33 contain the individual days, I want to calculate - since we've only had 4 business days - what E3 through E7 would total. When I change it to 5 business days, i want it to calculate E3 through E8. So if we've had 4 business days, it only calculates the totals from the first 4 cells, if 5 business days, then the first 5 cells, etc, etc.

I can sort of picture it in my head, but I can't seem to find the necessary equation for it. It may require a macro, but I'd rather use a formula if possible. I'm not a big fan of macros.

To be more specific. I have a simple quantity times price equals cost formula
that i would like on my worsheet so the user can calculate cost to put into
the chart easier. However I don't want that formula or the titles above the
formula to be printed. I relize you can put it out of the print area and just
print page 1, but i would like to make it more simpler than that and just be
able to press print and it not show up.

Thanks,
Ron

To be more specific. I have a simple quantity times price equals cost formula
that i would like on my worsheet so the user can calculate cost to put into
the chart easier. However I don't want that formula or the titles above the
formula to be printed. I relize you can put it out of the print area and just
print page 1, but i would like to make it more simpler than that and just be
able to press print and it not show up.

Thanks,
Ron

My worksheet has the following:

Column J which is a number if items and data entered in these cells

Column AA which is a total cost formula
=IF(SUM(P30:R30,T30:V30,X30,Z30)=0,"",SUM(P30:R30,T30:V30,X30,Z30))

Column AB which is average cost per item formula
=IF(J30="","",(J30*AC$2))

Cell AC$2 formula
=IF(J2="","",ROUND(AA201/J201,2))

Cells AA201 and J201 are sums for those columns formula
=SUM(J2:J200) and =SUM(AA2:AA200)

Cell AC$2 remains blank althought AA201 and J 201 have value (numbers) in them.

All cells in columb AB have the #VALUE! in them

Where have I gone wrong.

Thanks.

Hello All!

Trying to develop a project cost analysis, and I'm needing a more automated way to populate the labor rate of technicians. I've experimented with the if(), but don't believe it can differentiate between more than two possibilities.

Lets say I have three techs, PH, MD, and JC. I have a sheet in the workbook that has their respective pay rates. On another sheet, I enter their initials and hours worked. What I'd like to accomplish is, by typing their initials, the cell in which their labor costs are determined knows from which cell in the rate sheet to pull their actual rate, hence returning their cost.

I have a little experience in VBA, and am presuming it's the only way to go in my case. Am I wrong? Can anyone shed some light?

Thanks!
Cole

Hi all ,

Now if i have 2 worksheets ( see the attachment please ).

What i want to do is :

Day 1

Daily Cost ( value)
Accumilative Cost = Daily Cost

Day 2

Daily Cost ( value )
Accumilative Cost = Daily Cost of day 2 + Accumilative Cost of day 1

Day 3

Daily Cost ( value )
Accumilative Cost = Daily Cost of day 3 + Accumilative Cost of day 2

Now by looking at the excel and those images youll get a clear idea of what im trying to do and wheres the problem :

Im on

UZ 112 DMR (2)

Worksheet

I right click on the worksheet and select " Move Or Copy "

I select " move to the end " "& Create Copy

A new Worksheet is created under the name of UZ 112 DMR (3)

Now when looking the accumilative cost formula its :

=AK31+'UZ 112 DMR (1)'!P31:Z31

Instead of (1) it should be (2)

Is there any workaround to make this work automatically ??

For instance if im on

UZ 112 DMR (7) My Accumilative Cost Formula should read

=AK31+'UZ 112 DMR (6)'!P31:Z31

*************************************

Thanx in advance

In a tool of ours, we have SUMIF monthly formulas within other formulas like the following:
+C8)
M2: =ConVolNA_Val*Volumes!D9*(SUMIF(UsVendVuc,$BM8,'Vendor Unit Costs'!I$319:'Vendor Unit Costs'!I$618)+D8)
.
.
.
M60: =ConVolNA_Val*Volumes!BJ9*(SUMIF(UsVendVuc,$BM8,'Vendor Unit Costs'!BO$319:'Vendor Unit Costs'!BO$618)+BJ8)
On another sheet with formulas, we are setting up some formulas and letting the user select what month's costs to use. So, for example, if they say they want to use month 2, then the formula for the costs should reference column I on the Vendor Unit Costs sheet (i.e.we would need to use SUMIF(UsVendVuc,$BM8,'Vendor Unit Costs'!I$319:'Vendor Unit Costs'!I$618)) . Therefore, I'd like to have a formula that will automatically set the formula to use the correct column (H thru BO), depending on the month number they enter. Does anyone know how this can be done? We don't want to have to create 60 more columns of just the monthly cost formulas because this would add alot to the tool since it would have to be added for every geo, cost type, etc.

Hi everyone,

I am trying to come up with a formula that will tell me the increase or decrease between two numbers.

Example:

Baseline Labor Cost = 1,000
Month 1 Labor Cost = 1,500
Month 2 Labor Cost = 500

Month 1 Variance = +500
Month 2 Variance = -500

The results of the formula should show the variance for each month.

I know this is probably simple, but I have gotten all wrapped around the axle and can't make it work properly!!!

Hi -

I have been unsuccessful in finding an answer that works for my situation:

I have a table that shows Mon-Sun on the top / names on the left
Each day, I enter the number of hours and minutes worked. The column to the right adds all the hours. I need the final number to read as a decimal.

Below, you can see my table. As of now, I take each day and convert each day by looking at my little decimal cheat sheet. It takes forever with 40 employees and 7 days!

Any way I can enter 4 hours and 47 minutes (as 4.47) and it will convert to 4.78? Then the last column will show the proper hours and minutes as a decimal?

(I have an attachment of the entire worksheet since the paste table looks terrible. Please refer to the labor tab at the bottom)

Thank you so much for any help you can give. I am a beginner - able to use basic formulas so really need step by step help.

Peace. Michele

WEEK: __________________________________________ 4/25/2011 - 5/1/2011
PRODUCT LINE REG OT MON TUE WED THU FRI SAT SUN TOTAL REG OT
RATE RATE 4/25 4/26 4/27 4/28 4/29 4/30 5/1HRS PAY PAY
O'Neil 10.00 15.00 4.37 2.56 6.50 4.25 7.58 0.00 0.00 25.26 253 0
Janet 11.00 16.50 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0
Bobby 15.00 22.50 9.00 8.50 8.27 7.49 0.00 0.00 10.32 43.58 600 81
Robert 13.75 20.63 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0
Vicente 12.75 19.13 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0
Joan G 12.00 18.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0
Ricky 13.00 19.50 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0
Jenn B 11.00 16.50 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0
Tammy 11.00 16.50 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0

TOTAL LABOR COST 13.37 11.06 14.77 11.74 7.58 0.00 10.32 68.84 853 81
TOTAL DAILY SALES 0 0 0 0 0 0 0 9,056


No luck finding an answer? You could always try Google.