Dividing # of investigation between different Officers (When 2 Officers must get a fixed #)

[Robinho]

e.i A department has 7 Police Officers. The Chief Officer has 98 cases to be divided amongst each officer. Officer #1 & #2 must only get 4 cases each.

Question - What formula do I use to ensure that the reminder # of cases is equally divided amongst the other 5 Officers and that there are no decimals.

• Cell A2- number of cases to be assigned\ B2 Officer #1 \ C2 Officer #2 \ D2 Officer #3 / E2 Officer #4 \ F2 Officer #5 \ G2 Officer #6 \ H2 Officer #7 / I2 Validation of cases assigned is the same Cell A2
• Officers # 1 & 2 will get a total of 4 cases each on a monthly basis.
• Officers #3/#4/#5/#6/#7 is assigned the rest of the cases. (i.e 98 cases to be assigned. Officers #1&#2 get one (4) cases each, and the remainder (90) is divided by Officers 3,4,5,6, and 7. (leaving the last officer to receive the Odd case (the extra case in the batch)

Please let me know if you have any questions. I've spent the entire day trying to figure this one out.

Really appreciate any recommendation.

Thank you,

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2	99	4	4	18	18	18	18	19
3	98	4	4	18	18	18	18	18
4	97	4	4	18	18	18	18	17
Main

Cell Formulas
Range	Formula
B2:C4	B2	=MIN(INT($A2/7),4)
D2:G4	D2	=ROUND(($A2-SUM($B2:$C2))/5,0)
H2:H4	H2	=A2-SUM(B2:G2)