Help with a conditional formula
 

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

"Tracey" skrev i en meddelelse
...
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(A1100,4+ROUNDUP((A1-100)/50,0),MIN(IF(A1<={15,30,45,60,75,90,100},{1,2,3,3, 3,4,4})))

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

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))))))))

"Tracey" wrote:

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

kalsghfakljsfhkljsfh
asdflaölfdjklöja


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=383050

Thanks Leo, much appreciated

Tracey

"Tracey" skrev i en meddelelse
...
Thanks Leo, much appreciated

Tracey
You're welcome, Tracey, and thanks for the feedback :-)

LeoH