If, And, Or Formula with range of Conditions

[rajkenya1]

Hi

I need some help on the attached spread sheet.

Example - In cells B4 to B7 i have a range of children starting from 13 and below to 50 and above. In Cells C4 to C7 i have number of supervisors required and Cells D4 to D7 i have number of carers required.

So for example if i have 13 kids i need 1 supervisor and if i have 50 i need 2 supervisors.

Similarly if i have 13 kids of below i need 1 carer, if have 13 kids but below 26 i need 2 carers. If i have 26 to 39 kids i have 3 carers etc.

In the attached sheer from cell c11 i have manually inserted what the answer should be. Can anyone help to formularise it.

Many thanks

 

[Luke M]

You need to adjust the lookup table a little bit, but something like this will work nicely.

 

[alacrity59]

I think you have the lookups on the wrong side of the issue Luke. Maybe this.

 

[rajkenya1]

Thanks Luke

I think this should work. I am not sure what the gentleman below is referring to by changing the looks up and trying again.

Thanks very much Luke.

Appreciate it

You need to adjust the lookup table a little bit, but something like this will work nicely.

 

[rajkenya1]

Thanks

Your spreadsheet doesn't have any formulas mate.

Please send again

Cheers

I think you have the lookups on the wrong side of the issue Luke. Maybe this.

es

 

[alacrity59]

I have the lookups on the wrong side of the issue so I put the formulas in c4:d7. Basically the same idea as Luke's. Sorry for the confusion.

 

[rajkenya1]

Thanks so much buddy.

Appreciate your help.

Kind regards

I have the lookups on the wrong side of the issue so I put the formulas in c4:d7. Basically the same idea as Luke's. Sorry for the confusion.