Forum Discussion
Tracking Profit
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?
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.
25 Replies
Where is Jonathan's hourly rate stored?
- I dont have it stored anywhere right now, but I could create another spreadsheet that has each crew person's hourly rate.
- Can it pull from another spreadsheet within the same workbook?
What if there's multiple crew members on the same job?
