Nested IF function

[SARAH11]

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

[#2]

Select F3, G3, O3, P3, X3,Y3, AG3, AH3... then apply the formula
=IF(CellWithTheValueFromDropDownList=100%,"PutTheValueYouWant","Stay_As_It_is")

[#3]

Thanks, Do I write down "Stay_As_It_is" ?!

[#4]

I forgot to mention that I have 4 percentage: 100%, 75%, 50% and 25% in the dropdown list. every percent incur different cells numbers

[#5 · Accepted solution]

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.

[#6]

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

[#7]

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

[#8]

As per the formula, for anything other than 25%,50%,75%,100% in cell N10, the output should be same as value in cell F3 of sheet Transition matrix. Is output not so? Are you sure the format of cell F3 of sheet Transition matrix is same as the formula cell? (And just checking, is the cell F3.. holding the as-it-is value that you desire to be outputted for zero at N10?)

[#9]

The output when the dropdown list is zero is a weird number.. not sure where it came from!

"And just checking, is the cell F3.. holding the as-it-is value that you desire to be outputted for zero at N10?" YES

[#10]

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

[#11]

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

[#12]

@SARAH11 

Find attachment

[#13]

Much appreciated, but even though it didnt work in the file you sent, do I need to activate/download something for CHOOSE function to work?

[#14]

Can you please show us the error? You can snapshot it here.

[#15]

@Juliano-Petrukio 

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

[#16]

You mean by saying 75 as 75%. It is a different number and different from the proposed solutions so far.
As you can see in the formula, the {MatrixOfOptions} means the different values that can be selected as per your desirable output range.
As I told you, 75% <> 75. If you really want to consider 75 you need to change from 0.75 to 75 on the {MatrixOfOptions}

As I can understand there is a pattern on your calculation that means you have a X_Value that will be increased by certain percentage value. =SUM(100*(1+(A2/100)))

[#17]

when I change the numbers in the dropdown list in the file you sent, the formula doesnt work, meaning it does not give any other number than 5.

[#18]

Pls. inform what output do you want in F1 for different values in B1 in form of a table. (See attached image.)
Pls. cover all possible options in B1 column that can be possible and take care to use (or not use) the % sign while filling B1.
Then it would be easy for someone to make a file with working formula and send you.

[#19]

@amit_bhola 

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

[#20]

N10 is the dropdown list


AT3= 0.00521 (25% result)
AQ3= 0.00347 (50% result)
AN3 = 0.00174 (75% result)


'Transition matrix'!F3= stay as it is value which supposed to be the value of 0 and empty