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

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"

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"

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

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

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