Help with a conditional formula

[Tracey]

Hello

I have been trying to help a user who wants to achieve the following:

It's to do with the number of toilets to be provided on a floor of a
building so for example A1 contains the number of people and A2 must show
the number of toilets required

If cell A1 contains between 1 - 15, then set A2 to 1
If cell A1 contains between 15-30 then set A2 to 2
If cell A1 contains between 31-50 then set A2 to 3

.... and this goes on until we reach 100, checking the value of A1 and
setting A2 accordingly.

The problem for me is when we get to >100 because my user want to ask excel
to do this:

If the value in A1 is > 100, then automatically 4 toilets plus add another
toilet for every 50 (or up to 50) people so for example 116 people would
require 5 toilets.

I did this in cell A2:
=IF(A1<=15,"1",IF(A1<=30,"2",IF(A1<=45,"3",IF(A1<=60,"3",IF(A1<=75,"3",IF(A1<=90,"4",IF(A1<=100,"4")))))))
but I don't know how to deal with the >100 bit... can anyone help us??

thanks very much for any help
Tracey

 

[Leo Heuser]

Hello

I have been trying to help a user who wants to achieve the following:

It's to do with the number of toilets to be provided on a floor of a
building so for example A1 contains the number of people and A2 must show
the number of toilets required

If cell A1 contains between 1 - 15, then set A2 to 1
If cell A1 contains between 15-30 then set A2 to 2
If cell A1 contains between 31-50 then set A2 to 3

... and this goes on until we reach 100, checking the value of A1 and
setting A2 accordingly.

The problem for me is when we get to >100 because my user want to ask
excel to do this:

If the value in A1 is > 100, then automatically 4 toilets plus add another
toilet for every 50 (or up to 50) people so for example 116 people would
require 5 toilets.

I did this in cell A2:
=IF(A1<=15,"1",IF(A1<=30,"2",IF(A1<=45,"3",IF(A1<=60,"3",IF(A1<=75,"3",IF(A1<=90,"4",IF(A1<=100,"4")))))))
but I don't know how to deal with the >100 bit... can anyone help us??

thanks very much for any help
Tracey

Hello Tracey

This formula in A2 will return the proper result for
any number of people.

=IF(A1>100,4+ROUNDUP((A1-100)/50,0),MIN(IF(A1<={15,30,45,60,75,90,100},{1,2,3,3,3,4,4})))

 

[Guest]

Tracey,

Try this formula, have just added a function at the end:

=IF(A1<=15,"1",IF(A1<=30,"2",IF(A1<=45,"3",IF(A1<=60,"3",IF(A1<=75,"3",IF(A1<=90,"4",IF(A1<=100,"4",4+ROUNDUP((A1-100)/50,0))))))))

 

[olasa]

kalsghfakljsfhkljsfh
asdflaölfdjklöja

 

[Tracey]

Thanks Leo, much appreciated

Tracey

 

[Leo Heuser]

Thanks Leo, much appreciated

Tracey

You're welcome, Tracey, and thanks for the feedback

LeoH