Forum Discussion

SARAH11's avatar
SARAH11
Brass Contributor
Sep 25, 2021
Solved

Nested IF function

Hello Genius people,  I need your help with nested IF function    I have 4 tables and a dropdown list, I want some of the numbers from the tables to change according to the choice of the dropdown ...
Formulas and Functions
  • amit_bhola's avatar
    amit_bhola
    Sep 25, 2021
    Suppose,
    value for ....is
    as it is= 5
    25% = 125
    50% = 150
    75% = 175
    100% = 200

    and the drop-down list is in cell A1, then the required formula could be

    =IF(A1=0.25,125,IF(A1=0.5,150,IF(A1=0.75,175,IF(A1=1,200,5))))

    Note that the "as it is" value (5) needs to be hard coded into the formula. And effectively it (5) shall be displayed only when the A1 cell is empty.

    Hope that that's what you intended and the formula works as such.
SARAH11's avatar
SARAH11
Brass Contributor
Sep 25, 2021
Thanks, Do I write down "Stay_As_It_is" ?!
SARAH11's avatar
SARAH11
Brass Contributor
Sep 25, 2021
I forgot to mention that I have 4 percentage: 100%, 75%, 50% and 25% in the dropdown list. every percent incur different cells numbers
  • Juliano-Petrukio's avatar
    Juliano-Petrukio
    Bronze Contributor
    Sep 25, 2021

    SARAH11 

    I know that IF formula is so captivating to start it, but it can become a nightmare when we want to nest multiple conditions.
    So I recommend you using the CHOOSE formula where you can amplify easily your conditions.

     

     

    =IFERROR(CHOOSE(MATCH(A1,{MatrixOfOptions},0),125,150,175,200),5)

     

    Translating it 

     

    =IFERROR(CHOOSE(MATCH(A1,{0.25,0.5,0.75,1},0),125,150,175,200),5)

     

     

  • amit_bhola's avatar
    amit_bhola
    Iron Contributor
    Sep 25, 2021
    Suppose,
    value for ....is
    as it is= 5
    25% = 125
    50% = 150
    75% = 175
    100% = 200

    and the drop-down list is in cell A1, then the required formula could be

    =IF(A1=0.25,125,IF(A1=0.5,150,IF(A1=0.75,175,IF(A1=1,200,5))))

    Note that the "as it is" value (5) needs to be hard coded into the formula. And effectively it (5) shall be displayed only when the A1 cell is empty.

    Hope that that's what you intended and the formula works as such.
    • SARAH11's avatar
      SARAH11
      Brass Contributor
      Sep 25, 2021
      Amit.. you are smart.. It worked perfectly..

      It would be extra awesome if you can help me with the following...

      lets assume that the probability for patients having surgery = 0.007
      and the probability of the new medication to lead patients to do the surgery is 75% less.. then will test if the probability is 50% and 25% less... Is it possible to provide me with the new probabilities of the different percentages?

      Thank you
      • SARAH11's avatar
        SARAH11
        Brass Contributor
        Sep 25, 2021
        I added 0 to the dropdown list to allow the value to stay as it is, but when I choose zero it gave me complete different number !.

        My formula is :

        =IF(N10=25%,AT3,IF(N10=50%,AQ3,IF(N10=75%,AN3,IF(N10=100%,0,'Transition matrix'!F3))))

Resources