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$24>0,$D$13>=1,$D$13<=25),("$500"),IF(AND($E$24>0,$D$13>=26,$D$13<=50),("$750"),IF(AND($E$24>0,$D$13>=51,$D$13<=150),("$1,500"),IF(AND($E$24>0,$D$13>=151,$D$13<=250),("$2,000"),IF(AND($E$24>0,$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 <2601A95C-0A95-4D45-B09C-(E-Mail Removed)>,
cathy <(E-Mail Removed)> 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$24>0,$D$13>=1,$D$13<=25),("$500"),IF(AND($E$24>0,$D$13>=26,$D$13<
> =50),("$750"),IF(AND($E$24>0,$D$13>=51,$D$13<=150),("$1,500"),IF(AND($E$24>0,$
> D$13>=151,$D$13<=250),("$2,000"),IF(AND($E$24>0,$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 <2601A95C-0A95-4D45-B09C-(E-Mail Removed)>,
> cathy <(E-Mail Removed)> 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$24>0,$D$13>=1,$D$13<=25),("$500"),IF(AND($E$24>0,$D$13>=26,$D$13<
> > =50),("$750"),IF(AND($E$24>0,$D$13>=51,$D$13<=150),("$1,500"),IF(AND($E$24>0,$
> > D$13>=151,$D$13<=250),("$2,000"),IF(AND($E$24>0,$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 <C88AC361-A2C5-4FB7-83BD-(E-Mail Removed)>,
cathy <(E-Mail Removed)> 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 <C88AC361-A2C5-4FB7-83BD-(E-Mail Removed)>,
> cathy <(E-Mail Removed)> 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 <5D8C43CB-3052-497C-8AFE-(E-Mail Removed)>,
cathy <(E-Mail Removed)> 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"))))

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 <5D8C43CB-3052-497C-8AFE-(E-Mail Removed)>,
> cathy <(E-Mail Removed)> 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"))))
>

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 <8FD9BDFD-0DCE-40E4-81E7-(E-Mail Removed)>,
cathy <(E-Mail Removed)> 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.