Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help
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 he 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 In article , Codeman wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help
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 "Codeman" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help
=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. -- David Biddulph "Codeman" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help
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. "Codeman" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|