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!

 

[oldbrewer]

	infants	teachersInf	spare teacher	toddlers	teacherstod	spareteacher	total spare teacher	teachers needed
	5	2	0.75	7	2	0.833333333	1.583333333	3
	would this table suit you

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

 

[Shadowgaze]

	infants	teachersInf	spare teacher	toddlers	teacherstod	spareteacher	total spare teacher	teachers needed
	5	2	0.75	7	2	0.833333333	1.583333333	3
	would this table suit you

<tbody>
</tbody>

Ive already got something like that I'm trying to make a tracking form to show me my teacher count for each 30 min of each day so i need a formula to do the calculation i already have the chart made

 

[oldbrewer]

ok, there was no mention of a chart in post 1.

I assume you now want to deploy teachers from your teacher reserves ensuring each teacher gets their "fair share" of work.

good luck - it is beyond my skill level.

 

[Shadowgaze]

ok, there was no mention of a chart in post 1.

I assume you now want to deploy teachers from your teacher reserves ensuring each teacher gets their "fair share" of work.

good luck - it is beyond my skill level.

Yeah i am looking to figure out a way to Add the children up but the state has us grouping them together by age of youngest child within the ratio so its hard for me to get the numbers to always come out right i was just trying to figure out if i can have the remainder roll over to the next age group and so forth. ive used If commands and all the like they get close but still not totally accurate i can deploy my own teachers but when making schedules its allot more helpful if they chart would let me know if im going over rather than me doing it in my head each 30 min or so lol. its beyond my skill level as well hence me asking

if anyone else can help out i would appreciate it even if its just an idea or an equation or command that may work for part of it i might be able to use your idea to figure it out myself .... happened before lol

 

[oldbrewer]

the state has us grouping them together by age of youngest child within the ratio
---what does this mean

maybe if you have 1 infant and 5 toddlers that is 6 infants

solution ---make toddlers into infants by multiplying by 2/3

so 7 toddlers = 14/3 infants = 4 and 2/3

so all kids are infants and easy to compute teachers needed

 

[Shadowgaze]

the way it would work
1 Infant
5 toddlers

is 1 infant +3 Toddlers would be 1 teacher
the remaining 2 Toddlers would need a teacher but also i could have 4 more toddlers come in and still only need 2 teachers
so i do not want to calculate all kids as infants that would make me have too many teachers

 

[oldbrewer]

using your figures, 1 infant plus 9 toddlers converted to infants = 1 plus 9 x 2/3 = 1 plus 6 = 7 infants = 2 teachers

1 infant plus 10 toddlers = 7 and 2/3 infants = 2 teachers
1 infant plus 11 toddlers = 8 and 1/3 infants = 3 teachers

 

[oldbrewer]

				infants
			1	2	3	4	5	6	7	8	9	10	11	12
		1	1	1	1	2	2	2	2	3	3	3	3	4
		2	1	1	2	2	2	2	3	3	3	3	4	4
toddlers		3	1	2	2	2	2	3	3	3	3	4	4	4
		4	1	2	2	2	2	3	3	3	3	4	4	4
		5	2	2	2	2	3	3	3	3	4	4	4	4
		6	2	2	2	3	3	3	3	4	4	4	4	5
		7	2	2	2	3	3	3	3	4	4	4	4	5
		8	2	2	3	3	3	3	4	4	4	4	5	5
		9	2	3	3	3	3	4	4	4	4	5	5	5
		10	2	3	3	3	3	4	4	4	4	5	5	5
		11	3	3	3	3	4	4	4	4	5	5	5	5
		12	3	3	3	4	4	4	4	5	5	5	5	6
					this table tells you how many teachers are needed

<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Shadowgaze]

				infants
			1	2	3	4	5	6	7	8	9	10	11	12
		1	1	1	1	2	2	2	2	3	3	3	3	4
		2	1	1	2	2	2	2	3	3	3	3	4	4
toddlers		3	1	2	2	2	2	3	3	3	3	4	4	4
		4	1	2	2	2	2	3	3	3	3	4	4	4
		5	2	2	2	2	3	3	3	3	4	4	4	4
		6	2	2	2	3	3	3	3	4	4	4	4	5
		7	2	2	2	3	3	3	3	4	4	4	4	5
		8	2	2	3	3	3	3	4	4	4	4	5	5
		9	2	3	3	3	3	4	4	4	4	5	5	5
		10	2	3	3	3	3	4	4	4	4	5	5	5
		11	3	3	3	3	4	4	4	4	5	5	5	5
		12	3	3	3	4	4	4	4	5	5	5	5	6
					this table tells you how many teachers are needed

<tbody>
</tbody>

hmmm now that is an idea... i could make a table and use if and statements to locate the number ... that would be a HUGE if and statement though lol

but a new way to look at my problem maybe this will lead me to my conclusion
Anyone else have a different way to look at it? more ideas the better