IF Function with some randomization!!!

[LukeB11]

This is my function =if(A1=1,"+",if(A1=2,"-")), this function would be in cell B2. Now the problem is if A1 is neither 1 or 2 then FALSE appears in cell B2. so how do i fix it so that FALSE won't appear in cell B2 if neither 1 or 2 is in cell A1.

Also in that same function I would like to add another option, for example: if A1=3 then i would like Excel to randomly choose between either the + or - sign to appear in cell B2. I've been stuck on this for awhile and any help would be appreciated!

 

[shg]

One way:

=IF(MEDIAN(1, A1, 3) <> A1, "", CHOOSE(A1,"+", "-", MID("+-", RANDBETWEEN(1,2), 1)))

 

[joeu2004]

This is my function =if(A1=1,"+",if(A1=2,"-")), this function would be in cell B2. Now the problem is if A1 is neither 1 or 2 then FALSE appears in cell B2. so how do i fix it so that FALSE won't appear in cell B2 if neither 1 or 2 is in cell A1.

To begin with, you have to specify what you do want to see in that case instead of FALSE. How about nothing?

=IF(A1=1,"+",IF(A1=2,"-",""))

The double double-quotes is the null string. It appears blank. But note: ISBLANK(B2) will return FALSE because ISBLANK is a misnomer. It should be called ISEMPTY (no constant and no formula).

Also in that same function I would like to add another option, for example: if A1=3 then i would like Excel to randomly choose between either the + or - sign to appear in cell B2.

=IF(A1=1,"+",IF(A1=2,"-",IF(A1=3,IF(RAND()<50%,"+","-"),"")))

That says: if A1=3, display "+" 50% of the time, otherwise "-". Change 50% to whatever probability you want.

But be careful what you wish for! If A1=3, note that B2 might change every time any cell in any worksheet is edited, as well as other times that causes a recalculation. (For example, deleting a worksheet; but not adding a worksheet.)

 

[LukeB11]

Ok but if I want to add two more symbols "x" and "/" then I would go like this =IF(A1=1,"+",IF(A1=2,"-",IF(A1=3,"x",IF(A1=4,"/",IF(A1=5,IF(RAND()<50%,"+","-"),"")))))
but how would I include the "x" and "/" to the RAND??

 

[joeu2004]

Ok but if I want to add two more symbols "x" and "/" then I would go like this =IF(A1=1,"+",IF(A1=2,"-",IF(A1=3,"x",IF(A1=4,"/",IF(A1=5,IF(RAND()<50%,"+","-"),"")))))
but how would I include the "x" and "/" to the RAND??

First, it would be ideal if you can depend on A1 having only certain "expected" values, let's say 0 (or empty) through 5. Then you can avoid the nested IFs as follows:

=CHOOSE(A1+1,"","+","-","x","/",LOOKUP(RAND(),{0,0.25,0.50,0.75},{"+","-","x","/"}))

Note the curly braces around the lookup lists 0,0.25,etc and "+","-",etc. They might be difficult to read with your font.

Change 0.25 (25%), 0.50 (50%) and 0.75 (75%) to the desired probability distribution. Note that we must write 0.25 instead of 25% between curly braces (an array constant).

If you cannot depend on A1 having the expected values, you might write:

=IF(OR(A1<1,A1>5),"",CHOOSE(A1,"+","-","x","/",LOOKUP(RAND(),{0,0.25,0.50,0.75},{"+","-","x","/"})))

Note the subtle change in the first two CHOOSE parameters.

 

[joeu2004]

If you cannot depend on A1 having the expected values, you might write:

=IF(OR(A1<1,A1>5),"",CHOOSE(A1,"+","-","x","/",LOOKUP(RAND(),{0,0.25,0.50,0.75},{"+","-","x","/"})))

Alternatively:

=IF(OR(A1<1,A1>5),"",MID("+-x/",IF(A1<5,A1,MATCH(RAND(),{0,0.25,0.50,0.75})),1))

It is easier to maintain since the list of operators is specified only once.

PS: If you wish, you can replace OR(A1<1,A1>5) with the MEDIAN(1,A1,5)<>A1 trick that shg demonstrated. But I think the OR() function is easier to understand and modify, if the need arises.

 

[LukeB11]

Thanks so much!!