Complex IF statement

[cathy]

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"

 

[JE McGimpsey]

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

 

[cathy]

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.

 

[JE McGimpsey]

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.

 

[cathy]

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.

 

[JE McGimpsey]

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.

 

[cathy]

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.

 

[JE McGimpsey]

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