Formula help

[Guest]

I am trying to write a formula in several cells. I can figure the solution
by hand but I do not know how to write it in excel. Here is the problem.

Given:
Occupant load; example 4000
The occupant load must be multiplied by 50% to produce occupant loads
equally for men and women. That answer is applied to the following;
For the first 1500 people the ratio of plumbing fixtures is 1 to 75
For the amount of people greater than 1500 the ratio of plumbing fixtures is
1 to 120

Long hand:
50% of 4000 is 2000
Since 2000 is above 1500 then 1500 divided by 75 gives the first count of
fixtures (answer 20).
2000 minus 1500 equals 500 divided by 120 gives the second count of fixtures
(answer 15.4).
The total fixture count must be rounded up if there is a decimal in the
answer (answer 36).

When to occupant load is 1500 or less after the 50% split then the ratio of
1 to 120 does not apply.

I just do not know how to put this formula in excel. I was thinking it was
one of the “IF†scenarios.

Help would be appreciated.

Codeman

 

[JE McGimpsey]

One way:

=ROUNDUP(SUMPRODUCT(--(A1>{0,3000}) --(A1-{0,3000}),
{0.01333333,-0.005})/2,0)

or alternatively:

=ROUNDUP(SUMPRODUCT(--((A1/2)>{0,1500}), --((A1/2)-{0,1500}),
{0.01333333,-0.005}),0)

The technique is explained here:

http://www.mcgimpsey.com/excel/variablerate.html

Note: I assumed your description was correct, rather than your
calcuation, since 2000-1500 = 500, 500/120 = 4.17, not 15.4

 

[Guest]

Here's another alternative. If you're "Occupant load" is in cell A1:

=ROUNDUP(MIN(A1*0.5,1500)/75+MAX(0,A1*0.5-1500)/120,0)

Hopefully I am interpretting correctly (in your example, 500/120 is not 15.4).

Will

 

[David Biddulph]

=ROUNDUP(MIN(A1*50%,1500)/75+MAX(0,A1*50%-1500)/120,0)

Note that 500/120 is 4.167, not 15.4, so your total is 24.17, rounded up to
25.

 

[Guest]

Thanks for eveyone's help. I used one of the suggestions and modified some
input in the formula with applicable cells. Oh, sorry about my typo in one
of the answers.