Tracking Profit

[KristenHanson]

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?

[#2]

@KristenHanson 

Where is Jonathan's hourly rate stored?

[#3]

I dont have it stored anywhere right now, but I could create another spreadsheet that has each crew person's hourly rate.

[#4]

Can it pull from another spreadsheet within the same workbook?
What if there's multiple crew members on the same job?

[#5 · Accepted solution]

@KristenHanson 

Let'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.

[#6]

Perfect! Thank you! Yes, so for multiple crew leaders on the same job, we have in the same cell "Adam/Jonathan" Then those crew leaders also sometimes have their own crew MEMBERS. So on one job, it could potentially be 3-5 people on one big job. So Crew Leader (column P) might have "Adam/Jonathan" and Crew Member (column Q) might have "Devin/Lorenzo". Is there a formula for this scenario?

[#7]

@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?

[#8]

@KristenHanson 

Could you attach a sample workbook with anonymized data?

[#9]

@Hans Vogelaar Will this work?

[#10]

@KristenHanson 

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.

[#11]

@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 ...?

[#12]

@KristenHanson 

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.

[#13]

The total in the hours column is both the employees together, so total hours combined. So each having worked 6 hours/each.

[#14]

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?

[#15]

And if there's one person listed in Crew Leader (column P) and two listed under Crew Member (column Q), Column I is still the combined hours between all the people together.

[#16]

One more than Hans - is it possible for the formula to come back blank if there's nothing in either of the Pricing or total hours column?

[#17]

---

@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?

---

@KristenHanson 

See the attached version.

[#18]

You seriously rock! Any interest in allowing me to pick your brain for a little more in depth workbook?

[#19]

@KristenHanson 

I can't promise anything, but go ahead.

[#20]

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