Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
repke
 
Posts: n/a
Default Too many variables for an if than statement?


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

  #2   Report Post  
Posted to microsoft.public.excel.newusers
vandenberg p
 
Posts: n/a
Default Too many variables for an if than statement?



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

  #3   Report Post  
Posted to microsoft.public.excel.newusers
vandenberg p
 
Posts: n/a
Default Too many variables for an if than statement?

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

  #4   Report Post  
Posted to microsoft.public.excel.newusers
repke
 
Posts: n/a
Default Too many variables for an if than statement?


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


--
repke
------------------------------------------------------------------------
repke's Profile: http://www.excelforum.com/member.php...fo&userid=9890
View this thread: http://www.excelforum.com/showthread...hreadid=537916

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using variables as first and last cells in range statement Tom from Ga Excel Worksheet Functions 2 February 6th 06 10:08 PM
VBA reseting variables bpeltzer Excel Discussion (Misc queries) 0 February 4th 06 04:52 PM
SET statement tutorial Daminc Excel Discussion (Misc queries) 13 January 17th 06 05:47 PM
If statement Matt Montagliano Excel Discussion (Misc queries) 1 September 8th 05 08:47 PM
Do I need a sumif or sum of a vlookup formula? PeterB Excel Worksheet Functions 0 June 1st 05 12:23 PM


All times are GMT +1. The time now is 06:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"