Sep 25 2021 10:30 AM - edited Sep 25 2021 10:31 AM
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 list.
For example, if the dropdown list = 100%
Cells: F3, G3, O3, P3, X3,Y3, AG3, AH3.. have to have certain values else, stay as they are.
Hope I explained it clearly..
Thanks in advance
Sep 25 2021 10:36 AM
Sep 25 2021 10:58 AM
Sep 25 2021 11:24 AM
SolutionSep 25 2021 11:37 AM
Sep 25 2021 11:41 AM
Sep 25 2021 11:56 AM
Sep 25 2021 12:00 PM
Sep 25 2021 12:03 PM - edited Sep 25 2021 12:07 PM
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)
Sep 25 2021 12:08 PM - edited Sep 25 2021 12:09 PM
What {MatrixOfOptions} and 0 represents? Both formulas didnt work :\
Sep 25 2021 12:24 PM
Sep 25 2021 12:25 PM
Sep 25 2021 12:29 PM
it is not an error message per se. but for example a 75 from the dropdown list supposed to give 175, but in the picture it is 5
Sep 25 2021 12:40 PM
Sep 25 2021 01:53 PM
Sep 25 2021 07:31 PM
Sep 26 2021 06:57 PM
By using this formula:
=IF(N10=25%,AT3,IF(N10=50%,AQ3,IF(N10=75%,AN3,IF(N10=100%,0,'Transition matrix'!F3))))
the results are as follow:
they are calculated from 0.007
B1 | F1 | is this a right value? |
Empty | 0.00695 | wrong value should be 0.007 |
0 | 0.00695 | wrong value should be 0.007 |
25% | 0.00521 | right value |
50% | 0.00347 | right value |
75% | 0.00174 | right value |
100% | 0 | right value |
Sep 26 2021 07:01 PM
Sep 25 2021 11:24 AM
Solution