We help IT Professionals succeed at work.

Embedding the appropriate nested "If" statement

Bright01
Bright01 asked
on
EE Professionals,

I have a quick need for adding an embedded if statement (2nd level).  I have attached a graphic that shows what I'm trying to have the formula do.

Thank you in advance,

B.
Nested-If-Statement.png
Comment
Watch Question

CERTIFIED EXPERT

Commented:
=if(05="One time",N5, <...>)

Replace <...> with your previous formula.

Author

Commented:
ssaquibh,

The formula works.  However, I have one complication.  I have a macro that automatically updates the formula when a change occurs.  I get a Compile error that seems to be caused by the word "ONE" in this formula.

Range("R" & Target.Row).FormulaR1C1 = _
                   "=if(RC[-3]="One Time",RC[-4],if(RC[-4]=0,0,RC[-4]*(1+RC[-1])^(VLOOKUP(RC[-3],CA_Calc_Engine!R7C6:R15C8,3,0))*RC[-2]))"

Any ideas on what I'm doing wrong as I try to incorporate the formula in my macro?

B.
CERTIFIED EXPERT
Commented:
"=if(RC[-3]=""One Time"",RC[-4],if(RC[-4]=0,0,RC[-4]*(1+RC[-1])^(VLOOKUP(RC[-3],CA_Calc_Engine!R7C6:R15C8,3,0))*RC[-2]))"

Author

Commented:
Thank you ssaqubh!  Complicated WS and formula; but your formula and assistance provided the correct answer.

B.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.