 SOLVED

Contributor

# Multiple IF Function

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.

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

41 Replies

# Re: Multiple IF Function

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.

# Re: Multiple IF Function

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.

# Re: Multiple IF Function

@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. Example sharing of what you are trying to do. Pls. make a table and send :- # Re: Multiple IF Function

 If Condition 1 If condition 2 Then Result True True Result true value of condition 2 True False Result true value of condition 1 False True Result  false value of condition 1 False False Result false value of condition 1
best response confirmed by SARAH11 (Contributor)
Solution

# Re: Multiple IF Function

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

# Re: Multiple IF Function

=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

# Re: Multiple IF Function

Perhaps

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

# Re: Multiple IF Function

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

# Re: Multiple IF Function

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

# Re: Multiple IF Function

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

# Re: Multiple IF Function

@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 Pattern 2 = 3 possible results = what Mr. Sergei shared # Re: Multiple IF Function

As a comment, last two options are the same, thus totally 3 results to return # Re: Multiple IF Function

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

# Re: Multiple IF Function

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.

# Re: Multiple IF Function

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

# Re: Multiple IF Function

I tried the formula didn't work

# Re: Multiple IF Function

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

# Re: Multiple IF Function

@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 # Re: Multiple IF Function

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.