Using RANDBETWEEN with Non-Numeric Character strings To Generate a Result

jworkman7

Board Regular
Joined
Jul 1, 2015
Messages
50
Cells AE2 thru AH2 have the following characters:

AE2: COO1536578
AF2: AFI58491599
AG2: GHU4582154
AH2: UHT7851596

In AB2, I'd like to write a formula similar to the below, which will choose one of these cells at random and populate it in AB2. I currently have a working formula as follows, which works if the cells are numeric, but now need one for non-numeric:

=IFERROR(INDEX(Sheet1!$AE2:$AH2,1, RANDBETWEEN(1,COUNTIF(Sheet1!$AE2:$AH2,">0"))),0)
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try changing
COUNTIF(Sheet1!$AE2:$AH2,">0")
to
COUNTA(Sheet1!$AE2:$AH2)
 

jworkman7

Board Regular
Joined
Jul 1, 2015
Messages
50
Try changing
COUNTIF(Sheet1!$AE2:$AH2,">0")
to
COUNTA(Sheet1!$AE2:$AH2)

Hey Jonmo, this worked yes, with one exception... if any of the four cells in my target range are blank, how can I ignore them in generating the random selection? As it happens, some of the time, two or three out of the four cells in the range are blank, or result in "" because they are errors
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
OK, so they're not really blank. They contain formulas returning ""

Try
COUNTIF(Sheet1!$AE2:$AH2,"?*")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,311
Messages
5,600,886
Members
414,414
Latest member
neil_c

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