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

Free Microsoft Excel 2013 Quick Reference

Labor Rate %

Time Sheet Reads as followed:
A B C D E
4 Mark 8.00 4
5 Joe 12.00 0
6 Jim 5.00 5
7 Jay 6.00 0
8 Julie 7.00 5
9 Zoe 6.00 0
10 Al 5.00 6
11 Ken 5.00 0

A= Employee's B= Hourly Rate C,D,E,etc. = Weekday hours
How do I create a formula to figure The Labor Rate Daily for column C?
I have 8 employees total, but only 4 employees worked that day. I need a
formula to add the total labor rate for Column C for the 4 employees.
The answer will be $25.00

Thank you,
Myrna


Post your answer or comment

comments powered by Disqus
need invoice for service co. Two labor rates, mileage rate, parts pricing,
inventory tracking, several tax rates, and related stuff !!!!!!!!!!!1

Thanks for your help.

PS Am new to MS programs.excel, word etc.

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

All,

I have two worksheets that I use to track who is charging to an account. One worksheet is imported from another system and then currently I have to scan the imported information to see if I am missing anyone that isn't listed in the "Labor Rates" worksheet.

So I am looking for a way to run a macro or something else that will compare the "labor data" worksheet and the "labor rates" worksheet and upon finding an input in "Labor data" that isn't in the "Labor Rates" I would either like to be notified of those missing or something else so that I know which ones to add. I have attached an example.

The easiest way to do this I think would be to either check by employee name or employee ID. In the example provided ID "66666" is missing from "Labor Rates".

Thanks!

I have built a macro that tests to see if a date in a range is before the Period Start Date, and if it is, then the labor rate in that same row (3 Columns Over) should be escalated by the appropriate AWI. My code below works, but I am wondering if there is a better way than using offset(0,3) to prevent a code breakdown if columns are inserted between the Date and Rate columns. The date range is names "DATES" and the rate column in names "RATES" Thanks!!

My code is below:


	VB:
	
 CountTheCells() 
    Dim cell As Excel.Range 
    Dim i As Integer 
    For Each cell In Sheet2.Range("DATES") 
        If cell.Value < Sheet2.Range("POPS") Then 
            i = i + 1 
        End If 
    Next 
    MsgBox ("You have " & i & " Rates that will be escalated b4 the POP Begins") 
    yesno = MsgBox("Would you like to Escalate the Dates and Rates?", vbYesNo) 
    If yesno = vbNo Then 
        MsgBox ("Not Done") 
    Else 
        For Each cell In Sheet2.Range("DATES") 
            If cell.Value < Sheet2.Range("POPS") Then 
                cell.Offset(0, 3).Value = cell.Offset(0, 3).Value * (1 + Sheet2.Range("AWI")) 
            End If 
        Next cell 
        For Each cell In Sheet2.Range("Dates") 
            If cell.Value < Sheet2.Range("POPS") Then 
                cell.Value = DateAdd("yyyy", 1, cell.Value) 
            End If 
        Next cell 
        MsgBox ("Done") 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello all,

I'm stuck on getting something to work correctly. I have a service company and we use different hourly rates for different types of clients. Previously, I was using three different spreadsheets, one for each rate schedule, but this seems silly.

I want to have one spreadsheet that has a cell with a drop down box to select the rate schedule, say 1, 2, or 3. This would set the correct labor rates for the entire worksheet. This then needs to trigger a VLOOKUP in the correct column for different labor types.

Data is:

Regular Time - $ 60 for schedule 1, $ 50 for schedule 2, $ 40 for schedule 3
Service Time - $ 70, $ 60, $50
Travel Time - $ 30, $ 25, $ 20

So, If I select schedule 2 in the drop down box, I want to have the following hourly rate results auto populate next to their types:

Regular $ 50
Service $ 60
Travel $ 25

Would someone kindly care to help me out? I'm probably looking over something very simple, but after staring at it for a couple of hours, I'm no longer thinking clearly!

Many thanks!

This one has stumped me folks can any one help make this a little cleaner. i have a work sheet (work sheet 1) that i needs to take ever third line in the column and divide it by numbers on another work sheet (work sheet 2). The numbers on the work sheet 2 (FY-08 Labor Rate Report'!$K$36,FY-08 Labor Rate Report'!$K$37 and so on)are the hourly rate and the numbers on sheet 1 (F141,F144,F147 and so on) are the monthly cost. Once i get the division i multiply those numbers by a dollar factor ('FY-08 Labor Rate Report'!$C$5. I have 15 Rows that have a column for each month so there has to be a better way than what i have come up with

=(F141/'FY-08 Labor Rate Report'!$K$36+F144/'FY-08 Labor Rate Report'!$K$37+F147/'FY-08 Labor Rate Report'!$K$38+F150/'FY-08 Labor Rate Report'!$K$39)*'FY-08 Labor Rate Report'!$C$5

The actually formula will have an additional 12 more columns if i do it my way which is way to long.

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

I've been beating my head against the wall for a whole day on this, and I can not get around the circular references that are being generated.

I get a lump sum total cost associated with the labor. And I know how many hours were part of this sum. How can I figure out what the average wage was in regular hours and overtime hours and break the cost down into it's respective catagories.

[Img]******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: xl2000 : OS = Windows XP File Edit View Insert Options Tools Data Window Help AboutG19=
ABCDEFG1*******2**Hours*3**WorkTravel*4**RegularO/TRegularO/T*5**2456123342*6*******7*******8**Cost*9**WorkTravel*10**RegularO/TRegularO/T*11*Total*****12*******13*******14**Hourly*Rates*15**RegularO/T*16*****17*******18*Total$84,659.26****19*******Sheet1*
[HtmlMaker light Ver1.11] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.[/Img]

C18 is the total given to me.
C5,D5,E5,F5 are my known hours

I need to some how figure out C11,D11,E11,F11 which is the the total broke out into each catagory
And C16,E16 are the labor rates that i need to figure out.

Who here is the super accountant, this might be something they know how to do.

Really do appreciate any help.

I have a project cost analysis spreadsheet (which, thanks to someone on this forum, is actually quite wonderful now) that is returning an expected error when I clear out the values we don't have yet. I've read about ISERROR(), and it seems like that should do the trick, but it kicks back with "too many arguments".

This means my end goal of summing all the values we do have won't work without clearing the rows that are waiting for new information. Wondering how to proceed here.

The formula I have now is as follows:
=(VLOOKUP(B3,'Labor Rates'!$A$3:$B$10,2,FALSE)*(IF(D3>8,8,D3))+((VLOOKUP(B3,'Labor Rates'!$A$3:$B$10,2,FALSE)*1.5)*(IF(D3>8,D3-8,0))))

What I tried to do include was:
=IF(ISERROR(....the formula above, pretty much twice)

Any suggestions? Am I way off base?

Hi, I'm new to the board, I humbly ask for your acceptance... I work as a
cost estimator and have developed a very, very nice program to calculate the
selling price of our products. It incorporates Escalation, all direct labor
rates and overhead rates, is capable of time shifting to forward dates and
mid-points and so on. I have been "asked" by some of the people I answer to
for copies of this program, but I hesitate. If not all the adjustments are
kept up to date, the information can be eronious - and we all know "who will
be to blame" for eronious information. Here is what I want to do. I want to
have a macro that will delete all the pages then save the new file over top
of the old file. And if they have another copy - again, upon opening it, and
the statement seeing that the date has been passed - again activate the
macro. - forcing them to come back to me for another - which would be the
latest and greatest, right?
any thoughts? - Cheers to you all - and thanks for being here.
--
HW42

Good Morning. Our company uses a worksheet to develop project quotes. This work sheet has been around awhile and works fine. The president of our company wants to modify the sheet to lock in a set labor rate - not modified by mark ups. By doing this, one of the original formulas does not work as it had in the past. I've worked on this and have been unable to come up with a formula solution. Here's the issue: If you look at cell E127, you'll see that a mark up value may be entered. This drives the calculations that result in the total in cell G129. Now, you can enter a desired selling price in cell E131. This drives the calculations in cell G131 that should show the resulting actual mark up. Due to the set labor rate, this calculation is now skewed. Cell G131 should show a calculation result in this example of 1.75. Any help in modifying this calculation to reflect the correct final mark up while using the set labor rate would be very much appreciated!
Estimating Worksheet Feb 1 2012.xlsm
Thanks,
Steve

We have a customer at work who wants us to start billing in a 500 character edi format.

What that means is basically we need to take our bill and create a 500 character long string from each line.

Most of the characters in this "string" do not change however the billing code, description, labor rate, material charge, etc will alternate.

What I need to do is use our existing spreadsheet because the format is used for our billing system and have another spreadsheet that pulls in the work code, description, costs, etc and throw it in place with the 500 character line, merge it and kick it out in ascii.

Here is a small "dummy" example with the fields that I would need to pull in highlighted in red:

1GATX12050562478640800905EINTERIORRUBBERLINING0000000000000000000047290018500S000950RR0000000000000

I hope I explained it well enough. Is this even possible? Thanks in advance for your input.

I have a sheet for all my job costing SHEET 1.
I have a sheet with my employees and wages LABOR RATES.

I have set up the formula LOOKUP(B8,'Labor Rates'!A$3:D19) in sheet 1 so
once I choose the employee from the dropdown box their wage matches.

But my problem is when I add a new employee into my LABOR RATES and their
wage, their name is added to my dropdown list in SHEET 1 and when I choose
the new one the wage is incorrect?

What am I missing?

thanks tb

I have a worksheet in which the user enter's a lowercase "x" in the L column
if they wish to calculate the Labor cost and/or a lowercase "x" in the M
column if they wish to calculate the Material cost and/or a lowercase "x" in
the E column if they wish to calculate the Equipment cost and/or a lowercase
"x" in the O column if they wish to calculate the Other cost. In most cases,
the end user will be calculating costs for more than just Labor or Material.
In some cases, the end user will be calculating the cost of all four
classifications; Labor, Material, Equipment and Other. Once the lowercase "x"
is entered into the appropriate cell, Excel will make a calculation by
multiplying an inputted quantity x cost per quantity unit for materials and
equipment. If labor is to be calculated it would be Quantity x Labor Hours
per Quantity x Labor Rate in Dollars. If I cannot find a function or custom
function to perform this function, I have to added a sum column for each of
the four types of costs per task line item. With the lowercase "X" feature, I
only need one sum column. Any help would be appreciated. Thank You, JAD

Hello,

I'm trying to run a macro everytime any value in a cell range changes (the cells are linked to another worksheet). Currently, it's only changing when I change the actual cell values, not when it changes from the linked cell. The code I'm using is:

Private Sub CommandButton1_Click()

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("P4:Y56")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
    Is Nothing Then

' LaborRateSort Macro
'
    Range("P4:Y56").Select
    ActiveWindow.SmallScroll Down:=-36
    Selection.Copy
    Range("AA4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AA5:AJ56").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Labor Rates").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Labor Rates").Sort.SortFields.Add Key:=Range( _
        "AJ6:AJ56"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Labor Rates").Sort
        .SetRange Range("AA5:AJ56")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    MsgBox "cell " & Target.Address & " has changed."
    
    End If
End Sub
Any help is much appreciated!!!! Thanks.

Morning Folks, I have a 10-sheet services pricing calculator that is being used here in North America as well as internationally. On every sheet (tab) there's are all kinds of labor rates and software costs and it would be great for my international clients if I could give them the option of selecting the appropriate currency character on the front page. On the cover (first) page I created a drop-box where our clients can choose either the 'Dollar, UK Pound or Euro' currency character.

Here's my question, by choosing the desired character on the fist sheet, is there a formula I could use that would make the chosen character the default currency character for the whole workbook?

Meaning, if one of our UK partners clicks on the drop-box and chooses the British Pound character, the whole tool is now defaulted to the British Pound?

I've been beating my head against the wall for a while on this one and any help would be greatly appreciated.

Mike

Hi all,

I am creating a quote sheet for my estimators that will allow them to estimate production cost for 7 different facilities using labor rates specific to the facility and work center selected.

The estimator will first select a facility from the drop down in H8 on the "Quote Sheet"("Facility" list is located in the "List" tab). this will direct VLOOKUP to the proper labor rate list for the facility selected. (Labor rate lists are located in the "Labor Rate" tab.

Next, select a work center from the drop down box in B26. This will direct VlOOKUP to the work center and associated labor rate. The labor rate will be displayed in G26. Repeat until all operations for manufacturing are complete.

I can make this work with a 1 facility VLOOKUP formula =IF(H8="West",VLOOKUP(B26, West, 2, FALSE))
But I do not know how to nest a formula to cover all of the facilities. I am a Excel rookie, so I am not sure if I am using the correct logic to extract this information.

Any help would be greatly appreciated.

Copy of Quote Template.xlsx

I have created a Excel Database for estimating, my DB includes, item description, item code, unit price , labor rate etc.

I have created an estimating sheet and i would like to reference items in the database. i.e I have data validation on the estimating page which enables me to pick items from the DB on a drop down menu. What i would like to do is display field information of the item selected and automatically fill the estimating sheet with unit price, laboor rates etc.

i.e. I go to my Estimating sheet (which is blank) drop down menu select 20Amp receptacles, then automatically the data for Unit_Price, Labor_rates etc is filled with info from the corresponding cell in my DB.

thanks....

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!

Need a excel template with formulas for calculating direct and indirect job
costs. Specificall for labor and insurance. Like calculating payroll tax,
and workers comp to be added on to hourly rate or based on total payroll for
the job. Also formulas for calculating indirect costs like take monthly
totals for fuel and divide by 30, vehicle insurance daily rate. Or anything
like this that would help.

Hi, I will rely on the generosity of you all to help me with this. I need to calculate tax on an invoice but exclude labor and trip charge. So, if F38 and F39 are labor and trip charges, and I've entered the tax rate, how do I tell the total field to ignore labor and trip charges when calculating the total? I keep getting circular messages. With my tail between my legs, I ask for your guidance.

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

I'm trying to figure out how to get excel to determine rate of pay based on time.

i.e. if it is between 0800-1700 then the rate of pay is $30. If it is 1700-0800 it is $45.

The example I'm working with right now, is: Doing a job starting at 0600 and going to 1000. The first two hours would be at $45 the last two would be at $30. My total should be $150.

Up till now I've had to separate out 2 hours at $30, 2 hours at $45. If I could just get it to do it automatically I would be able to cut down on the columns needed and would simplify my time sheet.

Hi, I have a spreadhseet which has list of people who have worked for several weeks (invoice start date & invoice end date) in sheet 2 and for the same list of people their rates and margin and on costs information are available in sheet 1 but for a specified date range, i need to vlookup in such a way that the result i get in sheet 2 should give me the corresponding rates, on costs and margin information for that week which falls with in the date range in sheet 1. i have attached the spread sheet
thanks
sampath


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