Scale value based on two ratios?

[OaklandJim]

This is more a math question but hey, I'm doing it in Excel. I am working on a tips allocation worksheet. Tips for a specific person are based on two person-specific criteria: 1. hours worked and 2. "tip shares." Tip shares as a value from 1 to 5 indicating a staff person's position such as senior server (5 tip shares) to new dish washer (1 tip share). The tip for one person would be a function of 1. total tips for all people for a shift, 2. total hours worked by all people for a shift, 3. total tip shares for all people for a shift, 4. the person's hours worked and 5. the person's tips share. The best I could imagine is the following which does not seem to work. PersonTips = TotalTips * PersonHours/AllPeopleHours * PersonTipShares/AllPeopleTipShares. Your assistance is very much appreciated.

 

[JamesCanale]

MrExcelPlayground8-1.xlsx
	A	B	C	D	E
1	Tips	$500.00
2
3	Person	Hours	Shares	Share-Hours	Tips
4	Fred	6	5	30	$119.05
5	Mary	10	4	40	$158.73
6	Harry	10	3	30	$119.05
7	Sally	8	2	16	$63.49
8	Jamie	10	1	10	$39.68
9					$500.00
Sheet22

Cell Formulas
Range	Formula
D4:D8	D4	=B4*C4
E4:E8	E4	=D4/SUM($D$4:$D$8)*$B$1
E9	E9	=SUM(E4:E8)