Landscaping Company / Matching Units / Volume And Labor Hours To Hourly Labor Rate
Jun 26, 2014
For a landscaping company, who is looking to service gardens more effectively/efficiently, they are mirroring the below variables to the amount the client is willing to pay per hour. I also have a sample file.
I have, on Sheet1, 8 columns I'm working with:
R3:R132 is the currently hourly rate
L3:L132, M3:M132, N3:N132 are current units, volume, and labor hours respectively by location.
AD3:AD132, AE3:AE132, AF3:AF132 are new units, volume, and labor hours respectively by location.
AI3:AI132 is the suggested hourly rate by location. Note this is already calculated and filled in. I need to calculate new units, volume, and labor hours based on the suggested hourly rate.
On Sheet2, I have 4 columns I'm working with:
A2:A161, B2:B161, C2:C161, B2:B161 are hourly rates, units, volume, and labor hours respectively.
They are laid out in combination with one another to show possible combinations of these variables that make sense for the landscaping company.
My goal is to figure out what combination the company wants to go with for a given hourly rate on Sheet1 given the following logic to follow:
1) Attempt to reduce labor hours to get to suggested hourly rate - This is to calculate new labor hours on Sheet1 in cells AF3:AF132.
View 1 Replies
ADVERTISEMENT
Mar 6, 2014
It is data for a time study to compute labor hours. I have managed to group everything together per "work order" and "Employee ID". I am trying to sum up the labor hours by work order. I know it sounds simple but I do have another question. Once the total hours are set. Are they still available in the same format when put in a Pivot Table?
View 2 Replies
View Related
Aug 12, 2009
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.
View 2 Replies
View Related
Mar 24, 2008
How can I get the median of a column that contains both hourly and annual rates? I would like to either multiply the hourly rate * 2080 or divide the annual rate by 2080 then get the median.
View 6 Replies
View Related
Apr 18, 2006
I am trying to calculate earnings based upon hours worked based on 24 hours format (time out - time in = time worked) multiplied by hourly rate. I cannot get the proper calculation. How can I attach a copy of the Excel worksheet?
View 9 Replies
View Related
Nov 23, 2009
I have 3 sets of data for two different groups:
Group 1 - Inbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)
Group 2 - Outbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)
I need to calculate the weighted average of the win rate based on volume of calls. Is there any way to do that?
View 6 Replies
View Related
May 15, 2014
I am trying to match up company codes across three different columns. I am inserting rows to get them to match up. However, there has to be an easier way.
1017
1017
1027 Total
1051
1021
[Code] ...
View 8 Replies
View Related
Jan 8, 2012
I'm trying to match stock portfolios of mutual funds with stock indexes to find out how much the portfolios deviates from the indexes (active share).
The deviation is simply calculated as the percentage of the portfolio holdings not included in the index.
The data I have is 10 years of monthly portfolio weightings for 30 mutual funds and 8 indexes, organized with company names in column A and the percentage weight for each company in each month in the adjacent rows (see simplified screenshot). I want to match all companies with all indexes.
Twofold:
1)The mutual fund data comes from one source and index data from another. This means company names are not spelled exactly the same.
2)Within the mutual fund datasets, a company might be listed more than once with different names. The different names refer to different periods in time, so what should be matched with the index is the sum of these columns.
The specific task that I need to accomplish is to match company name in the portfolio with the index and then return the absolute difference between weight in the portfolio and the weight in the index for each month. (see screenshot).
Hence the result I'm looking for is a matrix of company names (of each portfolio) in col A and the absolute difference between the portfolio and the index weight in the adjacent row.
The best solution I've come up with is to device a list of all the portfolio company names, including "doubles", and then for each index, add these company names and copy the row weightings. This I'm not particularly happy with because index weightings would no longer sum to 100 and it would be difficult to check for errors. Also, it would still take a lot of time.
View 6 Replies
View Related
Aug 20, 2014
I need to expand on this formula.
I require a formula that will break down daily hours worked into rate categories eg Normal Time, Time & Half, Double Time.
eg. Column E = Total time worked
Column F = Normal time
Column G = Time & Half
Column H = Double Time
What I would like to do is enter hours into Column A and a formula in Column B will split of hours to a maximum amount of 7.6hrs then the remainder of the hours be placed in Column C to a maximum of 2 hours and Column D, no maximum.
These are the formula I am currently using
column B
=MIN(E2*1,7.6)
column C
=MAX(MIN(E2-7.6,2),0)
column D
=MAX(E2-9.6,0)
I would now like to be able to split the hours over 3 rows x 3 columns
Example 1
Row A B C D
1 3 3 0 0
2 5 4.6 .4 0
3 2 0 1.6 .4
Example 2
Row A B C D
1 6 6 0 0
2 21.6 0.4 0
3 0 0 0
The total hours in Column B cannot exceed 7.6 hrs
The total hours in Column C cannot exceed 2 hrs
There is not maximum for hours in Column D
View 2 Replies
View Related
Jun 1, 2006
I have a nested if formula that isn't working and I know there is a real simple fix.
Here is the situation: If Company A (A1) and Company B (B1) both have indices greater than 125, I want that to show that in my recommendation. For example: I used if(A1>125&B1>125,"Company A and Company B",if(A1>125,"Company A", if(B1>125,"Company B","None"))). For whatever reason, the first nested if statement isn't working correctly. what I am doing wrong with this if/and statement?
View 3 Replies
View Related
Apr 5, 2007
I have an excel sheet which displays the data in the following format:
(See image attachment). This is just an example of the way the master list is formatted. The master list has over 18,000 lines. Basically, there's a project name, resource name, and number of hours worked for the project within that work week. I've been trying to figure out a way to create a formula or macro to search all these names and add up the hours of the duplicates.
I need excel to search this list of names and total up all hours for each matching name and place each total value on another sheet. For example, Ken Craver has 8 occurances within the week of 1/21/2007 (from project 1 and 25) and his total hours = 31. So the value 31 should be calculated because all the hours associated with Ken Craver add up to 31. Then excel should scan another sheet which has a list of resource names and once it has found the matching name on the other sheet, it will place the value 31 in the column next to the name.
View 4 Replies
View Related
Jun 4, 2007
The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.
r = 100((Y/X)^(1/n))-1)
So for X = 200, Y = 750, n = 10, we have
r = 100((750/200)^(0.1))-1) = 14.1309%
View 3 Replies
View Related
Feb 13, 2014
From a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. How to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish.
View 6 Replies
View Related
Jun 27, 2014
As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.
Nights, holidays and holiday night have different price/hour.
The excel is working fine…but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctor’s shift the day hours tha night hours, the holiday hours and the holiday nights hours.
In the excel I have fill the hours Manuscript, I need a formula to do that for me…
On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!
Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)
View 1 Replies
View Related
Feb 7, 2008
I have a list of companies, each company has it is own name and particular code.
I specify Cell D12 For Company Code and Cell E12 for Company name.
How to write code that write company name automaticlly in cell E12 when I write the company code in cell D12?
View 9 Replies
View Related
Dec 11, 2008
I would like to integrate a button on my spreadsheet that looks and works much like a round volume button on a stero that you can turn in order increase or decrease volume.
However, I would like the button to control the values of a cell. If the volume is turned down, it should display a 1. If the volume is turned up, it should display a 16545. In between you should be able to - more or less - tune in on any integer in between.
View 9 Replies
View Related
Jan 23, 2009
a formula that would interpolate the volume in the below worksheet....
View 9 Replies
View Related
Jan 13, 2007
iam trying to use vba to work out the volume of a cylinder.
If the cylinder's dimenstion are:
Diameter = 200mm
Height = 800mm
the Volume should = 0.00017 cubic meters.
Iam getting 0.502654824574367 as the answer using this code.
Private Sub CommandButton1_Click()
'http://www.readymix.com.au/toolbox/calculators/calcConcreteRoundColumn.asp#
Dim RadiusSqared As Double
RadiusSqared = (txtHoleDiameter / 2) * 2
txtCubicMeters.Value = 3.14159265358979 * RadiusSqared * txtHoleDepth.Value
End Sub
View 11 Replies
View Related
Sep 17, 2010
I am trying to figure out if something here is even possible. I have an order form and a price list. I get different pricing based on the quantity I purchase. I would like to have the price auto populate based on the quantity entered. The price will be on another sheet.
I couldn't find a lot of info but I also may not know where to look.
example:
if (b2<62) then price is ($sheet2.b2)
if (b2<124) then price is ($sheet2.c2)
if (b2<372) then price is ($sheet2.d2)
Is this even possible to put into a formula?
View 6 Replies
View Related
Feb 27, 2008
Is there any way to add percents to a stacked bar graph that also displays volume? Right now in order to show percents in a stacked bar graph I have to manually enter each percent for each segment of the stacked bar.
For example, I am showing male and female volume in each bar (50 males, 50 females) and the Y axis scale will be stacked up to 100 for the two. In the actual bar I want to show 50% for the value in each stacked bar.
Any way to show both Volume and Share in the same stacked bar? I just want the Y-axis to read the "count / volume" and the actual bar to display the percent. But have just resorted to manually updating everything and it really takes way way too much time.
View 3 Replies
View Related
Jan 21, 2012
I have data in "column F" that I want to highlight. The data is Listed verticaly from Row 1 to 424 in Column F; I want the spread sheet to hightlight the number when it exceeds the previous days 30,60, and 90 day average volume. How I can make this occur?
View 2 Replies
View Related
Sep 15, 2013
I have this formula below which works for finding the Volume Total in range C7:E7...it displays the volume for that date in G7
Code:
=SUMIF(INDIRECT("'"&E$6&"'!B7:B10000"),$G$7,OFFSET(INDIRECT("'"&E$6&"'!B7:B10000")
,,MATCH($E10,INDIRECT("'"&E$6&"'!$C$7:$E$7"),0),,))
but when I use the formula for finding the PLanned Stops - Time in the range G7:H7 it gives the Volume Total from Column D...I want the Planned Stops -Time from H7.....
Code:
=SUMIF(INDIRECT("'"&E6&"'!B7:B10000"),G7,OFFSET(INDIRECT("'"&E6&"'!B7:B10000")
,,MATCH(E13,INDIRECT("'"&E6&"'!G7:H7"),0),,))
View 4 Replies
View Related
Dec 7, 2007
I have a spreadsheet with every day of the month where I input quantities of purchased product. We divide those quantities according to the number of days of pricing period.
So if we purchase volume of 100 on the 1st of the month on a 10 days pricing, I would like to have in my spreasheet a volume of 10 for each 10 first days. Easy so far, where I block is when I start having several deals, at different days and with different pricing period.
See attached file for example.
How can I increment my column? Would it be Macro or can I just stick to formulas?
View 3 Replies
View Related
Dec 22, 2007
Need a way of getting one of 4 values in sheet 2 to perform a calculation with. The aim is to show the total volume of a given item. Because there are four data sources for the actual volume (with varying degrees of accuracy) I've incorporated all of them in one sheet but want to use the values in precedence i.e. the first figure is where I've had someone physically tell me how many fit to a pallet (cubic metre) and know that to be accurate. If the value there is 0 I want to use the next figure as certain other items were measured physically. Column three and four are from some legacy systems and therefore should only be considered if the figures are 0 in the first two columns. Rather than ramble on any more I've attached a sample sheet
View 4 Replies
View Related
May 6, 2008
What is the SINGLE formula for getting Value by given volume and price individually for more then 2 products?
View 4 Replies
View Related
Aug 22, 2007
I have a dataset in one column. Here 4 cells should be turned into 1 in a new column using a middelvalue.
How do i get excel to do this, so it uses 4 new cells from the first row to every new cell in the new column?
View 12 Replies
View Related
Feb 10, 2009
I have been using the attached formula to calculate total volume (customers seen) by hour (between 1 and 2, 2 and 3 and so on...). But how can I change this formula to take a census at a certain period of time (let's do it on the hour to be easy) instead of double-counting those who were there, but left in the same hour and those who just entered in the same hour. I hope this makes sense...a true census really should not count two people twice though which I think is happening here...
I hate to create a sense of urgency, but would love to begin working on this later tonight. Please let me know if you need more detail.
View 8 Replies
View Related
Jul 14, 2009
I have an excel work sheet with 2 columns and hundreds of rows. The first column is time in minutes over a number a years and the second is a list of corresponding data readings (numbers). I want to only select on the hour data from the list however I cannot seem to do it. The times vary in the sense that they are not all just 1 minute apart, some are every minute, then there might be a 10 minute gap in data, a few more minutes, then another gap etc.. All I want to be able to do is to select all the on the hour data from the list and its corresponding data value.
View 6 Replies
View Related
Mar 27, 2007
I am trying to create a conditional formula. I have multiple workers that work for different $$ per hour. I am keeping the hourly rates on one page for security purposes (the person that is going to be updating the hours worked is not very computer savy and they less they have to type the better) and the hours on another... I want copy a formula down the page that will calculate the hours to the hourly rate. The issue is that i want the formula to look at all of the hourly rates (assume they get raises) for Joe and multiply the most recent hourly rate times the hours... in addition I don't want the formula to re-calculate when a new hourly rate is put into the hours sheet.
Example
Jan. 1 joe works 8hours at $10 per hour formula in A1 (or wherever) calculates $80
Jan. 2 joe gets $1 raise
Jan. 2 Joe works 8 hours at $11 per hour formual in A2 calculates $88 but formula in A1 maintains the $80 and does not calculate the $1 raise.
View 12 Replies
View Related
Nov 4, 2008
I have a list of days and hours, and data for whether a type of event either did or did not occur for each sampled hour. I need to return a new value, some sort of "Event Day" value, which will essentially tell me if an event occurred at least once for each day.
Attached is a sheet with 4 days' worth of data. Jan 1st and 3rd 2006 are my two "Event Days".
To complicate things, I can't be sure I have 24 hour readings for each day, so I can't assume each day is a data range (e.g. A1:A24). Hours might be missing. (As such, I am trying to organise along the lines of IF the preceding cell is the same date...)
I also have lots of data stored as months, so would love a formula I could just autofill down. All it needs to do is effectively flag up for me when a day has experienced one event.
View 4 Replies
View Related
