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

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"

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"

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")))

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")))

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"))))

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"))))

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.