help with returning incremental values

[szwr197]

Hello,

I need help with a spreadsheet that involves safety ratios of kids to adults based on grade ranges. So for instance, if a group of kids is between K and 1st , they need 2 adults for every 12 kids. If there were 24 of them, they would need 4 adults. I have a list of over a 1,000 groups and I want excel to pull for me the number of adults needed for each group. And, even if they don't meet the minimum, they still need at least two adults. When they are in exact multiples, I have it figured out, like the example I provided above. However, k-1st graders need an additional adult for each additional 6 kids. and 2-3 need an additional adult for every 8 kids. That is what is totally throwing me.

What I did was, I set up a reference chart of the grade levels and the adult needed and then I did a vlookup formula off of that. This is the formula =IF(ROUNDDOWN(H11/(VLOOKUP(G11, $K$1:$M$7, 2, 0)),0)*2<2, "2", ROUNDDOWN(H11/(VLOOKUP(G11, $K$1:$M$7, 2, 0)),0)*2)

This is my reference chart [TABLE="width: 317"]
<tbody>[TR]
[TD]level[/TD]
[TD]two unrelated adults per[/TD]
[TD]Plus 1 for each[/TD]
[/TR]
[TR]
[TD]K-1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]2-3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]4-5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]6-8[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]9-10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]11-12[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]

So if I had 30 K-1, the number of adults should be 5. How would I make it do that?

Thanks in advance for any help.

 

[horrellbt01]

(Edited to cover rounding issues)

Here is what I did.

I recreated your data above, with Level starting in cell A1

The formula you need is as follows:
=ROUNDUP((F2-(VLOOKUP(E2,A1:C7,2,0)))/(VLOOKUP(E2,A1:C7,3,0))+2,0)

With F2 being the number of students you have and E2 being the grade level

 

[szwr197]

Thank you for your quick response. I am not super great with excel so it will take me a bit to digest what you gave me and try to insert it into my document. Thank you very much for the help.