|
IF Statement
I have created an Excel Order form. After the Subtotal and Tax have been
calculated, I want the form to automatically put in the handling charges. The IF Statement that I have used to format the cell is: =IF((E39+E40)<10.01,3,IF(10.01<25,4,IF(25.01<40,5) ))---where E39 = subtotal and E40 = tax Now this formula works great if order is less than 10.01--brings back 3 as result. And if order is between 10.01 and 25 it brings back 4 as result. However, it does not work if amount is above 25.01---still brings back 4 as result when it should bring back 5. I would also like to continue IF statement in this cell to bring back results up to $100. Am I doing something wrong here? Why does formula work great up to a point, but no further?? Could someone please help. Thank you |
IF Statement
You have to refer to E39+E40 in every clause, e.g.
=IF(E39+E40<10.01,3,IF(E39+E40<25,4,IF(E39+E40<40, 5,6))) or you could try LOOKUP...with a table or hard-coded into the formula like this =LOOKUP(E39+E40,{0,10.01,25,40;3,4,5,6}) "Snake_Plisken" wrote: I have created an Excel Order form. After the Subtotal and Tax have been calculated, I want the form to automatically put in the handling charges. The IF Statement that I have used to format the cell is: =IF((E39+E40)<10.01,3,IF(10.01<25,4,IF(25.01<40,5) ))---where E39 = subtotal and E40 = tax Now this formula works great if order is less than 10.01--brings back 3 as result. And if order is between 10.01 and 25 it brings back 4 as result. However, it does not work if amount is above 25.01---still brings back 4 as result when it should bring back 5. I would also like to continue IF statement in this cell to bring back results up to $100. Am I doing something wrong here? Why does formula work great up to a point, but no further?? Could someone please help. Thank you |
IF Statement
Thanks. This seems to work. However, I can't go pass an 8th condition. Can I
get more into the cell or is this the limit. Ex. =IF((E39+E40)<10.01,3,IF(E39+E40<25,5,IF(E39+E40<4 0,6,IF(E39+E40<55,7,IF(E39+E40<70,8,IF(E39+E40<85, 9,IF(E39+E40<100,10,IF(E39+E40<150,12,IF(E39+E40<2 00,14,IF(E39+E40<300,15,)))))))))) Receive error at 9th condition...IF(E39+E40<200,14,IF(E39+E40<300,15,)) )))))))) Last , but not least, I would like every order that is over $300 to have handling charges equal to 7% of the order (subtot+tax). So would the final condition read.....IF(E39+E40300,*7% Would a LOOKUP statement help here? Again thanks for your help "Don Guillett" wrote: if(e39+e40<40,5,if(e39+e40<25,4,etc with ) per if -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... I have created an Excel Order form. After the Subtotal and Tax have been calculated, I want the form to automatically put in the handling charges. The IF Statement that I have used to format the cell is: =IF((E39+E40)<10.01,3,IF(10.01<25,4,IF(25.01<40,5) ))---where E39 = subtotal and E40 = tax Now this formula works great if order is less than 10.01--brings back 3 as result. And if order is between 10.01 and 25 it brings back 4 as result. However, it does not work if amount is above 25.01---still brings back 4 as result when it should bring back 5. I would also like to continue IF statement in this cell to bring back results up to $100. Am I doing something wrong here? Why does formula work great up to a point, but no further?? Could someone please help. Thank you |
IF Statement
Thanks. This seems to work. However, I can't go pass an 8th condition. Can I
get more into the cell or is this the limit. Ex. =IF((E39+E40)<10.01,3,IF(E39+E40<25,5,IF(E39+E40<4 0,6,IF(E39+E40<55,7,IF(E39+E40<70,8,IF(E39+E40<85, 9,IF(E39+E40<100,10,IF(E39+E40<150,12,IF(E39+E40<2 00,14,IF(E39+E40<300,15,)))))))))) Receive error at 9th condition...IF(E39+E40<200,14,IF(E39+E40<300,15,)) )))))))) Last , but not least, I would like every order that is over $300 to have handling charges equal to 7% of the order (subtot+tax). So would the final condition read.....IF(E39+E40300,*7% Would a LOOKUP statement help here? Again thanks for your help "daddylonglegs" wrote: You have to refer to E39+E40 in every clause, e.g. =IF(E39+E40<10.01,3,IF(E39+E40<25,4,IF(E39+E40<40, 5,6))) or you could try LOOKUP...with a table or hard-coded into the formula like this =LOOKUP(E39+E40,{0,10.01,25,40;3,4,5,6}) "Snake_Plisken" wrote: I have created an Excel Order form. After the Subtotal and Tax have been calculated, I want the form to automatically put in the handling charges. The IF Statement that I have used to format the cell is: =IF((E39+E40)<10.01,3,IF(10.01<25,4,IF(25.01<40,5) ))---where E39 = subtotal and E40 = tax Now this formula works great if order is less than 10.01--brings back 3 as result. And if order is between 10.01 and 25 it brings back 4 as result. However, it does not work if amount is above 25.01---still brings back 4 as result when it should bring back 5. I would also like to continue IF statement in this cell to bring back results up to $100. Am I doing something wrong here? Why does formula work great up to a point, but no further?? Could someone please help. Thank you |
IF Statement
Use the lookup suggestion
-- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... Thanks. This seems to work. However, I can't go pass an 8th condition. Can I get more into the cell or is this the limit. Ex. =IF((E39+E40)<10.01,3,IF(E39+E40<25,5,IF(E39+E40<4 0,6,IF(E39+E40<55,7,IF(E39+E40<70,8,IF(E39+E40<85, 9,IF(E39+E40<100,10,IF(E39+E40<150,12,IF(E39+E40<2 00,14,IF(E39+E40<300,15,)))))))))) Receive error at 9th condition...IF(E39+E40<200,14,IF(E39+E40<300,15,)) )))))))) Last , but not least, I would like every order that is over $300 to have handling charges equal to 7% of the order (subtot+tax). So would the final condition read.....IF(E39+E40300,*7% Would a LOOKUP statement help here? Again thanks for your help "Don Guillett" wrote: if(e39+e40<40,5,if(e39+e40<25,4,etc with ) per if -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... I have created an Excel Order form. After the Subtotal and Tax have been calculated, I want the form to automatically put in the handling charges. The IF Statement that I have used to format the cell is: =IF((E39+E40)<10.01,3,IF(10.01<25,4,IF(25.01<40,5) ))---where E39 = subtotal and E40 = tax Now this formula works great if order is less than 10.01--brings back 3 as result. And if order is between 10.01 and 25 it brings back 4 as result. However, it does not work if amount is above 25.01---still brings back 4 as result when it should bring back 5. I would also like to continue IF statement in this cell to bring back results up to $100. Am I doing something wrong here? Why does formula work great up to a point, but no further?? Could someone please help. Thank you |
IF Statement
LOOKUP works perfectly. Again though I need to resolve the issue of orders
over $300. I need the result to be 7% of total. I tried to use multiplication at the end of string (......,*7%) and also (........,*1.7) neither worked. Any suggestions? Thanks "Don Guillett" wrote: Use the lookup suggestion -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... Thanks. This seems to work. However, I can't go pass an 8th condition. Can I get more into the cell or is this the limit. Ex. =IF((E39+E40)<10.01,3,IF(E39+E40<25,5,IF(E39+E40<4 0,6,IF(E39+E40<55,7,IF(E39+E40<70,8,IF(E39+E40<85, 9,IF(E39+E40<100,10,IF(E39+E40<150,12,IF(E39+E40<2 00,14,IF(E39+E40<300,15,)))))))))) Receive error at 9th condition...IF(E39+E40<200,14,IF(E39+E40<300,15,)) )))))))) Last , but not least, I would like every order that is over $300 to have handling charges equal to 7% of the order (subtot+tax). So would the final condition read.....IF(E39+E40300,*7% Would a LOOKUP statement help here? Again thanks for your help "Don Guillett" wrote: if(e39+e40<40,5,if(e39+e40<25,4,etc with ) per if -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... I have created an Excel Order form. After the Subtotal and Tax have been calculated, I want the form to automatically put in the handling charges. The IF Statement that I have used to format the cell is: =IF((E39+E40)<10.01,3,IF(10.01<25,4,IF(25.01<40,5) ))---where E39 = subtotal and E40 = tax Now this formula works great if order is less than 10.01--brings back 3 as result. And if order is between 10.01 and 25 it brings back 4 as result. However, it does not work if amount is above 25.01---still brings back 4 as result when it should bring back 5. I would also like to continue IF statement in this cell to bring back results up to $100. Am I doing something wrong here? Why does formula work great up to a point, but no further?? Could someone please help. Thank you |
IF Statement
Just *insert* the actual formula you want to use:
Instead of: IF(E39+E40300,*7% USE: IF(E39+E40300,(E39+E40)*7% -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Snake_Plisken" wrote in message ... LOOKUP works perfectly. Again though I need to resolve the issue of orders over $300. I need the result to be 7% of total. I tried to use multiplication at the end of string (......,*7%) and also (........,*1.7) neither worked. Any suggestions? Thanks "Don Guillett" wrote: Use the lookup suggestion -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... Thanks. This seems to work. However, I can't go pass an 8th condition. Can I get more into the cell or is this the limit. Ex. =IF((E39+E40)<10.01,3,IF(E39+E40<25,5,IF(E39+E40<4 0,6,IF(E39+E40<55,7,IF(E39+E40<70,8,IF(E39+E40<85, 9,IF(E39+E40<100,10,IF(E39+E40<150,12,IF(E39+E40<2 00,14,IF(E39+E40<300,15,)))))))))) Receive error at 9th condition...IF(E39+E40<200,14,IF(E39+E40<300,15,)) )))))))) Last , but not least, I would like every order that is over $300 to have handling charges equal to 7% of the order (subtot+tax). So would the final condition read.....IF(E39+E40300,*7% Would a LOOKUP statement help here? Again thanks for your help "Don Guillett" wrote: if(e39+e40<40,5,if(e39+e40<25,4,etc with ) per if -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... I have created an Excel Order form. After the Subtotal and Tax have been calculated, I want the form to automatically put in the handling charges. The IF Statement that I have used to format the cell is: =IF((E39+E40)<10.01,3,IF(10.01<25,4,IF(25.01<40,5) ))---where E39 = subtotal and E40 = tax Now this formula works great if order is less than 10.01--brings back 3 as result. And if order is between 10.01 and 25 it brings back 4 as result. However, it does not work if amount is above 25.01---still brings back 4 as result when it should bring back 5. I would also like to continue IF statement in this cell to bring back results up to $100. Am I doing something wrong here? Why does formula work great up to a point, but no further?? Could someone please help. Thank you |
IF Statement
Tried this RagDyeR. There is a syntax error.
Here is my formula: =LOOKUP(E39+E40,{0,10.01,25.01,40.01,55.01,70.01,8 5.01,100.01,150.01,200.01,300.01;3,5,6,7,8,9,10,11 ,13,14,IF(E39+E40300,(E39+E40)*7%})) Can I place an IF statement within a LOOKUP? After the error, Excel highlights the *7%. "RagDyeR" wrote: Just *insert* the actual formula you want to use: Instead of: IF(E39+E40300,*7% USE: IF(E39+E40300,(E39+E40)*7% -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Snake_Plisken" wrote in message ... LOOKUP works perfectly. Again though I need to resolve the issue of orders over $300. I need the result to be 7% of total. I tried to use multiplication at the end of string (......,*7%) and also (........,*1.7) neither worked. Any suggestions? Thanks "Don Guillett" wrote: Use the lookup suggestion -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... Thanks. This seems to work. However, I can't go pass an 8th condition. Can I get more into the cell or is this the limit. Ex. =IF((E39+E40)<10.01,3,IF(E39+E40<25,5,IF(E39+E40<4 0,6,IF(E39+E40<55,7,IF(E39+E40<70,8,IF(E39+E40<85, 9,IF(E39+E40<100,10,IF(E39+E40<150,12,IF(E39+E40<2 00,14,IF(E39+E40<300,15,)))))))))) Receive error at 9th condition...IF(E39+E40<200,14,IF(E39+E40<300,15,)) )))))))) Last , but not least, I would like every order that is over $300 to have handling charges equal to 7% of the order (subtot+tax). So would the final condition read.....IF(E39+E40300,*7% Would a LOOKUP statement help here? Again thanks for your help "Don Guillett" wrote: if(e39+e40<40,5,if(e39+e40<25,4,etc with ) per if -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... I have created an Excel Order form. After the Subtotal and Tax have been calculated, I want the form to automatically put in the handling charges. The IF Statement that I have used to format the cell is: =IF((E39+E40)<10.01,3,IF(10.01<25,4,IF(25.01<40,5) ))---where E39 = subtotal and E40 = tax Now this formula works great if order is less than 10.01--brings back 3 as result. And if order is between 10.01 and 25 it brings back 4 as result. However, it does not work if amount is above 25.01---still brings back 4 as result when it should bring back 5. I would also like to continue IF statement in this cell to bring back results up to $100. Am I doing something wrong here? Why does formula work great up to a point, but no further?? Could someone please help. Thank you |
IF Statement
You've a couple of things wrong.
Your array is uneven (lookup vector = 11, result vector = 10). I added 0 at the beginning of the result vector. The array constant curly brackets should enclose *only* the array. You have it including the rest of the formula, out to the 7%. You didn't really need the "IF()", so I replaced it with a boolean argument. See if this works for you: =LOOKUP(E39+E40,{0,10.01,25.01,40.01,55.01,70.01,8 5.01,100.01,150.01,200.01,300.01;0,3,5,6,7,8,9,10, 11,13,14})+(E39+E40300)*((E39+E40)*7%) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Snake_Plisken" wrote in message ... Tried this RagDyeR. There is a syntax error. Here is my formula: =LOOKUP(E39+E40,{0,10.01,25.01,40.01,55.01,70.01,8 5.01,100.01,150.01,200.01,300.01;3,5,6,7,8,9,10,11 ,13,14,IF(E39+E40300,(E39+E40)*7%})) Can I place an IF statement within a LOOKUP? After the error, Excel highlights the *7%. "RagDyeR" wrote: Just *insert* the actual formula you want to use: Instead of: IF(E39+E40300,*7% USE: IF(E39+E40300,(E39+E40)*7% -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Snake_Plisken" wrote in message ... LOOKUP works perfectly. Again though I need to resolve the issue of orders over $300. I need the result to be 7% of total. I tried to use multiplication at the end of string (......,*7%) and also (........,*1.7) neither worked. Any suggestions? Thanks "Don Guillett" wrote: Use the lookup suggestion -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... Thanks. This seems to work. However, I can't go pass an 8th condition. Can I get more into the cell or is this the limit. Ex. =IF((E39+E40)<10.01,3,IF(E39+E40<25,5,IF(E39+E40<4 0,6,IF(E39+E40<55,7,IF(E39+E40<70,8,IF(E39+E40<85, 9,IF(E39+E40<100,10,IF(E39+E40<150,12,IF(E39+E40<2 00,14,IF(E39+E40<300,15,)))))))))) Receive error at 9th condition...IF(E39+E40<200,14,IF(E39+E40<300,15,)) )))))))) Last , but not least, I would like every order that is over $300 to have handling charges equal to 7% of the order (subtot+tax). So would the final condition read.....IF(E39+E40300,*7% Would a LOOKUP statement help here? Again thanks for your help "Don Guillett" wrote: if(e39+e40<40,5,if(e39+e40<25,4,etc with ) per if -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... I have created an Excel Order form. After the Subtotal and Tax have been calculated, I want the form to automatically put in the handling charges. The IF Statement that I have used to format the cell is: =IF((E39+E40)<10.01,3,IF(10.01<25,4,IF(25.01<40,5) ))---where E39 = subtotal and E40 = tax Now this formula works great if order is less than 10.01--brings back 3 as result. And if order is between 10.01 and 25 it brings back 4 as result. However, it does not work if amount is above 25.01---still brings back 4 as result when it should bring back 5. I would also like to continue IF statement in this cell to bring back results up to $100. Am I doing something wrong here? Why does formula work great up to a point, but no further?? Could someone please help. Thank you |
IF Statement
Thanks RagDyeR, this works.
"RagDyeR" wrote: You've a couple of things wrong. Your array is uneven (lookup vector = 11, result vector = 10). I added 0 at the beginning of the result vector. The array constant curly brackets should enclose *only* the array. You have it including the rest of the formula, out to the 7%. You didn't really need the "IF()", so I replaced it with a boolean argument. See if this works for you: =LOOKUP(E39+E40,{0,10.01,25.01,40.01,55.01,70.01,8 5.01,100.01,150.01,200.01,300.01;0,3,5,6,7,8,9,10, 11,13,14})+(E39+E40300)*((E39+E40)*7%) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Snake_Plisken" wrote in message ... Tried this RagDyeR. There is a syntax error. Here is my formula: =LOOKUP(E39+E40,{0,10.01,25.01,40.01,55.01,70.01,8 5.01,100.01,150.01,200.01,300.01;3,5,6,7,8,9,10,11 ,13,14,IF(E39+E40300,(E39+E40)*7%})) Can I place an IF statement within a LOOKUP? After the error, Excel highlights the *7%. "RagDyeR" wrote: Just *insert* the actual formula you want to use: Instead of: IF(E39+E40300,*7% USE: IF(E39+E40300,(E39+E40)*7% -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Snake_Plisken" wrote in message ... LOOKUP works perfectly. Again though I need to resolve the issue of orders over $300. I need the result to be 7% of total. I tried to use multiplication at the end of string (......,*7%) and also (........,*1.7) neither worked. Any suggestions? Thanks "Don Guillett" wrote: Use the lookup suggestion -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... Thanks. This seems to work. However, I can't go pass an 8th condition. Can I get more into the cell or is this the limit. Ex. =IF((E39+E40)<10.01,3,IF(E39+E40<25,5,IF(E39+E40<4 0,6,IF(E39+E40<55,7,IF(E39+E40<70,8,IF(E39+E40<85, 9,IF(E39+E40<100,10,IF(E39+E40<150,12,IF(E39+E40<2 00,14,IF(E39+E40<300,15,)))))))))) Receive error at 9th condition...IF(E39+E40<200,14,IF(E39+E40<300,15,)) )))))))) Last , but not least, I would like every order that is over $300 to have handling charges equal to 7% of the order (subtot+tax). So would the final condition read.....IF(E39+E40300,*7% Would a LOOKUP statement help here? Again thanks for your help "Don Guillett" wrote: if(e39+e40<40,5,if(e39+e40<25,4,etc with ) per if -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... I have created an Excel Order form. After the Subtotal and Tax have been calculated, I want the form to automatically put in the handling charges. The IF Statement that I have used to format the cell is: =IF((E39+E40)<10.01,3,IF(10.01<25,4,IF(25.01<40,5) ))---where E39 = subtotal and E40 = tax Now this formula works great if order is less than 10.01--brings back 3 as result. And if order is between 10.01 and 25 it brings back 4 as result. However, it does not work if amount is above 25.01---still brings back 4 as result when it should bring back 5. I would also like to continue IF statement in this cell to bring back results up to $100. Am I doing something wrong here? Why does formula work great up to a point, but no further?? Could someone please help. Thank you |
IF Statement
You're welcome, and appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Snake_Plisken" wrote in message ... Thanks RagDyeR, this works. "RagDyeR" wrote: You've a couple of things wrong. Your array is uneven (lookup vector = 11, result vector = 10). I added 0 at the beginning of the result vector. The array constant curly brackets should enclose *only* the array. You have it including the rest of the formula, out to the 7%. You didn't really need the "IF()", so I replaced it with a boolean argument. See if this works for you: =LOOKUP(E39+E40,{0,10.01,25.01,40.01,55.01,70.01,8 5.01,100.01,150.01,200.01, 300.01;0,3,5,6,7,8,9,10,11,13,14})+(E39+E40300)*( (E39+E40)*7%) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Snake_Plisken" wrote in message ... Tried this RagDyeR. There is a syntax error. Here is my formula: =LOOKUP(E39+E40,{0,10.01,25.01,40.01,55.01,70.01,8 5.01,100.01,150.01,200.01, 300.01;3,5,6,7,8,9,10,11,13,14,IF(E39+E40300,(E39 +E40)*7%})) Can I place an IF statement within a LOOKUP? After the error, Excel highlights the *7%. "RagDyeR" wrote: Just *insert* the actual formula you want to use: Instead of: IF(E39+E40300,*7% USE: IF(E39+E40300,(E39+E40)*7% -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Snake_Plisken" wrote in message ... LOOKUP works perfectly. Again though I need to resolve the issue of orders over $300. I need the result to be 7% of total. I tried to use multiplication at the end of string (......,*7%) and also (........,*1.7) neither worked. Any suggestions? Thanks "Don Guillett" wrote: Use the lookup suggestion -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... Thanks. This seems to work. However, I can't go pass an 8th condition. Can I get more into the cell or is this the limit. Ex. =IF((E39+E40)<10.01,3,IF(E39+E40<25,5,IF(E39+E40<4 0,6,IF(E39+E40<55,7,IF(E39 +E40<70,8,IF(E39+E40<85,9,IF(E39+E40<100,10,IF(E39 +E40<150,12,IF(E39+E40<200 ,14,IF(E39+E40<300,15,)))))))))) Receive error at 9th condition...IF(E39+E40<200,14,IF(E39+E40<300,15,)) )))))))) Last , but not least, I would like every order that is over $300 to have handling charges equal to 7% of the order (subtot+tax). So would the final condition read.....IF(E39+E40300,*7% Would a LOOKUP statement help here? Again thanks for your help "Don Guillett" wrote: if(e39+e40<40,5,if(e39+e40<25,4,etc with ) per if -- Don Guillett Microsoft MVP Excel SalesAid Software "Snake_Plisken" wrote in message ... I have created an Excel Order form. After the Subtotal and Tax have been calculated, I want the form to automatically put in the handling charges. The IF Statement that I have used to format the cell is: =IF((E39+E40)<10.01,3,IF(10.01<25,4,IF(25.01<40,5) ))---where E39 = subtotal and E40 = tax Now this formula works great if order is less than 10.01--brings back 3 as result. And if order is between 10.01 and 25 it brings back 4 as result. However, it does not work if amount is above 25.01---still brings back 4 as result when it should bring back 5. I would also like to continue IF statement in this cell to bring back results up to $100. Am I doing something wrong here? Why does formula work great up to a point, but no further?? Could someone please help. Thank you |
| All times are GMT +1. The time now is 09:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com