|
IF then statements-Tiered PRicing
Please help!
I am going to have to create an Excel spread using tiered pricing. I imagine it will be: IF QUANTITY PRICE <100 10.00 100 5.00 Please help-I have no idea how to use an "IF" statement to make this work and this is what my boss wants. Thanks! |
IF then statements-Tiered PRicing
=IF(X<100,10,IF(X100,5))
You don't specify what happens if X = 100. Dave -- Brevity is the soul of wit. "Jackiec21" wrote: Please help! I am going to have to create an Excel spread using tiered pricing. I imagine it will be: IF QUANTITY PRICE <100 10.00 100 5.00 Please help-I have no idea how to use an "IF" statement to make this work and this is what my boss wants. Thanks! |
IF then statements-Tiered PRicing
Thank you for your quick response. If X=100, then price is $8.00, so how do
I need to enter that one?? "Dave F" wrote: =IF(X<100,10,IF(X100,5)) You don't specify what happens if X = 100. Dave -- Brevity is the soul of wit. "Jackiec21" wrote: Please help! I am going to have to create an Excel spread using tiered pricing. I imagine it will be: IF QUANTITY PRICE <100 10.00 100 5.00 Please help-I have no idea how to use an "IF" statement to make this work and this is what my boss wants. Thanks! |
IF then statements-Tiered PRicing
=IF(X<100,10,IF(X=100,8,IF(X100,5)))
Dave -- Brevity is the soul of wit. "Jackiec21" wrote: Thank you for your quick response. If X=100, then price is $8.00, so how do I need to enter that one?? "Dave F" wrote: =IF(X<100,10,IF(X100,5)) You don't specify what happens if X = 100. Dave -- Brevity is the soul of wit. "Jackiec21" wrote: Please help! I am going to have to create an Excel spread using tiered pricing. I imagine it will be: IF QUANTITY PRICE <100 10.00 100 5.00 Please help-I have no idea how to use an "IF" statement to make this work and this is what my boss wants. Thanks! |
IF then statements-Tiered PRicing
What I am trying to do is say-if a customer orders 95 widgits, then the price
is $10.00. If the customer orders 105 widgits (or some other number) than the price is $5.00. I am still not clear on how to get a price to calculate, as I received a "False" answer. Thanks for any additional help! "Dave F" wrote: =IF(X<100,10,IF(X100,5)) You don't specify what happens if X = 100. Dave -- Brevity is the soul of wit. "Jackiec21" wrote: Please help! I am going to have to create an Excel spread using tiered pricing. I imagine it will be: IF QUANTITY PRICE <100 10.00 100 5.00 Please help-I have no idea how to use an "IF" statement to make this work and this is what my boss wants. Thanks! |
IF then statements-Tiered PRicing
=IF(X<100,10,IF(X=100,8,5))
"Jackiec21" wrote: Thank you for your quick response. If X=100, then price is $8.00, so how do I need to enter that one?? "Dave F" wrote: =IF(X<100,10,IF(X100,5)) You don't specify what happens if X = 100. Dave -- Brevity is the soul of wit. "Jackiec21" wrote: Please help! I am going to have to create an Excel spread using tiered pricing. I imagine it will be: IF QUANTITY PRICE <100 10.00 100 5.00 Please help-I have no idea how to use an "IF" statement to make this work and this is what my boss wants. Thanks! |
IF then statements-Tiered PRicing
Assume A1 has the number of widgets ordered.
Then: =IF(A1=95,10,IF(A1=105,5)) If you want to avoid the FALSE value, you have to either specify what your ELSE clause is, which you haven't done, or, otherwise, suppress errors, as in: =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is 95, THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string." Dave -- Brevity is the soul of wit. "Jackiec21" wrote: What I am trying to do is say-if a customer orders 95 widgits, then the price is $10.00. If the customer orders 105 widgits (or some other number) than the price is $5.00. I am still not clear on how to get a price to calculate, as I received a "False" answer. Thanks for any additional help! "Dave F" wrote: =IF(X<100,10,IF(X100,5)) You don't specify what happens if X = 100. Dave -- Brevity is the soul of wit. "Jackiec21" wrote: Please help! I am going to have to create an Excel spread using tiered pricing. I imagine it will be: IF QUANTITY PRICE <100 10.00 100 5.00 Please help-I have no idea how to use an "IF" statement to make this work and this is what my boss wants. Thanks! |
IF then statements-Tiered PRicing
Assume A1 has the number of widgets ordered, then to calculate price in (say)
B1: =IF(A1<100,10,IF(A1=100,8,5)) HTH "Dave F" wrote: Assume A1 has the number of widgets ordered. Then: =IF(A1=95,10,IF(A1=105,5)) If you want to avoid the FALSE value, you have to either specify what your ELSE clause is, which you haven't done, or, otherwise, suppress errors, as in: =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is 95, THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string." Dave -- Brevity is the soul of wit. "Jackiec21" wrote: What I am trying to do is say-if a customer orders 95 widgits, then the price is $10.00. If the customer orders 105 widgits (or some other number) than the price is $5.00. I am still not clear on how to get a price to calculate, as I received a "False" answer. Thanks for any additional help! "Dave F" wrote: =IF(X<100,10,IF(X100,5)) You don't specify what happens if X = 100. Dave -- Brevity is the soul of wit. "Jackiec21" wrote: Please help! I am going to have to create an Excel spread using tiered pricing. I imagine it will be: IF QUANTITY PRICE <100 10.00 100 5.00 Please help-I have no idea how to use an "IF" statement to make this work and this is what my boss wants. Thanks! |
IF then statements-Tiered PRicing
Yeah, that defines the ELSE clause.
-- Brevity is the soul of wit. "Toppers" wrote: Assume A1 has the number of widgets ordered, then to calculate price in (say) B1: =IF(A1<100,10,IF(A1=100,8,5)) HTH "Dave F" wrote: Assume A1 has the number of widgets ordered. Then: =IF(A1=95,10,IF(A1=105,5)) If you want to avoid the FALSE value, you have to either specify what your ELSE clause is, which you haven't done, or, otherwise, suppress errors, as in: =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is 95, THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string." Dave -- Brevity is the soul of wit. "Jackiec21" wrote: What I am trying to do is say-if a customer orders 95 widgits, then the price is $10.00. If the customer orders 105 widgits (or some other number) than the price is $5.00. I am still not clear on how to get a price to calculate, as I received a "False" answer. Thanks for any additional help! "Dave F" wrote: =IF(X<100,10,IF(X100,5)) You don't specify what happens if X = 100. Dave -- Brevity is the soul of wit. "Jackiec21" wrote: Please help! I am going to have to create an Excel spread using tiered pricing. I imagine it will be: IF QUANTITY PRICE <100 10.00 100 5.00 Please help-I have no idea how to use an "IF" statement to make this work and this is what my boss wants. Thanks! |
IF then statements-Tiered PRicing
Assume A1 has the number of widgets ordered ......
I like what you said ...... *ordered* is the keyword. If the user enters a negative number to A1 to indicate the number of widgets *returned*, say -200, then the pricing will be off. Yes, the poster did say "order." I just want to highlight the fact that there shouldn't be any negative numbers in A1. Epinn "Toppers" wrote in message ... Assume A1 has the number of widgets ordered, then to calculate price in (say) B1: =IF(A1<100,10,IF(A1=100,8,5)) HTH "Dave F" wrote: Assume A1 has the number of widgets ordered. Then: =IF(A1=95,10,IF(A1=105,5)) If you want to avoid the FALSE value, you have to either specify what your ELSE clause is, which you haven't done, or, otherwise, suppress errors, as in: =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is 95, THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string." Dave -- Brevity is the soul of wit. "Jackiec21" wrote: What I am trying to do is say-if a customer orders 95 widgits, then the price is $10.00. If the customer orders 105 widgits (or some other number) than the price is $5.00. I am still not clear on how to get a price to calculate, as I received a "False" answer. Thanks for any additional help! "Dave F" wrote: =IF(X<100,10,IF(X100,5)) You don't specify what happens if X = 100. Dave -- Brevity is the soul of wit. "Jackiec21" wrote: Please help! I am going to have to create an Excel spread using tiered pricing. I imagine it will be: IF QUANTITY PRICE <100 10.00 100 5.00 Please help-I have no idea how to use an "IF" statement to make this work and this is what my boss wants. Thanks! |
IF then statements-Tiered PRicing
=AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5
-- Kevin James. Tua'r Goleuni "Epinn" wrote in message ... Assume A1 has the number of widgets ordered ...... I like what you said ...... *ordered* is the keyword. If the user enters a negative number to A1 to indicate the number of widgets *returned*, say -200, then the pricing will be off. Yes, the poster did say "order." I just want to highlight the fact that there shouldn't be any negative numbers in A1. Epinn "Toppers" wrote in message ... Assume A1 has the number of widgets ordered, then to calculate price in (say) B1: =IF(A1<100,10,IF(A1=100,8,5)) HTH "Dave F" wrote: Assume A1 has the number of widgets ordered. Then: =IF(A1=95,10,IF(A1=105,5)) If you want to avoid the FALSE value, you have to either specify what your ELSE clause is, which you haven't done, or, otherwise, suppress errors, as in: =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is 95, THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string." Dave -- Brevity is the soul of wit. "Jackiec21" wrote: What I am trying to do is say-if a customer orders 95 widgits, then the price is $10.00. If the customer orders 105 widgits (or some other number) than the price is $5.00. I am still not clear on how to get a price to calculate, as I received a "False" answer. Thanks for any additional help! "Dave F" wrote: =IF(X<100,10,IF(X100,5)) You don't specify what happens if X = 100. Dave -- Brevity is the soul of wit. "Jackiec21" wrote: Please help! I am going to have to create an Excel spread using tiered pricing. I imagine it will be: IF QUANTITY PRICE <100 10.00 100 5.00 Please help-I have no idea how to use an "IF" statement to make this work and this is what my boss wants. Thanks! |
IF then statements-Tiered PRicing
Hi Kevin
Tua'r Goleuni Towards the light Did you by any chance attend Cardiff High School? -- Regards Roger Govier "Kevin James" wrote in message ... =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5 -- Kevin James. Tua'r Goleuni "Epinn" wrote in message ... Assume A1 has the number of widgets ordered ...... I like what you said ...... *ordered* is the keyword. If the user enters a negative number to A1 to indicate the number of widgets *returned*, say -200, then the pricing will be off. Yes, the poster did say "order." I just want to highlight the fact that there shouldn't be any negative numbers in A1. Epinn "Toppers" wrote in message ... Assume A1 has the number of widgets ordered, then to calculate price in (say) B1: =IF(A1<100,10,IF(A1=100,8,5)) HTH "Dave F" wrote: Assume A1 has the number of widgets ordered. Then: =IF(A1=95,10,IF(A1=105,5)) If you want to avoid the FALSE value, you have to either specify what your ELSE clause is, which you haven't done, or, otherwise, suppress errors, as in: =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is 95, THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string." Dave -- Brevity is the soul of wit. "Jackiec21" wrote: What I am trying to do is say-if a customer orders 95 widgits, then the price is $10.00. If the customer orders 105 widgits (or some other number) than the price is $5.00. I am still not clear on how to get a price to calculate, as I received a "False" answer. Thanks for any additional help! "Dave F" wrote: =IF(X<100,10,IF(X100,5)) You don't specify what happens if X = 100. Dave -- Brevity is the soul of wit. "Jackiec21" wrote: Please help! I am going to have to create an Excel spread using tiered pricing. I imagine it will be: IF QUANTITY PRICE <100 10.00 100 5.00 Please help-I have no idea how to use an "IF" statement to make this work and this is what my boss wants. Thanks! |
IF then statements-Tiered PRicing
Er... No.
But there's a full explanation here, http://www.btinternet.com/~kevin.james1/Tuar.htm HTH, -- Kevin James. Tua'r Goleuni "Roger Govier" wrote in message ... | Hi Kevin | | Tua'r Goleuni | | Towards the light | Did you by any chance attend Cardiff High School? | | -- | Regards | | Roger Govier | | | "Kevin James" wrote in message | ... | =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5 | | -- | Kevin James. | Tua'r Goleuni | | | "Epinn" wrote in message | ... | Assume A1 has the number of widgets ordered ...... | | I like what you said ...... *ordered* is the keyword. | | If the user enters a negative number to A1 to indicate the number of | widgets | *returned*, say -200, then the pricing will be off. | | Yes, the poster did say "order." I just want to highlight the fact | that there | shouldn't be any negative numbers in A1. | | Epinn | | "Toppers" wrote in message | ... | Assume A1 has the number of widgets ordered, then to calculate price | in (say) | B1: | | =IF(A1<100,10,IF(A1=100,8,5)) | | HTH | | | "Dave F" wrote: | | Assume A1 has the number of widgets ordered. | | Then: | | =IF(A1=95,10,IF(A1=105,5)) | | If you want to avoid the FALSE value, you have to either specify what | your | ELSE clause is, which you haven't done, or, otherwise, suppress | errors, as | in: | | =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is | 95, | THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string." | | Dave | -- | Brevity is the soul of wit. | | | "Jackiec21" wrote: | | What I am trying to do is say-if a customer orders 95 widgits, then | the | price | is $10.00. If the customer orders 105 widgits (or some other | number) than | the price is $5.00. I am still not clear on how to get a price to | calculate, | as I received a "False" answer. Thanks for any additional help! | | "Dave F" wrote: | | =IF(X<100,10,IF(X100,5)) | | You don't specify what happens if X = 100. | | Dave | -- | Brevity is the soul of wit. | | | "Jackiec21" wrote: | | Please help! | | I am going to have to create an Excel spread using tiered | pricing. I | imagine it will be: | | IF QUANTITY PRICE | <100 10.00 | 100 5.00 | | Please help-I have no idea how to use an "IF" statement to make | this | work | and this is what my boss wants. Thanks! | | | | | |
IF then statements-Tiered PRicing
Hi Kevin
Thank you for that. Very interesting information. Being an old boy (well, a very old boy<g) of Cardiff High School, I "saw the light" as that was also our school motto. No reason of course why we should have had a monopoly it its use and I'm delighted to know that Bargoed Grammar used it as well. -- Regards Roger Govier "Kevin James" wrote in message ... Er... No. But there's a full explanation here, http://www.btinternet.com/~kevin.james1/Tuar.htm HTH, -- Kevin James. Tua'r Goleuni "Roger Govier" wrote in message ... | Hi Kevin | | Tua'r Goleuni | | Towards the light | Did you by any chance attend Cardiff High School? | | -- | Regards | | Roger Govier | | | "Kevin James" wrote in message | ... | =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5 | | -- | Kevin James. | Tua'r Goleuni | | | "Epinn" wrote in message | ... | Assume A1 has the number of widgets ordered ...... | | I like what you said ...... *ordered* is the keyword. | | If the user enters a negative number to A1 to indicate the number of | widgets | *returned*, say -200, then the pricing will be off. | | Yes, the poster did say "order." I just want to highlight the fact | that there | shouldn't be any negative numbers in A1. | | Epinn | | "Toppers" wrote in message | ... | Assume A1 has the number of widgets ordered, then to calculate price | in (say) | B1: | | =IF(A1<100,10,IF(A1=100,8,5)) | | HTH | | | "Dave F" wrote: | | Assume A1 has the number of widgets ordered. | | Then: | | =IF(A1=95,10,IF(A1=105,5)) | | If you want to avoid the FALSE value, you have to either specify what | your | ELSE clause is, which you haven't done, or, otherwise, suppress | errors, as | in: | | =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is | 95, | THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string." | | Dave | -- | Brevity is the soul of wit. | | | "Jackiec21" wrote: | | What I am trying to do is say-if a customer orders 95 widgits, then | the | price | is $10.00. If the customer orders 105 widgits (or some other | number) than | the price is $5.00. I am still not clear on how to get a price to | calculate, | as I received a "False" answer. Thanks for any additional help! | | "Dave F" wrote: | | =IF(X<100,10,IF(X100,5)) | | You don't specify what happens if X = 100. | | Dave | -- | Brevity is the soul of wit. | | | "Jackiec21" wrote: | | Please help! | | I am going to have to create an Excel spread using tiered | pricing. I | imagine it will be: | | IF QUANTITY PRICE | <100 10.00 | 100 5.00 | | Please help-I have no idea how to use an "IF" statement to make | this | work | and this is what my boss wants. Thanks! | | | | | |
IF then statements-Tiered PRicing
Hi Roger,
I was hoping that you found your long lost classmate. I wish I know how to pronounce "Tua'r Goleuni." I have never attended schools in the U.K., but I once had a Welsh teacher. << (well, a very old boy<g) I won't ask how old you are when I didn't tell you my g _ _ _ _ _. <bg But, let me say this, people who "tolerate" me well are usually over 50. If you are younger than 50, please don't feel offended. It's a compliment. Now, on to my main reason to post. I want to let you and Kevin know that I like this formula. To borrow Bob's words, "what little gem!" =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5 Roger, I think I can see "+" as "or" *without* the presence of SUMPRODUCT. Your lecture on Boolean is beginning to see some results. Bob, are you here? I also learn that if B6 is <0, 0 will be displayed because FALSE+FALSE+FALSE = 0+0+0=0. We didn't have to say "if <0,=0" etc. Okay, I am one big step forward. Thank you for your attention. Epinn "Roger Govier" wrote in message ... Hi Kevin Thank you for that. Very interesting information. Being an old boy (well, a very old boy<g) of Cardiff High School, I "saw the light" as that was also our school motto. No reason of course why we should have had a monopoly it its use and I'm delighted to know that Bargoed Grammar used it as well. -- Regards Roger Govier "Kevin James" wrote in message ... Er... No. But there's a full explanation here, http://www.btinternet.com/~kevin.james1/Tuar.htm HTH, -- Kevin James. Tua'r Goleuni "Roger Govier" wrote in message ... | Hi Kevin | | Tua'r Goleuni | | Towards the light | Did you by any chance attend Cardiff High School? | | -- | Regards | | Roger Govier | | | "Kevin James" wrote in message | ... | =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5 | | -- | Kevin James. | Tua'r Goleuni | | | "Epinn" wrote in message | ... | Assume A1 has the number of widgets ordered ...... | | I like what you said ...... *ordered* is the keyword. | | If the user enters a negative number to A1 to indicate the number of | widgets | *returned*, say -200, then the pricing will be off. | | Yes, the poster did say "order." I just want to highlight the fact | that there | shouldn't be any negative numbers in A1. | | Epinn | | "Toppers" wrote in message | ... | Assume A1 has the number of widgets ordered, then to calculate price | in (say) | B1: | | =IF(A1<100,10,IF(A1=100,8,5)) | | HTH | | | "Dave F" wrote: | | Assume A1 has the number of widgets ordered. | | Then: | | =IF(A1=95,10,IF(A1=105,5)) | | If you want to avoid the FALSE value, you have to either specify what | your | ELSE clause is, which you haven't done, or, otherwise, suppress | errors, as | in: | | =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is | 95, | THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string." | | Dave | -- | Brevity is the soul of wit. | | | "Jackiec21" wrote: | | What I am trying to do is say-if a customer orders 95 widgits, then | the | price | is $10.00. If the customer orders 105 widgits (or some other | number) than | the price is $5.00. I am still not clear on how to get a price to | calculate, | as I received a "False" answer. Thanks for any additional help! | | "Dave F" wrote: | | =IF(X<100,10,IF(X100,5)) | | You don't specify what happens if X = 100. | | Dave | -- | Brevity is the soul of wit. | | | "Jackiec21" wrote: | | Please help! | | I am going to have to create an Excel spread using tiered | pricing. I | imagine it will be: | | IF QUANTITY PRICE | <100 10.00 | 100 5.00 | | Please help-I have no idea how to use an "IF" statement to make | this | work | and this is what my boss wants. Thanks! | | | | | |
IF then statements-Tiered PRicing
Hi Epinn
Your initial comment concerned the formula posted by Toppers =IF(A1<100,10,IF(A1=100,8,5)) and concerned If the user enters a negative number to A1 to indicate the number of widgets *returned*, say -200, then the pricing will be off. ....... I just want to highlight the fact that there shouldn't be any negative numbers in A1. Kevin's formula (adjusted to relate to the same cell A1) =AND(A1<100,A10)*10+(A1=100)*8+(A1100)*5 addresses the issue of negative numbers being entered, and has the merit of returning 0, as opposed to 10 if cell A1 is empty. It could be that the original task of the OP was to deal with creating a Credit, if Widgets were returned, in which case =IF(ABS(A1)<100,10,IF(ABS(A1)=100,8,5))*SIGN(A1) will deal with negative quantities and produce the same value per widget as per the original order, and will return 0 if cell A1 is empty. -- Regards Roger Govier "Epinn" wrote in message ... Hi Roger, I was hoping that you found your long lost classmate. I wish I know how to pronounce "Tua'r Goleuni." I have never attended schools in the U.K., but I once had a Welsh teacher. << (well, a very old boy<g) I won't ask how old you are when I didn't tell you my g _ _ _ _ _. <bg But, let me say this, people who "tolerate" me well are usually over 50. If you are younger than 50, please don't feel offended. It's a compliment. Now, on to my main reason to post. I want to let you and Kevin know that I like this formula. To borrow Bob's words, "what little gem!" =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5 Roger, I think I can see "+" as "or" *without* the presence of SUMPRODUCT. Your lecture on Boolean is beginning to see some results. Bob, are you here? I also learn that if B6 is <0, 0 will be displayed because FALSE+FALSE+FALSE = 0+0+0=0. We didn't have to say "if <0,=0" etc. Okay, I am one big step forward. Thank you for your attention. Epinn "Roger Govier" wrote in message ... Hi Kevin Thank you for that. Very interesting information. Being an old boy (well, a very old boy<g) of Cardiff High School, I "saw the light" as that was also our school motto. No reason of course why we should have had a monopoly it its use and I'm delighted to know that Bargoed Grammar used it as well. -- Regards Roger Govier "Kevin James" wrote in message ... Er... No. But there's a full explanation here, http://www.btinternet.com/~kevin.james1/Tuar.htm HTH, -- Kevin James. Tua'r Goleuni "Roger Govier" wrote in message ... | Hi Kevin | | Tua'r Goleuni | | Towards the light | Did you by any chance attend Cardiff High School? | | -- | Regards | | Roger Govier | | | "Kevin James" wrote in message | ... | =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5 | | -- | Kevin James. | Tua'r Goleuni | | | "Epinn" wrote in message | ... | Assume A1 has the number of widgets ordered ...... | | I like what you said ...... *ordered* is the keyword. | | If the user enters a negative number to A1 to indicate the number of | widgets | *returned*, say -200, then the pricing will be off. | | Yes, the poster did say "order." I just want to highlight the fact | that there | shouldn't be any negative numbers in A1. | | Epinn | | "Toppers" wrote in message | ... | Assume A1 has the number of widgets ordered, then to calculate price | in (say) | B1: | | =IF(A1<100,10,IF(A1=100,8,5)) | | HTH | | | "Dave F" wrote: | | Assume A1 has the number of widgets ordered. | | Then: | | =IF(A1=95,10,IF(A1=105,5)) | | If you want to avoid the FALSE value, you have to either specify what | your | ELSE clause is, which you haven't done, or, otherwise, suppress | errors, as | in: | | =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is | 95, | THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string." | | Dave | -- | Brevity is the soul of wit. | | | "Jackiec21" wrote: | | What I am trying to do is say-if a customer orders 95 widgits, then | the | price | is $10.00. If the customer orders 105 widgits (or some other | number) than | the price is $5.00. I am still not clear on how to get a price to | calculate, | as I received a "False" answer. Thanks for any additional help! | | "Dave F" wrote: | | =IF(X<100,10,IF(X100,5)) | | You don't specify what happens if X = 100. | | Dave | -- | Brevity is the soul of wit. | | | "Jackiec21" wrote: | | Please help! | | I am going to have to create an Excel spread using tiered | pricing. I | imagine it will be: | | IF QUANTITY PRICE | <100 10.00 | 100 5.00 | | Please help-I have no idea how to use an "IF" statement to make | this | work | and this is what my boss wants. Thanks! | | | | | |
IF then statements-Tiered Pricing
Roger,
"That's admirable!" I am totally satisfied now as the formula is perfect and practical meaning it can take care of returned products. SIGN( ) is quite handy. I was so concerned with negative numbers and I didn't notice an empty cell was a problem too. Hope the OP is still around. Epinn "Roger Govier" wrote in message ... Hi Epinn Your initial comment concerned the formula posted by Toppers =IF(A1<100,10,IF(A1=100,8,5)) and concerned If the user enters a negative number to A1 to indicate the number of widgets *returned*, say -200, then the pricing will be off. ....... I just want to highlight the fact that there shouldn't be any negative numbers in A1. Kevin's formula (adjusted to relate to the same cell A1) =AND(A1<100,A10)*10+(A1=100)*8+(A1100)*5 addresses the issue of negative numbers being entered, and has the merit of returning 0, as opposed to 10 if cell A1 is empty. It could be that the original task of the OP was to deal with creating a Credit, if Widgets were returned, in which case =IF(ABS(A1)<100,10,IF(ABS(A1)=100,8,5))*SIGN(A1) will deal with negative quantities and produce the same value per widget as per the original order, and will return 0 if cell A1 is empty. -- Regards Roger Govier "Epinn" wrote in message ... Hi Roger, I was hoping that you found your long lost classmate. I wish I know how to pronounce "Tua'r Goleuni." I have never attended schools in the U.K., but I once had a Welsh teacher. << (well, a very old boy<g) I won't ask how old you are when I didn't tell you my g _ _ _ _ _. <bg But, let me say this, people who "tolerate" me well are usually over 50. If you are younger than 50, please don't feel offended. It's a compliment. Now, on to my main reason to post. I want to let you and Kevin know that I like this formula. To borrow Bob's words, "what little gem!" =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5 Roger, I think I can see "+" as "or" *without* the presence of SUMPRODUCT. Your lecture on Boolean is beginning to see some results. Bob, are you here? I also learn that if B6 is <0, 0 will be displayed because FALSE+FALSE+FALSE = 0+0+0=0. We didn't have to say "if <0,=0" etc. Okay, I am one big step forward. Thank you for your attention. Epinn "Roger Govier" wrote in message ... Hi Kevin Thank you for that. Very interesting information. Being an old boy (well, a very old boy<g) of Cardiff High School, I "saw the light" as that was also our school motto. No reason of course why we should have had a monopoly it its use and I'm delighted to know that Bargoed Grammar used it as well. -- Regards Roger Govier "Kevin James" wrote in message ... Er... No. But there's a full explanation here, http://www.btinternet.com/~kevin.james1/Tuar.htm HTH, -- Kevin James. Tua'r Goleuni "Roger Govier" wrote in message ... | Hi Kevin | | Tua'r Goleuni | | Towards the light | Did you by any chance attend Cardiff High School? | | -- | Regards | | Roger Govier | | | "Kevin James" wrote in message | ... | =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5 | | -- | Kevin James. | Tua'r Goleuni | | | "Epinn" wrote in message | ... | Assume A1 has the number of widgets ordered ...... | | I like what you said ...... *ordered* is the keyword. | | If the user enters a negative number to A1 to indicate the number of | widgets | *returned*, say -200, then the pricing will be off. | | Yes, the poster did say "order." I just want to highlight the fact | that there | shouldn't be any negative numbers in A1. | | Epinn | | "Toppers" wrote in message | ... | Assume A1 has the number of widgets ordered, then to calculate price | in (say) | B1: | | =IF(A1<100,10,IF(A1=100,8,5)) | | HTH | | | "Dave F" wrote: | | Assume A1 has the number of widgets ordered. | | Then: | | =IF(A1=95,10,IF(A1=105,5)) | | If you want to avoid the FALSE value, you have to either specify what | your | ELSE clause is, which you haven't done, or, otherwise, suppress | errors, as | in: | | =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is | 95, | THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string." | | Dave | -- | Brevity is the soul of wit. | | | "Jackiec21" wrote: | | What I am trying to do is say-if a customer orders 95 widgits, then | the | price | is $10.00. If the customer orders 105 widgits (or some other | number) than | the price is $5.00. I am still not clear on how to get a price to | calculate, | as I received a "False" answer. Thanks for any additional help! | | "Dave F" wrote: | | =IF(X<100,10,IF(X100,5)) | | You don't specify what happens if X = 100. | | Dave | -- | Brevity is the soul of wit. | | | "Jackiec21" wrote: | | Please help! | | I am going to have to create an Excel spread using tiered | pricing. I | imagine it will be: | | IF QUANTITY PRICE | <100 10.00 | 100 5.00 | | Please help-I have no idea how to use an "IF" statement to make | this | work | and this is what my boss wants. Thanks! | | | | | |
| All times are GMT +1. The time now is 09:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com