Nov 01 2020 04:47 AM - edited Nov 01 2020 04:51 AM
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
Thank you
Nov 01 2020 05:12 AM
Nov 01 2020 05:20 AM
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.
Nov 01 2020 05:45 AM
@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 :-
Nov 01 2020 05:54 AM
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 |
Nov 01 2020 06:10 AM - edited Nov 01 2020 06:15 AM
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)))
Nov 01 2020 06:15 AM
=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
Nov 01 2020 06:16 AM
Nov 01 2020 06:19 AM
@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!
Nov 01 2020 06:20 AM
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
Nov 01 2020 06:23 AM
Nov 01 2020 06:31 AM
@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
Nov 01 2020 07:01 AM
Nov 01 2020 07:16 AM
@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)
Nov 01 2020 10:43 AM
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
Nov 01 2020 12:42 PM
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 ?
Nov 01 2020 05:46 PM
Nov 02 2020 09:22 AM - edited Nov 02 2020 09:23 AM
@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
Nov 02 2020 10:02 AM
@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)))