SOLVED

Multiple IF Function

Brass Contributor

Hello everyone

 

I have an urgent question that I couldn't find an answer to

I am trying to use 2 IF statements at the same time, each one contains if true value and if false value however the cells for true and false values are different 

 

All the formulas I found contains the true value only.

 

Your advice is much appreciated.

 

This is what I am trying to do: however, didn't work

122.png

 

 

 

Thank you

41 Replies
Using 2 or more IF functions together means that you need to write formula such that the 2nd IF formula written either in the TRUE result or the FALSE result of the 1st IF formula, ie, either at place for [value if true] or at the place of [value if false].
In screenshot shared, the 1st IF formula does not have the 2nd IF formula written at either of these 2 places (arguments).
Note that the formula inputs and results (arguments) should be separated by commas but a semicolon is visible after H30.

Nested IF should be like below:-

=IF(condition 1, result if condition 1 is true, IF(condition 2, result if condition 2 is true, result if condition 2 is false))

Pls try to form the formula again.

Thank you so much for your valuable time @amit_bhola 

 

However when I applied the formula you suggested it got messy as it used the first condition to give the second true result .  so when I changed the first condition from 1 to 0, it gave me the result when the second condition changed from 3 to 2. hope there is a solution for this obstacle. 

@SARAH11 , are you trying to write a formula like below table pattern? If so, then can you write the conditions and required results in yellow cells and send back. This kind of pattern requires use of AND formula inside IF formula.

 

amit_bhola_0-1604238209416.png

 

Example sharing of what you are trying to do. Pls. make a table and send :-

 

amit_bhola_1-1604238277428.png

 

@amit_bhola  

If Condition 1If condition 2Then Result
TrueTrueResult true value of condition 2
TrueFalseResult true value of condition 1
FalseTrueResult  false value of condition 1
FalseFalseResult false value of condition 1
best response confirmed by SARAH11 (Brass Contributor)
Solution

@SARAH11 , i think for the False False row, you meant "Result false value of condition 2"

 

Pls. see if below formula is giving the expected results or not.. and i hope you would catch how this formula is made with use of AND to check the 2 conditions simultaneously, and so if some adjustment of results is required, you can do..

 

 

=IF(AND(G20=0,J20=1),0.35,IF(AND(G20=0,NOT(J20=1)),F30/(F30+G30),IF(AND(NOT(G20=0),J20=1),H30,I30)))

 

 

@amit_bhola 

 

=IF(AND(G20=0,J20=1),0.25,IF(AND(G20=0,NOT(J20=1)),F30/(F30+G30),IF(AND(NOT(G20=0),J20=1),H30,I30)))

 

I made a slight change that is bold, otherwise it work yaaaaaay you are the hero of my day 

 

I cant thank you enough 

 

Million thanks to you I really appreciate your time and effort

@SARAH11 

Perhaps

=IF( G20=0,IF(J20=1,0.35,F30/(F30+H30)),H30)

@SARAH11 , yeah i noted later..  did you mean the bold text =  0.35 instead of 0.25 ? pls. check.. in screenshot, it was 0.35...  (it seems to be the typo day today but never mind...) Thanks!

@Sergei Baklan 

 

This also worked with a slight change 

 

=IF( G20=0,IF(J20=1,0.35,F30/(F30+G30)),H30)

 

Thank you so much appreciate it 

@amit_bhola 

 

dont worry about it its my mistake regardless I really thank you for your valuable help 

@SARAH11 , sorry to bother again, but since you confirmed it so i should clarify. Pls. take care that the formula shared by @Sergei Baklan is of different pattern. It gives 3 possible results instead of 4 possible results which you described by the table. In any case, now you know how to write formulas for both patterns.

 

(Your original query was somewhat difficult to understand the intended pattern)

 

Pattern 1 = 4 possible results (truth table) = what i shared

amit_bhola_1-1604240931690.png

 

Pattern 2 = 3 possible results = what Mr. Sergei shared

amit_bhola_0-1604240908168.png

 

@amit_bhola 

As a comment, last two options are the same, thus totally 3 results to return

image.png

@Sergei Baklan , i get that. From truth table it is 3 results but then i noticed that my 3 results suggestion of my 1st reply was dismissed and i also noticed that "I30" is one of the results (4th) in original query screenshot so later i went for the 4 results type formula..

 @SARAH11 , truly you should check if you need 3 possible outcomes or 4 possible outcomes and select the formula accordingly. I am concerned since only one of the patterns can be correct intent and so only one of the formulas can be correct. You can chose accordingly (perhaps you want 3 outcomes and "I30" is never an intended outcome, pls. check)

 

@amit_bhola , @Sergei Baklan 

 

Oh god could my life be more complicated??!!

I just realized that I need 2 choices in each cell as I have lower and upper estimate 

as the following formula will give a result of lower estimate when its equal to 0.25 

=IF(AND($G$20=0,$J$20=1),0.25,IF(AND($G$20=0,NOT($J$20=1)),G30/(G30+H30),IF(AND(NOT($G$20=0),$J$20=1),I30,J30)))

 

however I need to consider the upper estimate as well, I can't Put the upper and lower values in 2 cells as I am going to use the value cell in further analysis hence I need to make sure that I can apply both upper and lower estimate to one cell.

Please help I am about to give up <\3

@SARAH11

 

I had a  idea but not sure if its correct or not 

=IF(AND($G$20=0,$J$20=1 Or 2 ),0.25 Or 0.99,IF(AND($G$20=0,NOT($J$20=1 Or 2 )),G30/(G30+H30),IF(AND(NOT($G$20=0),$J$20=1 Or 2 ),I30,J30)))

1 = Lower estimate with value of 0.25

2= Upper estimate with value of 0.99 

 

Would that work correctly ?

I tried the formula didn't work :(
Below should work:-

=IF(AND($G$20=0,OR($J$20=1, $J$20=2)),IF($J$20=1,0.25,0.99),IF(AND($G$20=0,NOT(OR($J$20=1, $J$20=2))),G30/(G30+H30),IF(AND(NOT($G$20=0),OR($J$20=1, $J$20=2)),I30,J30)))

Also, pls check earlier posts 3 or 4 possible outcomes discussion. For the False False case do you want the result to be same as False True case or different? In simpler words, for False False case, do you want I30 as the result or J30? just check and put that in the end of the formula. (Currently J30 is written. I hope J30 is what you intend.)

 

@amit_bhola Thank you so much you are an Excel Genius  

I faced a problem though, when both are false instead of I20 it gives 0 don't know why 

 

123.png

@SARAH11 , 

For  FALSE-FALSE case , the output given in formula is the red font. It is currently written as J30. If you want that instead of J30, the FALSE-FALSE output should be I30, then replace the J30 with I30 in the formula. 

 

Instead of below

=IF(AND($G$20=0,OR($J$20=1, $J$20=2)),IF($J$20=1,0.25,0.99),IF(AND($G$20=0,NOT(OR($J$20=1, $J$20=2))),G30/(G30+H30),IF(AND(NOT($G$20=0),OR($J$20=1, $J$20=2)),I30,J30)))

 

Write below

=IF(AND($G$20=0,OR($J$20=1, $J$20=2)),IF($J$20=1,0.25,0.99),IF(AND($G$20=0,NOT(OR($J$20=1, $J$20=2))),G30/(G30+H30),IF(AND(NOT($G$20=0),OR($J$20=1, $J$20=2)),I30,I30)))

1 best response

Accepted Solutions
best response confirmed by SARAH11 (Brass Contributor)
Solution

@SARAH11 , i think for the False False row, you meant "Result false value of condition 2"

 

Pls. see if below formula is giving the expected results or not.. and i hope you would catch how this formula is made with use of AND to check the 2 conditions simultaneously, and so if some adjustment of results is required, you can do..

 

 

=IF(AND(G20=0,J20=1),0.35,IF(AND(G20=0,NOT(J20=1)),F30/(F30+G30),IF(AND(NOT(G20=0),J20=1),H30,I30)))

 

 

View solution in original post