Too many variables for an if than statement?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

repke - 02 May 2006 01:42 GMT

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

Signature

repke

Reply to this Message

vandenberg p - 02 May 2006 04:21 GMT

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

: 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
[quoted text clipped - 4 lines]
: 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

Reply to this Message

vandenberg p - 02 May 2006 04:58 GMT

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

: 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
[quoted text clipped - 7 lines]
: H13:     712
: H14:     713

: And:

: I4:     28
: I5:     28
[quoted text clipped - 7 lines]
: 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
[quoted text clipped - 3 lines]
: 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

: : 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
[quoted text clipped - 4 lines]
: : 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

Reply to this Message

repke - 02 May 2006 17:01 GMT

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

Reply to this Message