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

 

 

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

    What {MatrixOfOptions} and 0 represents? Both formulas didnt work 😕

Resources