Formula Help

[wavalandone]

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?

Thanks in advance!

[#2]

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

[#3]

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

[#4]

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

[#5]

@Savia 

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)

[#6]

@wavalandone 

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

[#7]

@Sergei BaklanThank you Sergei, works perfectly!

[#8]

@wavalandone , you are welcome