# need excel formula

Discussion in 'Business Applications' started by sgalde, Aug 29, 2007.

Not open for further replies.

Joined:
Aug 29, 2007
Messages:
7
the word problem is:
1 per 25 for the first 50 and 1 per 50 for the remainder exceding 50

=IF(C11/B11>50,((50/25)+(C11/B11-50)/50),(C11/B11)/25)

above is the formula i have, it is not correct, c11/b11 if the number from which this formula is calculated

the other word problem is:
1 per 40 for the first 80 and 1 per 80 for the remainder exceeding 80

=IF(C11/B11>80,((80/40)+(C11/B11-80)/80),(C11/B11)/40)

above is the formula I have, it is also not correct
c11/b11 if the number from which this formula is calculated.

If i do the math manually i do not get the same answers.

I am really excel illeterate.

2. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Hi there,

Sounds like homework. What are you working on?

Joined:
Aug 29, 2007
Messages:
7
it is a formula for calcualting toilet and sink requirements based on an occupant load factor, to make sure commercial spaces meet building codes

4. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
I ran the first formula in Excel and compared it to hand calculations and it worked fine. What sorts of errors are you getting exactly? Give me some figures that don't seem to work.
The second formula is, despite the different variables, the same as the first, so it should work also.

Joined:
Aug 29, 2007
Messages:
7
1 per 25 for the first 50.
If my occupant laod factor is 26.75

that would be 1 toilet for the first 25 and then 1 toilet for the remaining 1.75?

6. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
So are you really wanting a formula that will result in 1 for up to 25, 2 for 26-50, 3 for 51-75 and so on?

Joined:
Aug 29, 2007
Messages:
7
After the first 50 it is one per 50.

Thanks again, and what is the quickest way to learn how to build excel formulas. I encounter a lot of word problems like that for calcualting square foot areas and lots of other code requirements. Let's just say word problems in math were my downfall. Now I need to know them.

8. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
You never answered my question. Is it homework?

Joined:
Aug 29, 2007
Messages:
7
No, I wish it were... This is for my job, I am a commercial interior designer.

10. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
What, exactly, then, will be in the columns B and C? Obviously has to do with how seating capacity per fire code but what is the other?

Joined:
Aug 29, 2007
Messages:
7
SF in b and ocupant laod factor in c

b/c is what toilets are calculated from

12. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
I think this may do what you want:
=IF(C11/B11>50,ROUNDUP((2+(C11/B11-50)/50),0),ROUNDUP((C11/B11/25),0))
Now that I look at it, I am thinking maybe not. Try it out and let me know.

13. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Maybe I'm not understanding right, but maybe...

=C11/B11/50+(C11/B11>25)

That would give you a list of ...

Code:
```25	1
50	2
75	2
100	3
125	3
150	4
175	4
200	5
225	5
250	6
275	6
300	7
325	7
350	8
375	8
400	9
425	9
450	10
475	10
500	11```
Is that right?

14. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
I didn't know you could put a conditional statement (C11/B11>25) in a formula without an if statement...interesting!

15. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Sure. They evaluate to TRUE/FALSE which, when confronted in a mathematical proposition will be coerced to a 1/0 output. I'm just not sure I followed the logic needed for the formula. LOL!

As Seen On