Multiple IF Function

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

amit_bhola Sure absolutely fine by me take your time

SARAH11 

When I tried the following formula it work Perfectly 

Hope it is a correct one !!

=IF(NOT(G20=0),I32,IF(OR(J20=3,J20=4),IF(J20=3,0.22,0.88),I32))

The bolded is the ones I changed from the original formula  ( regardless of cell name)

I want to make sure this is the right formula to work with as I have to do the same formula 10 times to 10 different parameters so are the numbers I changed are the only things to change every time I apply the formula to a parameter ( regardless of cell name)? 

Additionally to avoid 20 numbers on the dropdown list and as every parameter will have upper and lower values, hence I am thinking of  assigning J20 for lower values and K20 to upper values however both lower and upper values results' will be assign to a single cell.  Is that possible to do ?

as 1 & 3 (0.25, 0.22) will be the lower values, 2 & 4 (0.99, 0.88) the upper values.

If you have a better/ easier suggestion on how to handle/avoid 20 numbers on the dropdown list, I am all ears. 

Thank you

amit_bhola It did work completely  perfectly this time. 

Thank you so very much. your effort and time is much appreciated. 

Last question. If I want to change 1 & 2 to 3 ,4 

and change 0.25 & 0.99 to 0.22 , 0.88 

is the bolded numbers are the only thing to change in the formula (regardless of cell name)?

=IF(NOT(G20=0),I30,IF(OR(J20=1,J20=2),IF(J20=1,0.25,0.99),I30))

as when I tried it, it didn't work perfectly. I replaced 0.25 with 0.22 and 0.99 with 0.88 however when I tried to do the following it didn't give me the wanted result

SARAH11 , ok, below formula satisfies the table you mentioned but note that in none of the results 

is mentioned. Is the table right? (since earlier you were expecting 

as one of the possible outcomes...

Anyways, below formula satisfies the table you mentioned , pls. check

=IF(NOT(G20=0),I30,IF(OR(J20=1,J20=2),IF(J20=1,0.25,0.99),I30))

amit_bhola 

SARAH11 , don't worry, Let's go table way again.

Is below table OK?  If not, then send the correct required table :-

amit_bhola The problem with this formula is that it gave me 0.25 and 0.99 when G20=1 which is not correct 

yh true when G20 = 0 it gave me I30 but when J20 = 1 or 2 still gave me I30 instead it suppose to give me 0.25 for 1 

and 0.99 for 2 

SARAH11 , everywhere 30 became 29 as i happened to make this formula in row 29. I meant below :-

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

SARAH11 , pls. try this

=IF(AND(NOT($G$20=0),OR($J$20=1, $J$20=2)),IF($J$20=1,0.25,0.99),IF(AND(NOT($G$20=0),NOT(OR($J$20=1, $J$20=2))),G29/(G29+H29),IF(AND($G$20=0,OR($J$20=1, $J$20=2)),I29,I29)))

amit_bhola yes by model I meant when G20 =0 

"Formula will result in I30 when condition is FALSE, meaning when the G20 is not =0."

however this is not happening as when G30 is not = 0 it gave me a random number which keep changing every time I press f9 

the random number comes from H30  which is a part of I 30 formula 

to sum up:

when G20 = 0 

Q30 must = I30 

and must not generate random numbers.

SARAH11 , sorry but it is difficult to catch what do you mean by "model"  or the phrase "when the model is 0" .  Are you calling G20 = model ?

If so, then take care that, since you have specified first condition as

=IF(G20=0 ...

this condition will be TRUE when G20=0    and

this condition will be FALSE when G20 is not =0

Formula will result in I30 when condition is FALSE, meaning when the G20 is not =0.

In maths, 0 = FALSE , but since you specified the condition as =IF(G20=0 ... so it became a LOGIC. and Logic will be True if G20=0.

So,, when you say model is 0 , do you mean to say G20=0 or G20 is not = 0 ?

Regarding the random number issue... pls. see all the precedent cells on which the final formula is depending. If any of the precedent cells at any level contains a =RAND() or similar random number generating formula, then it will affect the whole chain of dependant cells.

amit_bhola yh I corrected that however when the model is deterministic which is 0 

the values  are not suppose to generate new numbers yet they do with the latest formula 

meaning when the model is 0 the value of Q30 must be equal to the value in I30 

SARAH11 , there seems to be something wrong with the formula in cell "I30", because the formula for "I30" itself contains "I30".  You are calculating I30 using I30 which seems unusual and can be the cause of the problem.

Pls. check if the "I30" cell contains the correct formula?

amit_bhola So sorry but these two cells must be equal when both are false 

and Q30 shouldn't generate random numbers (f9) when the model is 0 however in my case it dose hence the value change where it shouldn't 

SARAH11 , glad to know it works! Thanks and all the best!

amit_bhola Yeah now it worked perfectly,

You are a life safer. Cant thank you enough 

grateful and appreciate your time and effort . 

You are a very nice person wish you all the best .

The formula components work like below :-

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

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