Counting using raitios and combining Remainders

[Shadowgaze]

I love excel and use it for all my business needs ive recently made a very large workbook with many calculations and they all work the way i want. save for one section! i for the life of me cannot get this to calculate correctly. please help me!

here is what i am attempting
i need to count children at my daycare and then use 4 different ratios to figure out how many teachers i need to have in attendance normally this would be simple except if the ratio is not a whole number you need to combine kids from a lower ratio to a higher ratio and the higher ratio must now take on the ratio of the lower ratio kids

IE the ratio for infants is 4 to 1 and toddlers is 6 to 1..... if i have 6 infants and 5 toddlers .... 2 of the toddlers need to be counted as infants to get the infants to a whole number and the remainder of toddlers can be counted at the toddler raitio
so
4 infants = 1 teacher
2 infants + 2 Toddlers = 1 Teacher
3 remaining Toddlers = 1 Teacher


img host

sometimes the calculations are right but they are NOT all the time and its causing allot of unessential problems at work
i know its probably something easy but i cannot figure it out please help!

 

[shg]

Interesting problem.

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​			Initial​	Initial​	Final​	Final​	`	`
2​		K/T​	Kids​	Teachers​	Kids​	Teachers​
3​	Infant	4​	5​	2​	8​	2​		B3:B6: Input
4​	Toddler	6​	8​	2​	6​	1​		C3:C6: Input
5​	Preschool	10​	3​	1​	7​	1​		D3: =CEILING(C3/$B3, 1)
6​	School	12​	5​	1​	0​	0​		E3: =MIN($C$9 - SUM(E$2:E2), CEILING(SUM(C$3:C3) - SUM(E$2:E2), B3))
7​								F3: =CEILING(E3/$B3, 1)
8​
9​			21​	6​	21​	4​

 

[oldbrewer]

if you choose to use a table like mine, all you need is (for 4 toddlers and 7 infants)

=offset ($d$9,match(4,$d$10:$d$21,0),match(7,$e$9:$p$9,0))

clearly the 4 and the 7 can be cell references

D9 is the cell just above 1 toddler

 

[oldbrewer]

	infants	7	8	9	9	9			7	7	7	7	7			7	7	7	7	7
	toddlers	8	8	9	10	11			8	8	8	8	8			8	8	8	8	8
	infant equiv	12.3	13.3	15.0	15.7	16.3			12.3	12.3	12.3	12.3	12.3			12.3	12.3	12.3	12.3	12.3
	teachers	4	4	4	4	5			4	4	4	4	4			4	4	4	4	4
	spare teacher capacity	0.9	0.7	0.3	0.1	0.9			0.9	0.9	0.9	0.9	0.9			0.9	0.9	0.9	0.9	0.9
	staffing status	under by 1	over by 1	correct	correct	over by 1			under by 1	under by 1	correct	correct	correct			under by 2	correct	correct	correct	under by 1
		Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri
		06/04/2015	07/04/2015	08/04/2015	09/04/2015	10/04/2015			13/04/2015	14/04/2015	15/04/2015	16/04/2015	17/04/2015			20/04/2015	21/04/2015	22/04/2015	23/04/2015	24/04/2015
	teacher1	1	1	1					1	1	1
	teacher2															1	1	1
	teacher3
	teacher4	1	1	1	1	1			1		1	1	1			1	1	1	1
	teacher5									1		1	1
	teacher6		1	1	1	1					1	1	1				1	1	1	1
	teacher7
	teacher8	1	1	1	1	1			1	1	1	1						1	1	1
	teacher9					1							1							1
	teacher10		1		1	1											1		1
			teacher usage since April 6th, 2015
			teacher1	6
			teacher2	3
			teacher3	0
			teacher4	13
			teacher5	3
			teacher6	11
			teacher7	0
			teacher8	12
			teacher9	3
			teacher10	5
		here is another idea - a system to tell you how many teachers you need
		and how many you currently have deployed
		you could highlight teacher shortages in red by using conditional formatting
		and you can see your surplus capacity to aid decision making
		the lower table tells you how many days each teacher has worked

<colgroup><col><col><col><col span="2"><col><col><col><col><col><col span="4"><col><col><col span="5"></colgroup><tbody>
</tbody>