Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex IF statement
I am working on a pricing model and I am having a challenge programming cell
$D$24. Currently it reads as follows however I now need to add another element to it. IF($E$24=0,(" "),IF(AND($E$240,$D$13=1,$D$13<=25),("$500"),IF( AND($E$240,$D$13=26,$D$13<=50),("$750"),IF(AND($ E$240,$D$13=51,$D$13<=150),("$1,500"),IF(AND($E$ 240,$D$13=151,$D$13<=250),("$2,000"),IF(AND($E$2 40,$D$13=251),("$5,000"),IF(AND($E$24=1,$D$13=0) ,("# of Vehicles MUST be 0")," "))))))) Cell $D$24 now varies based on cell $D$15. Cell $D$15 is a data validation drop down allowing the cell to either be blank or say "Affiliate". When "Affiliate" is selected, I need the pricing in cell $D$24 to reduce by 50%. For example, instead of $500, $750, $1,500, $2,000 and $5,000, I need it to be $250, $375, $750, $1,000 and $2,500. This occurs when $E$24=0, when $D$13 mirrors the information above and when $D$15="Affiliate" -- Cathy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex IF statement
one way:
=IF($E$24<=0,"",IF($D$13<1,"# of Vehicles MUST be 0", LOOKUP($D$13, {1,500; 26,750; 51,1500; 151,2000; 251,5000})/(1 + ($D$15="Affiliate"))) In article , cathy wrote: I am working on a pricing model and I am having a challenge programming cell $D$24. Currently it reads as follows however I now need to add another element to it. IF($E$24=0,(" "),IF(AND($E$240,$D$13=1,$D$13<=25),("$500"),IF( AND($E$240,$D$13=26,$D$13< =50),("$750"),IF(AND($E$240,$D$13=51,$D$13<=150) ,("$1,500"),IF(AND($E$240,$ D$13=151,$D$13<=250),("$2,000"),IF(AND($E$240,$D $13=251),("$5,000"),IF(AND( $E$24=1,$D$13=0),("# of Vehicles MUST be 0")," "))))))) Cell $D$24 now varies based on cell $D$15. Cell $D$15 is a data validation drop down allowing the cell to either be blank or say "Affiliate". When "Affiliate" is selected, I need the pricing in cell $D$24 to reduce by 50%. For example, instead of $500, $750, $1,500, $2,000 and $5,000, I need it to be $250, $375, $750, $1,000 and $2,500. This occurs when $E$24=0, when $D$13 mirrors the information above and when $D$15="Affiliate" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex IF statement
When using the formula suggested, I receive an error message (specifically
#NAME?) when I select cell $E$24 as "1" and cell $D$13 as "1" and $D$15 as blank. -- Cathy "JE McGimpsey" wrote: one way: =IF($E$24<=0,"",IF($D$13<1,"# of Vehicles MUST be 0", LOOKUP($D$13, {1,500; 26,750; 51,1500; 151,2000; 251,5000})/(1 + ($D$15="Affiliate"))) In article , cathy wrote: I am working on a pricing model and I am having a challenge programming cell $D$24. Currently it reads as follows however I now need to add another element to it. IF($E$24=0,(" "),IF(AND($E$240,$D$13=1,$D$13<=25),("$500"),IF( AND($E$240,$D$13=26,$D$13< =50),("$750"),IF(AND($E$240,$D$13=51,$D$13<=150) ,("$1,500"),IF(AND($E$240,$ D$13=151,$D$13<=250),("$2,000"),IF(AND($E$240,$D $13=251),("$5,000"),IF(AND( $E$24=1,$D$13=0),("# of Vehicles MUST be 0")," "))))))) Cell $D$24 now varies based on cell $D$15. Cell $D$15 is a data validation drop down allowing the cell to either be blank or say "Affiliate". When "Affiliate" is selected, I need the pricing in cell $D$24 to reduce by 50%. For example, instead of $500, $750, $1,500, $2,000 and $5,000, I need it to be $250, $375, $750, $1,000 and $2,500. This occurs when $E$24=0, when $D$13 mirrors the information above and when $D$15="Affiliate" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex IF statement
Hmm.. check your typing.
It's true that my copy/paste apparently left off the last closing paren (so it should be: =IF($E$24<=0,"",IF($D$13<1,"# of Vehicles MUST be 0", LOOKUP($D$13, {1,500;26,750;51,1500;151,2000;251,5000})/(1 + ($D$15="Affiliate")))) with four closing parens at the end), but there's nothing there that should give a #NAME? error... With D13: 1 E24: 1 (i.e., no quotes), D24 returns 500. In article , cathy wrote: When using the formula suggested, I receive an error message (specifically #NAME?) when I select cell $E$24 as "1" and cell $D$13 as "1" and $D$15 as blank. -- Cathy "JE McGimpsey" wrote: one way: =IF($E$24<=0,"",IF($D$13<1,"# of Vehicles MUST be 0", LOOKUP($D$13, {1,500; 26,750; 51,1500; 151,2000; 251,5000})/(1 + ($D$15="Affiliate"))) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex IF statement
It eliminated the error message. However, the prices remain the same whether
or not cell $D$15 is "Affiliate" or not. If $D$15 reads "Affiliate", I need cell $D$24's price to reduce by 50% of what it would normally be based on the number of affiliates selected in cell $D$13. Thanks. -- Cathy "JE McGimpsey" wrote: Hmm.. check your typing. It's true that my copy/paste apparently left off the last closing paren (so it should be: =IF($E$24<=0,"",IF($D$13<1,"# of Vehicles MUST be 0", LOOKUP($D$13, {1,500;26,750;51,1500;151,2000;251,5000})/(1 + ($D$15="Affiliate")))) with four closing parens at the end), but there's nothing there that should give a #NAME? error... With D13: 1 E24: 1 (i.e., no quotes), D24 returns 500. In article , cathy wrote: When using the formula suggested, I receive an error message (specifically #NAME?) when I select cell $E$24 as "1" and cell $D$13 as "1" and $D$15 as blank. -- Cathy "JE McGimpsey" wrote: one way: =IF($E$24<=0,"",IF($D$13<1,"# of Vehicles MUST be 0", LOOKUP($D$13, {1,500; 26,750; 51,1500; 151,2000; 251,5000})/(1 + ($D$15="Affiliate"))) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex IF statement
Does your Dropdown value in D15 have leading or trailing spaces? Is
"Affiliate" spelled correctly (or at least the same way), in both the formula and the dropdown? The (1 + ($D$15="Affiliate")) portion of the formula returns 1 if D15 does NOT contain "Affiliate", and 2 if it does, so if D15 actually contains "Affiliate", the result of the lookup will be divided in half. In article , cathy wrote: It eliminated the error message. However, the prices remain the same whether or not cell $D$15 is "Affiliate" or not. If $D$15 reads "Affiliate", I need cell $D$24's price to reduce by 50% of what it would normally be based on the number of affiliates selected in cell $D$13. Thanks. -- Cathy "JE McGimpsey" wrote: Hmm.. check your typing. It's true that my copy/paste apparently left off the last closing paren (so it should be: =IF($E$24<=0,"",IF($D$13<1,"# of Vehicles MUST be 0", LOOKUP($D$13, {1,500;26,750;51,1500;151,2000;251,5000})/(1 + ($D$15="Affiliate")))) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex IF statement
That was it! Perfect!!!! Thank you. I really enjoy programming - where can
I learn more about excel programming. I'd like to become more specialized. -- Cathy "JE McGimpsey" wrote: Does your Dropdown value in D15 have leading or trailing spaces? Is "Affiliate" spelled correctly (or at least the same way), in both the formula and the dropdown? The (1 + ($D$15="Affiliate")) portion of the formula returns 1 if D15 does NOT contain "Affiliate", and 2 if it does, so if D15 actually contains "Affiliate", the result of the lookup will be divided in half. In article , cathy wrote: It eliminated the error message. However, the prices remain the same whether or not cell $D$15 is "Affiliate" or not. If $D$15 reads "Affiliate", I need cell $D$24's price to reduce by 50% of what it would normally be based on the number of affiliates selected in cell $D$13. Thanks. -- Cathy "JE McGimpsey" wrote: Hmm.. check your typing. It's true that my copy/paste apparently left off the last closing paren (so it should be: =IF($E$24<=0,"",IF($D$13<1,"# of Vehicles MUST be 0", LOOKUP($D$13, {1,500;26,750;51,1500;151,2000;251,5000})/(1 + ($D$15="Affiliate")))) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex IF statement
Well, this hasn't been programming, but the
microsoft.public.excel.programming group is a good place to start, as is http://www.mvps.org/dmcritchie/excel/getstarted.htm In article , cathy wrote: That was it! Perfect!!!! Thank you. I really enjoy programming - where can I learn more about excel programming. I'd like to become more specialized. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex IF Statement | Excel Discussion (Misc queries) | |||
Complex IF THEN statement | Excel Worksheet Functions | |||
Complex If/And Statement | Excel Worksheet Functions | |||
complex IF statement | Excel Programming | |||
Help with complex If statement | Excel Worksheet Functions |