 Highlighted

# Formula Help

I'm sure this will be quite elementary for some of you, but I'm getting hung up trying to write a formula that I believe is an 'if' 'and' statement, but I cannot get it to work. Below I've written out the 4 statements, but I'm struggling with the 'and' side of it (I've added spaces to make it a little easier to read here):

If C15 = ‘renewal’ and D15 <=12; E15 = a15*.25

If C15 = ‘renewal’ and D15 >12 and <= 17; E15=a15/d15 * 12 *.25

If C15 = ‘renewal’ and D15 >17 and <=35; E15=a15/d15 * 12 *.50

If C15 = ‘renewal’ and D15 >35; E15 = a15/d15 *12 *.75

Is it possible to write this into one formula?

7 Replies
Highlighted

# Re: Formula Help

@wavalandone What happens if C15 isn't equal to renewal?  In this formula I have just set the formula to 0 in that case.

=IF(C15="renewal" ,IF(D15<=12, A15*0.25, IF(D15<=17, A15/D15*12*0.25, IF(D15<=35, A15/D15*12*0.5, A15/D15*0.75))), 0)

Highlighted

# Re: Formula Help

Savia,
Thanks for the response and good point, I should have mentioned that.

The big picture that I'm trying to accomplish is to have 3 different options in C15: Renewal, New or Old. I was hoping that if I can get the assistance on 'renewal' then I can figure out how to adapt it for the other 2. Decent chance I'll struggle with it, but I like the challenge

Highlighted

# Re: Formula Help

@wavalandone Mine relies on nesting IF inside one another in the right order, which is reasonably straightforward but long-winded.  If you have even more options, I'd suggest maybe looking at some options for reducing the formula complexity - such as calculating some of the stages of the formula over several columns, or reworking to use a newer function such as IFS.

Highlighted

# Re: Formula Help

I'm stuck- I'm not sure how to describe/write the formula to do one thing is written in a cell, but another thing entirely if something else is written. Below are the three formulas (you created one and then I modified it to make it work for the other ones). Each of these work on their own, but how do I combine these into one? Or am I approaching this in the wrong way?

=IF(C15="renewal",IF(D15<=12, A15*0.25, IF(D15<=17, A15/D15*12*0.25, IF(D15<=35,A15/D15*12*0.5, A15/D15*12*0.75))), 0)

=IF(C15="New",IF(D15<=12, A15*0.80, IF(D15<=17, A15/D15*12*0.80, IF(D15<=35,A15/D15*12*1.0, A15/D15*12*1.1))), 0)

=IF(C15="Old",IF(D15=0,A15*1.22),0)

Highlighted

# Re: Formula Help

If straightforward

``````=IF(C15="renewal",
IF(D15<=12,
A15*0.25,
IF(D15<=17,
A15/D15*12*0.25,
IF(D15<=35,
A15/D15*12*0.5,
A15/D15*12*0.75
)
)
),
IF(C15="New",
IF(D15<=12,
A15*0.80,
IF(D15<=17,
A15/D15*12*0.80,
IF(D15<=35,
A15/D15*12*1.0,
A15/D15*12*1.1
)
)
),
IF(C15="Old",
IF(D15=0,
A15*1.22,
0),
0
)))
``````

Highlighted

# Re: Formula Help

@Sergei BaklanThank you Sergei, works perfectly!

Highlighted

# Re: Formula Help

@wavalandone , you are welcome