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

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

- Calculating Manhours/labor cost in excel 2003
- Projected Cost Formula not working.
- If And Then Formula
- ISDN call cost formula
- Quarterly totals formula in Excel 97
- How to write the formula for time accounting for a work schedule
- Copy formula which maintaining a constant value.
- Creating formula using if function
- Labor day formula
- Summarizing cost data from list by unit #
- Calculating labor costs using an employee schedule
- Payroll labor costs
- Finding labor total for multimple employees in 15 min. increments.
- What am I doing wrong!
- Excel Database Invoice Question
- Calculating cells used based on another cells data
- Is there a way that I can exclude a formula from being printed?
- Is there a way that I can exclude a formula from being printed?
- Need help with a series of formulas
- Trying to populate labor rate based on initials
- How to add Accumilative + daily cost value from the previous worksheet automatically
- Formula question-SUMIF monthly
- Increase/Decrease in 2 Numbers
- Hours & minutes to decimal

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!

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

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!

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!

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!

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

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,

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?

write that formula? Thanks in advance!!!

Thanks!!

schedule. I'm not sure what formula to use.

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!

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

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

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.

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

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

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.

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

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

+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.

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!!!

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.