Forum Discussion
Tracking Profit
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.
HansVogelaar Will this work?
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.
See if this works for you - please check carefully.
In the Dummy Work Hx workbook, I have changed the dates in column N to random dates in March so that there are some results in the Bonus workbook.
As before, open the Dummy Work Hx workbook first.
- I know, I'm sorry! How complicated it is is why I need formulas to figure it out for me. haha. I spend so much time filtering and plugging in numbers each week! If it's too much, I completely understand, and appreciate the time you've helped me with so far!
No, I didn't take that into account. It's getting complicated. I'll have to work on it again.
- Added columns is great if it makes it easier! 🙂 Does this allow that if there's a crew LEADER, it's only giving the solo hours to the crew leader? Basically, the crew member's tracking only count if there is no crew leader. Otherwise, all the $ and hours get logged under the crew LEADER.
I had to make a few changes to do this.
I added four helper columns on the right hand side of the Dummy Work Hx workbook.
And in the Bonus workbook, I split the date ranges into two separate cells; this makes it much easier to use them in the formulas.
Open the Dummy Work Hx workbook first, then the Bonus workbook.
I only added formulas for the first week. All formulas return 0 because the Dummy workbook doesn't have dates in March.
I will have to work on that. It's late in the evening here, so it'll have to be tomorrow.
HansVogelaar 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.
I can't promise anything, but go ahead.
- You seriously rock! Any interest in allowing me to pick your brain for a little more in depth workbook?
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.
- 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?