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.

 

[CherryxDarling]

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

Okay here is a snapshot of the excel spreadsheet I'm working on:

http://www.flickr.com/photos/61240762@N03/5577431310/sizes/l/in/photostream/

The # of occupants will be inputed into cell E19 and the results should be displayed in cells F20 and G20.

So if you put 5200 in cell E19, you should get 30 for males (F20) and 56 for females (G20)

 

[CherryxDarling]

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

I tried editing it and I kept getting the wrong answer, so if you wouldn't mind I would really appreciate it.

 

[Zakkaroo]

For FEMALES

=IF((E19/2)>1520,38+ROUNDUP(((E19/2)-1520)/60,0),ROUNDUP((E19/2)/40,0))

 

[CherryxDarling]

For FEMALES

=IF((E19/2)>1520,38+ROUNDUP(((E19/2)-1520)/60,0),ROUNDUP((E19/2)/40,0))

Oh my god thank you so much! I can do all the other formulas for my spreadsheet just fine now, but this one had me wanting to bang my head against the wall. It all works perfectly =)

 

[CherryxDarling]

Okay just one more thing and I'm done:

Would it be possible to make it so that when nothing is in cell E19 it won't display anything in F20 and G20? Right now when there's nothing in E19 it displays 0 in those cells and I would like it to be blank.

 

[CherryxDarling]

Would this be correct?

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

 

[jim may]

I created a table in Range A1:E3 (Which can be modified), but formula in F20 and G20 read off the amts therein... HTH Jim

Excel Workbook
	A	B	C	D	E	F	G
1		For First	Count	Each	Over 1st
2	Male	1500	75	20	120
3	Female	1520	40	38	60
4
5
6
7
8
9
10
11
12
13
14
15
16
17						Males	Females
18						50%	50%
19					5200
20						30	56
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D2	=B2/C2
D3	=B3/C3
F20	=IF(($E$19*F$18)<$B$2,ROUNDUP(($E$19*F$18)/$C$2,0),IF(($E$19*F$18)>$B$2,$D$2+ROUNDUP((($E$19*F$18)-$B$2)/$E$2,0),0))
G20	=IF(($E$19*G$18)<$B$3,ROUNDUP(($E$19*G$18)/$C$3,0),IF(($E$19*G$18)>$B$3,$D$3+ROUNDUP((($E$19*G$18)-$B$3)/$E$3,0),0))

 

[Zakkaroo]

Would this be correct?

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

Yeah, that should definitely work. Sorry, didn't get chance to reply before you solved it yourself lol Hope all is well now.

 

[CherryxDarling]

Thank you so much for your help.

 

[CherryxDarling]

Yeah, that should definitely work. Sorry, didn't get chance to reply before you solved it yourself lol Hope all is well now.

I actually copied and pasted it from another formula I had in the spreadsheet lol. Forgot I had already done it. Thanks again for all your help.