Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1278687%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1278687%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20sure%20this%20will%20be%20quite%20elementary%20for%20some%20of%20you%2C%20but%20I'm%20getting%20hung%20up%20trying%20to%20write%20a%20formula%20that%20I%20believe%20is%20an%20'if'%20'and'%20statement%2C%20but%20I%20cannot%20get%20it%20to%20work.%20Below%20I've%20written%20out%20the%204%20statements%2C%20but%20I'm%20struggling%20with%20the%20'and'%20side%20of%20it%20(I've%20added%20spaces%20to%20make%20it%20a%20little%20easier%20to%20read%20here)%3A%3C%2FP%3E%3CP%3EIf%20C15%20%3D%20%E2%80%98renewal%E2%80%99%20and%20D15%20%26lt%3B%3D12%3B%20E15%20%3D%20a15*.25%3C%2FP%3E%3CP%3EIf%20C15%20%3D%20%E2%80%98renewal%E2%80%99%20and%20D15%20%26gt%3B12%20and%20%26lt%3B%3D%2017%3B%20E15%3Da15%2Fd15%20*%2012%20*.25%3C%2FP%3E%3CP%3EIf%20C15%20%3D%20%E2%80%98renewal%E2%80%99%20and%20D15%20%26gt%3B17%20and%20%26lt%3B%3D35%3B%20E15%3Da15%2Fd15%20*%2012%20*.50%3C%2FP%3E%3CP%3EIf%20C15%20%3D%20%E2%80%98renewal%E2%80%99%20and%20D15%20%26gt%3B35%3B%20E15%20%3D%20a15%2Fd15%20*12%20*.75%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20write%20this%20into%20one%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1278687%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1278787%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1278787%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F608012%22%20target%3D%22_blank%22%3E%40wavalandone%3C%2FA%3E%26nbsp%3BWhat%20happens%20if%20C15%20isn't%20equal%20to%20renewal%3F%26nbsp%3B%20In%20this%20formula%20I%20have%20just%20set%20the%20formula%20to%200%20in%20that%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(C15%3D%22renewal%22%20%2CIF(D15%26lt%3B%3D12%2C%20A15*0.25%2C%20IF(D15%26lt%3B%3D17%2C%20A15%2FD15*12*0.25%2C%20IF(D15%26lt%3B%3D35%2C%20A15%2FD15*12*0.5%2C%20A15%2FD15*0.75)))%2C%200)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1278823%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1278823%22%20slang%3D%22en-US%22%3E%3CP%3ESavia%2C%3CBR%20%2F%3EThanks%20for%20the%20response%20and%20good%20point%2C%20I%20should%20have%20mentioned%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20big%20picture%20that%20I'm%20trying%20to%20accomplish%20is%20to%20have%203%20different%20options%20in%20C15%3A%20Renewal%2C%20New%20or%20Old.%20I%20was%20hoping%20that%20if%20I%20can%20get%20the%20assistance%20on%20'renewal'%20then%20I%20can%20figure%20out%20how%20to%20adapt%20it%20for%20the%20other%202.%20Decent%20chance%20I'll%20struggle%20with%20it%2C%20but%20I%20like%20the%20challenge%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1278874%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1278874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F608012%22%20target%3D%22_blank%22%3E%40wavalandone%3C%2FA%3E%26nbsp%3BMine%20relies%20on%20nesting%20IF%20inside%20one%20another%20in%20the%20right%20order%2C%20which%20is%20reasonably%20straightforward%20but%20long-winded.%26nbsp%3B%20If%20you%20have%20even%20more%20options%2C%20I'd%20suggest%20maybe%20looking%20at%20some%20options%20for%20reducing%20the%20formula%20complexity%20-%20such%20as%20calculating%20some%20of%20the%20stages%20of%20the%20formula%20over%20several%20columns%2C%20or%20reworking%20to%20use%20a%20newer%20function%20such%20as%20IFS.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1279315%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1279315%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20stuck-%20I'm%20not%20sure%20how%20to%20describe%2Fwrite%20the%20formula%20to%20do%20one%20thing%20is%20written%20in%20a%20cell%2C%20but%20another%20thing%20entirely%20if%20something%20else%20is%20written.%20Below%20are%20the%20three%20formulas%20(you%20created%20one%20and%20then%20I%20modified%20it%20to%20make%20it%20work%20for%20the%20other%20ones).%20Each%20of%20these%20work%20on%20their%20own%2C%20but%20how%20do%20I%20combine%20these%20into%20one%3F%20Or%20am%20I%20approaching%20this%20in%20the%20wrong%20way%3F%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(C15%3D%22renewal%22%2CIF(D15%26lt%3B%3D12%2C%20A15*0.25%2C%20IF(D15%26lt%3B%3D17%2C%20A15%2FD15*12*0.25%2C%20IF(D15%26lt%3B%3D35%2CA15%2FD15*12*0.5%2C%20A15%2FD15*12*0.75)))%2C%200)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(C15%3D%22New%22%2CIF(D15%26lt%3B%3D12%2C%20A15*0.80%2C%20IF(D15%26lt%3B%3D17%2C%20A15%2FD15*12*0.80%2C%20IF(D15%26lt%3B%3D35%2CA15%2FD15*12*1.0%2C%20A15%2FD15*12*1.1)))%2C%200)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(C15%3D%22Old%22%2CIF(D15%3D0%2CA15*1.22)%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1279414%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1279414%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F608012%22%20target%3D%22_blank%22%3E%40wavalandone%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20straightforward%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(C15%3D%22renewal%22%2C%0A%20%20%20IF(D15%26lt%3B%3D12%2C%0A%20%20%20%20%20%20A15*0.25%2C%0A%20%20%20%20%20%20IF(D15%26lt%3B%3D17%2C%0A%20%20%20%20%20%20%20%20%20A15%2FD15*12*0.25%2C%0A%20%20%20%20%20%20%20%20%20IF(D15%26lt%3B%3D35%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20A15%2FD15*12*0.5%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20A15%2FD15*12*0.75%0A%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20)%0A%20%20%20)%2C%0A%20%20%20IF(C15%3D%22New%22%2C%0A%20%20%20%20%20%20IF(D15%26lt%3B%3D12%2C%0A%20%20%20%20%20%20%20%20%20A15*0.80%2C%0A%20%20%20%20%20%20%20%20%20IF(D15%26lt%3B%3D17%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20A15%2FD15*12*0.80%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20IF(D15%26lt%3B%3D35%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20A15%2FD15*12*1.0%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20A15%2FD15*12*1.1%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20)%2C%0A%20%20%20IF(C15%3D%22Old%22%2C%0A%20%20%20%20%20%20IF(D15%3D0%2C%0A%20%20%20%20%20%20%20%20%20A15*1.22%2C%0A%20%20%20%20%20%20%20%20%200)%2C%0A%20%20%20%20%20%200%0A)))%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1279701%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1279701%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThank%20you%20Sergei%2C%20works%20perfectly!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281485%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281485%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F608012%22%20target%3D%22_blank%22%3E%40wavalandone%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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!

7 Replies
Highlighted

@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

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

@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

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

 

 

Highlighted

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

 

Highlighted

@Sergei BaklanThank you Sergei, works perfectly!

Highlighted

@wavalandone , you are welcome