Forum Discussion
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)))
Regarding the matter of making the input of 2 sets of values easier (lower and upper), I would be able to work on it only after about 8-9 hours. I hope that is fine.
Thanks
amit_bhola Sure absolutely fine by me take your time
SARAH11 , My pleasure, Thanks for your wishes. Communication is two way and you were also patient with conversation. All the best!
amit_bhola Dear Amit,
I appreciate all your effort and everything you offered for the past few days.
Since separating them would be a hustle then 20 numbers on the dropdown list doesn't sound too bad.
I'm beyond thankful and grateful.
Wish you all the best in your life.
It was nice and easy communicating with you.
Thank you for being super helpful. and thank you for being a great and amazing human being.
SARAH11 , do you mean that you want to enter only lower choice values in J20 and only higher choice values in K20 ?
Well while technically it is possible to make a formula (either some IF formula or some LOOKUP formula) , but it would be a too complicated one to manage. And due to complicated, it could also be difficult to understand and manage in future. e.g. if you want to edit such a formula in future, you might get stuck with difficulty. Pls. think that in comparison to the inconvenience of selecting 1 of the 20 drop down values, which situation is more difficult (or should i say, desirable)?
I gather that the input-output table for your suggestion would be like below. Did i get it right? If not, then you can edit the table and send. Ideally, for possible outcomes beyond three - four numbers (count), one would write a lookup formula, not an IF formula.
To understand the LOOKUP formula, pls. search for VLOOKUP, or XLOOKUP in Excel help or internet. There may be some good videos which tell use of LOOKUP formulas.. They are complicated than IF formula but can deal with situations like below :-
G20 J20 K20 Required result 0 <blank> <blank> ??? Not 0 <blank> <blank> ??? 0 1 <blank> 0.25 0 999 <blank> i30 0 <blank> 2 0.99 0 <blank> 999 i30 0 1 2 ??? Not 0 1 <blank> i30 Not 0 999 <blank> i30 Not 0 <blank> 2 i30 Not 0 <blank> 999 i30 Not 0 1 2 i30 0 3 <blank> 0.22 0 999 <blank> i30 0 <blank> 4 0.88 0 <blank> 999 i30 0 3 4 ??? Not 0 3 <blank> i30 Not 0 999 <blank> i30 Not 0 <blank> 4 i30 Not 0 <blank> 999 i30 Not 0 3 4 i30