Formula help!

[Workerl3ee]

I need help with a formula that will give me accurate results.

I have created a staff to child ratio tracker.
0-2 is a ratio of 1:4
2-3 is a ratio of 1:5
3+ is a ratio of 1:10
Over school is 1:15

I have used ROUNDUP(B2/4+B3/5......etc) but the number is not consistently correct.

(Pictured example) It says 4 required but it is actually 5 needed because all ratios fall to the youngest age group.

 

[Joe4]

Welcome to the Board!

You need to apply the ROUNDUP formula to each age bracket, if you want to do it like that, i.e.

=ROUNDUP(B2/4,0)+ROUNDUP(B3/5,0)+ROUNDUP(B4/10,0)+ROUNDUP(B6/15,0)

This will return the 5 you want.

Otherwise, what you are doing is this:
=ROUNDUP(2.25 + 1.00 + 0.60 + 0.00,0)
=ROUNDUP(3.85,0)
=4

 

[Workerl3ee]

Welcome to the Board!

You need to apply the ROUNDUP formula to each age bracket, if you want to do it like that, i.e.

=ROUNDUP(B2/4,0)+ROUNDUP(B3/5,0)+ROUNDUP(B4/10,0)+ROUNDUP(B6/15,0)

This will return the 5 you want.

Otherwise, what you are doing is this:
=ROUNDUP(2.25 + 1.00 + 0.60 + 0.00,0)
=ROUNDUP(3.85,0)
=4

Yes, I tried this way too but then I get the opposite and it over estimates the number for some.

 

[Joe4]

Yes, I tried this way too but then I get the opposite and it over estimates the number for some.

I don't think that shouldn't happen, if you are consistent and always use the logic you displayed in your original example.
Please show us an example where it overestimates it.

 

[kvsrinivasamurthy]

Try.

=CEILING(B2/4,1)+CEILING(B3/5,1)+CEILING(B4/10,1)+CEILING(B5/15,1)

 

[Workerl3ee]

Try.

=CEILING(B2/4,1)+CEILING(B3/5,1)+CEILING(B4/10,1)+CEILING(B5/15,1)

Unfortunately not accurate, over estimating

 

[Joe4]

I asked you to please show us an example where our formulas are over estimating.
The formulas followed the logic you seem to present in your original example.
So if it is not working correctly, you may have not presenting it clearly or entirely, and there may be some important piece of the logic you left out.
If we could see an example of where it is not working the way you envision, I think it may become much more clear to us exactly how you expect this to work.
But right now, you haven't given us very much to work with. Both our formulas work perfectly on the example you presented.

 

[Workerl3ee]

I asked you to please show us an example where our formulas are over estimating.
The formulas followed the logic you seem to present in your original example.
So if it is not working correctly, you may have not presenting it clearly or entirely, and there may be some important piece of the logic you left out.
If we could see an example of where it is not working the way you envision, I think it may become much more clear to us exactly how you expect this to work.
But right now, you haven't given us very much to work with. Both our formulas work perfectly on the example you presented.

I didn't see that part sorry.
So using the ROUNDUP in staff required and underneath I've put the calculations manually in.

Essentially when the first age group of 1:4 has left over they need to borrow the remaimder to make it full from the next age group and so on until we find the absolute minimum number required. It works for the majority but not all and it needs to be accurate for legal purposes

 

[Joe4]

Please pick a specific example that is not working correctly, and walk us through it, showing us exactly what your EXPECTED result should be.
If you are "borrowing" from the other groups, I am not quite sure how you would determine that, since every group has different requirements (you are comparing "apples to oranges").
Like in the very first example you posted in your initial question, if you can "borrow" from other groups, why couldn't the first group of 1:4 "borrow" from the group of 1:10 to make it a total of 4 people instead of 5?

So I think you need to explain the logic of exactly how you wish to do that in detail, as it is not obvious the methodology you want to apply here.