Complex IF statement

C

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

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

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.
 
J

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.
 
C

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.
 
J

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.
 
C

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top