Too many variables for an if than statement?

[repke]

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,IF(E4<=500,54.05,IF(E4<=712,E4*0.1081,IF(E4<=1000,77))))))

Thanks
Chris

[vandenberg p]

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 <[email protected]> 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,IF(E4<=500,54.05,IF(E4<=712,E4*0.1081,IF(E4<=1000,77))))))

: 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

[vandenberg p]

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 <[email protected]> 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 <[email protected]> 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,IF(E4<=500,54.05,IF(E4<=712,E4*0.1081,IF(E4<=1000,77))))))

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

[repke]

Thanks Pieter !!! I'll will try this out tonight