Forum Discussion
Formula help
Ok, I understand now.
If you check the sum of hours worked for bussers and runners respectively, and only subtract when there are some hours worked by at least one busser or runner, then again only subtract from that when there are at least some hours worked by a bartender, you'll get what you need.
So, for cell H2 for Kristi, for example, you could use this:=G2-IF(SUM($L$2:$L$3)>0,$N$2,0)-IF(SUM($L$6:$L$7)>0,$N$6,0)That is to say, subtract from the total tips earned, the amount for the bussers only if bussers hours were greater than zero, and the amount for the runners only if the runners hours were greater than zero.
This amount is then what you calculate the bartender's tips from (which you already have in cells N10:P10).
Then finally, the amount left for the server is column H minus the bartender's tips but only if there were bartender hours worked. So this formula goes in B10:
=H2-IF(SUM($L$10:$L$11)>0,$N$10,0)I edited a copy of your workbook on the sheet "New". See attached.
Hi, I've added the sheet I'm working with if.
In the "add if a busser/runner wasn't working" that result should be if only 7% was taken rather than 14%.
Some questions:
- how many bussers can there be? (presumably between 0 and some number. 1? 3? something else?)
- does every busser get a % of all the servers' tips?
- how many runners can there be? (again, is it between 0 and some number? Is there a maximum number of runners?)
- does every runner get a % of every server's tips? If so, why does Beth not have any tip money next to her name in cells N7:P7?
- it looks like the bartender(s) get 25% of whatever is left over after giving money to the bussers and runners. Does every bartender get 25% of what remains? What's the maximum number of bartenders there can be? Presumably not that many,... or the server would have nothing left!
I know, I prolly explained it horribly, sorry.
Bussers/runners/bartenders are an entity, so, they get 7%/7%/25% regardless of how many ppl are doing that job, 0-3.
Beth & Manny are runners that share 7% of the tip if they worked together, sometimes there's only 1 person working.
Bartender gets 25% after the 7%-7% has been taken out.
My problem is, what formula would it be if there was only 1 runner or busser working. What would be left after to take the 25%.
Ok, I understand now.
If you check the sum of hours worked for bussers and runners respectively, and only subtract when there are some hours worked by at least one busser or runner, then again only subtract from that when there are at least some hours worked by a bartender, you'll get what you need.
So, for cell H2 for Kristi, for example, you could use this:=G2-IF(SUM($L$2:$L$3)>0,$N$2,0)-IF(SUM($L$6:$L$7)>0,$N$6,0)That is to say, subtract from the total tips earned, the amount for the bussers only if bussers hours were greater than zero, and the amount for the runners only if the runners hours were greater than zero.
This amount is then what you calculate the bartender's tips from (which you already have in cells N10:P10).
Then finally, the amount left for the server is column H minus the bartender's tips but only if there were bartender hours worked. So this formula goes in B10:
=H2-IF(SUM($L$10:$L$11)>0,$N$10,0)I edited a copy of your workbook on the sheet "New". See attached.