Adding one more variable to an IF statement Formula

    I have this and it works, but i want to add one more variable.


    if a number 1 is entered in E5 then leave the formula alone to generate the number, but if a 2 is entered in E5 then run the fromula*2 - run the formula and times it by 2


    Code
    =IF(E3=1,A7+5,IF(E3=2,A7*2+5,IF(E3=3,A7*3+5,IF(E3=4,A7*4+5, IF(E3=5,A7*5+5,IF(E3=6,A7*6+5, IF(E3=7,A7*7+5, IF(E3=8,A7*8+5,IF(E3=9,A7*9+5,IF(E3=10,A7*10+5, IF(E3=11,A7*11+5, IF(E3=12,A7*12+5, IF(E3=13,A7*13+5, IF(E3=14,A7*14+5, IF(E3=15,A7*15+5, IF(E3=16,A7*16+5, IF(E3=17,A7*17+5, IF(E3=18,A7*18+5, IF(E3=19,A7*19+5, IF(E3=20,A7*20+5)))))))))))))))))))

    Hi,


    To make things a lot easier to understand and to adjust, attaching a small sample file would greatly help ... :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi again,


    Should you need a compact solution with no list, then following Array formula could help :

    Code
     =INDEX(A7*ROW(INDIRECT("1:20"))+5,MATCH(E3,ROW(INDIRECT("1:20")),0))*E5

    Do not know which Excel version you are using ...


    But, prior Excel 365 / Excel 2021, you need to use simultaneously the three keys Control Shift Enter (instead of the standard Enter key)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Like 1

    Glad to hear you like it :thumbup:


    Thanks a lot for your Thanks AND for the Like 8)


    As far as decoding the formula is concerned, you can easily decipher it with these few steps :

    1. Place yourself in Edit Mode with F2

    2. Highlight a section of the formula, say, exactly this bit A7*ROW(INDIRECT("1:20"))+5

    3. Hit F9 to see the evaluation, which is the underlying calculation performed by Excel

    4. Hit Esc to escape this mode altogether


    Hope this will help :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login