Hello:
Here is an alternative that involves just one statement:
=CHOOSE((E4<=50)+(E4<=305)+(E4<=393)+(E4<=500)+(E4 <=712)+(E4<=1000),77, E4*0.1081,54.05,E4*0.1373,42,28)
(it is all one statement, the line will probably wrap)
It will return #value if the weight exceeds 1000. You could include
a test for greater than 1000 and produce a message.
Pieter Vandenberg
vandenberg p wrote:
: Hello:
: If statements are probably not the best way to solve this problem. It seems
: a vlookup table may serve you better. Assume a you did that the
: weight is in E4. Then in F4 put the following formula: (this will be your
: price)
: =VLOOKUP(E4,H4:I14,2)
: In H4 to I14 put the following values:
: H4: 0
: H5: 50
: H6: 51
: H7: 305
: H8: 306
: H9: 393
: H10: 394
: H11: 500
: H12: 501
: H13: 712
: H14: 713
: And:
: I4: 28
: I5: 28
: I6: 42
: I7: 42
: I8: =E4*0.1373
: I9: =E4*0.1373
: I10: 54.05
: I11: 54.05
: I12: =E4*0.1081
: I13: =E4*0.1081
: I14: 77
: This assumes that you round weight to whole values. If not adjust accordingly.
: Also when the weight is entered do not worry if some values in the lookup
: table are not correct since values beyond the value entered in e4 do not matter.
: As an example if you enter say 58 the values, beyound 305 (eg in I8 and I9 etc)
: will have no meaning. Just as if you enter 350 the value beyond 393 (eg 501)
: will have no meaning. I think you can figure out the logic and see that you
: could handle almost an unlimited number of rates.
: Pieter Vandneberg
: repke wrote:
: : Hello
: : I'm pretty much a novice at using Excel but made a formula a couple
: : years ago to calculate freight. There are 2 basic scenarios, if the
: : weight falls between a certain brackets it gets a set price or it gets
: : multiplied by a percentage. I think the max number of if than
: : statements is seven... correct? My excel file was corrupted about 3
: : months back and I have been unsuccessful in recreating it. I'm open to
: : using a more appropriate formula or fixing the current one. Either way
: : I appreciate the help.
: : =IF(E4<=50,28,IF(E4<=305,42,IF(E4<=393,E4*0.1373,I F(E4<=500,54.05,IF(E4<=712,E4*0.1081,IF(E4<=1000,7 7))))))
: : Thanks
: : Chris
: : --
: : repke
: : ------------------------------------------------------------------------
: : repke's Profile:
http://www.excelforum.com/member.php...fo&userid=9890
: : View this thread:
http://www.excelforum.com/showthread...hreadid=537916