IF Function with some randomization!!!

LukeB11

New Member
Joined
Mar 31, 2014
Messages
9
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! :)
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
One way:

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

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,962
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Mar 31, 2014
Messages
9
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,962
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

Rich (BB code):
=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:

Rich (BB code):
=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

Well-known Member
Joined
Mar 2, 2014
Messages
2,962
Office Version
  1. 2010
Platform
  1. Windows
If you cannot depend on A1 having the expected values, you might write:
Rich (BB code):
=IF(OR(A1<1,A1>5),"",CHOOSE(A1,"+","-","x","/",LOOKUP(RAND(),{0,0.25,0.50,0.75},{"+","-","x","/"})))

Alternatively:

Rich (BB code):
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,473
Messages
5,596,356
Members
414,061
Latest member
JJSB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top