Don't know what formula to use...

[CherryxDarling]

I'm creating a spreadsheet that basically tells someone how many bathrooms, toilets, drinking fountains etc. are needed per occupant load. I have different ratios for male/female, and what kind of building it is. So that being said I need a formula for these ratios:

Male: 1 per 75 for the first 1,500 and 1 per 120 for the remainder exceeding 1,500

Female: 1 per 40 for the first 1,520 and 1 per 60 for the remainder exceeding 1,520

I want to make it so that when I input a # into cell E19 for example, it will tell me how many bathrooms are needed according to this ratio. However, whatever # is inputed into E19 needs to be divided in half, half of it for males, and half for females.

I know it's complicated and I'm having trouble explaining it all as simply as I can, but if someone can give it a shot I would greatly appreciate it.

 

[Zakkaroo]

I think i've understood your query correctly. I've done male to start with ... tell me if this is on the right lines.

You would paste this code into whichever cell you want the result to appear in

=IF((E19/2)>1500,20+ROUNDDOWN((E19-1500)/120,0),ROUNDDOWN(E19/75,0))

 

[CherryxDarling]

I think i've understood your query correctly. I've done male to start with ... tell me if this is on the right lines.

You would paste this code into whichever cell you want the result to appear in

=IF((E19/2)>1500,20+ROUNDDOWN((E19-1500)/120,0),ROUNDDOWN(E19/75,0))

Unfortunately that doesn't give me the right answer, but I think we're on the right track here.

 

[Zakkaroo]

Unfortunately that doesn't give me the right answer, but I think we're on the right track here.

Okey dokey I can see where i've gone wrong, i'll try and play with it now, but in work lol

 

[Zakkaroo]

ok. Try this one instead!

=IF((E19/2)>1500,20+ROUNDDOWN(((E19/2)-1500)/120,0),ROUNDDOWN((E19/2)/75,0))

 

[lenze]

Perhaps change ROUNDDOWN to ROUNDUP in Zakkaroos formula

lenze

 

[CherryxDarling]

ok. Try this one instead!

=IF((E19/2)>1500,20+ROUNDDOWN(((E19/2)-1500)/120,0),ROUNDDOWN((E19/2)/75,0))

Okay that worked! However, I know I should have stated this earlier, but I need the decimal to always be rounded up regardless if it's 29.1 or something. Is that something we could work into the formula?

 

[jim may]

Why not provide 3 or 4 examples (of the input) and also the expected result of each.

 

[Zakkaroo]

Perhaps change ROUNDDOWN to ROUNDUP in Zakkaroos formula

lenze

Hi Lenze, I THINK round down was right ... I assume that what they're are after is 149 Men = 1 toilet and 150 men = 2. If that's not what Cherry wants then my bad!

What I had failed to do was divide everything by 2

 

[Zakkaroo]

Okay that worked! However, I know I should have stated this earlier, but I need the decimal to always be rounded up regardless if it's 29.1 or something. Is that something we could work into the formula?

In that case, as Lenze mentioned above, replace ROUNDDOWN, with ROUNDUP and you should then be good to go.

Do you want me to also produce the FEMALE equation or are you ok to edit?

i.e

=IF((E19/2)>1500,20+ROUNDUP(((E19/2)-1500)/120,0),ROUNDUP((E19/2)/75,0))