Mar 11 2021 07:49 AM - edited Mar 11 2021 07:53 AM
I want a column to be able to calculate BASIC profitability of a job. If the crew leader column (P) says "Jonathan", I want column T (not created yet), to take the total price of the job (column J) and divide by the crew member's hourly pay (ie: $16/hr) times by the total hours spent (column I)
So if Jonathan makes $16/hr. and the job is priced at $400 and it took him 6.63 hours (first row example in screenshot), I want the cell to breakdown: 16*6.63 =106.08 then $400-106.88 = $293.92
Is there any easy way to do this? What formula would I use? Do I need multiple columns to make this work?
Mar 11 2021 08:14 AM
Mar 11 2021 09:35 AM
Mar 11 2021 11:27 AM
SolutionLet's say you create a new worksheet in the same workbook and name it Rates.
In A1, enter Employee and in B1, enter Hourly Rate.
In A2, A3 etc., enter the names of the employees (Jonathan etc.).
In B2, B3 etc., enter the corresponding hourly rates.
Go back to the profit sheet.
Enter the following formula in T2:
=J2-I2*IFERROR(VLOOKUP(P2, Rates!$A$2:$B$1000, 2, FALSE), 0)
If you want to take multiple crew members into account, please provide more detailed information about the setup.
Mar 12 2021 05:27 AM
Mar 12 2021 05:38 AM
@Hans Vogelaar I've entered the formula suggested and this is what shows up. I've also included a screenshot of the rates spreadsheet as well. It doesnt seem to be working. I know on two of them there are blanks in the Crew Leader column, but the others, "David", "Elias", "Josue" and "Miguel" are all input on the rates spreadsheet. Do you see something I'm doing wrong perhaps?
Mar 12 2021 06:08 AM
Could you attach a sample workbook with anonymized data?
Mar 12 2021 08:41 AM
Cell P2 contains "Jonathan " with a space after the name.
And your list on the Rates sheet begins in row 1 instead of row 2, so the formula in P2 should be
=J2-I2*IFERROR(VLOOKUP(P2, RATES!$A$1:$B$1000, 2, FALSE), 0)
I'll see if I can find how to handle multiple names.
Mar 12 2021 08:55 AM
@KristenHanson One more thing: if column Q contains Devin/Martin and column I contains 12, have Devin and Martin each worked 12 hours? Or has each worked 12/2 = 6 hours? Or ...?
Mar 12 2021 09:01 AM
Here is one option:
1) Use Replace to remove all spaces from columns P and Q.
2) Enter the following formula in T2 and fill down:
=IFERROR(J2-I2*SUMPRODUCT(ISNUMBER(SEARCH("/"&RATES!$A$1:$A$100&"/","/"&'JAN 2021'!P2:Q2&"/"))*RATES!$B$1:$B$100),0)
Adjust the ranges if you have more than 100 names on the Rates sheet.
Mar 12 2021 11:10 AM
Mar 12 2021 11:20 AM
Mar 12 2021 11:39 AM
Mar 12 2021 11:54 AM
Mar 12 2021 12:37 PM
@KristenHanson wrote:
YOU ARE THE BEST!! Thank you so, so so much!! Can I ask one small other favor? If we add a column for "expenses on the job" (making it column K), can you give me the formula to do exactly above, but then to substract any job related expenses, equaling the basic profit?
See the attached version.
Mar 12 2021 12:50 PM
Mar 15 2021 02:08 PM
@Hans Vogelaar We currently track field guy's bonuses by hour much they bill per hour. We break it down weekly, to post a weekly leaderboard to see show who's performing the best and who needs to pick it up. In the workbook attached, you'll see the weeks broken up. Each guy has a spot for "solo" hours (they worked the job by themselves) and then "joint" (they worked with other techs and the hours are split right down the middle evenly). If we use this month for example, and focus on week 1 - I would love a formula that is able to pick up (from the original Work Hx spreadsheet I shared with you) in column "N" any dates ranging from 3/1/21-3/7/21. And out of those, any that say "Adam" (doesnt matter if anyone is in the crew member column or not), that price (column J) and hours (column i) gets plugged into the Bonus workbook (B4 and C4,) and so on for each person. Now if two or more people are in the crew leader spot, I would want those numbers split accordingly by how many people are in it, and then plugged into the corresponding spot on the Bonus spreadsheet.
If no one is listed in the crew leader column, I would then want everything to default based on the crew member (column R) and plugged into the bonus spreadsheet accordingly. And so on for each week of the month.
Mar 11 2021 11:27 AM
SolutionLet's say you create a new worksheet in the same workbook and name it Rates.
In A1, enter Employee and in B1, enter Hourly Rate.
In A2, A3 etc., enter the names of the employees (Jonathan etc.).
In B2, B3 etc., enter the corresponding hourly rates.
Go back to the profit sheet.
Enter the following formula in T2:
=J2-I2*IFERROR(VLOOKUP(P2, Rates!$A$2:$B$1000, 2, FALSE), 0)
If you want to take multiple crew members into account, please provide more detailed information about the setup.