help with returning incremental values

szwr197

New Member
Joined
Jan 23, 2019
Messages
2
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
(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
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top