Random string from an array Excel

Question

I'm just wondering if anyone can solve a simple query in Excel. Basically I want a randomly generated string from an array.

The formula I have is this:

=INDEX({"Day","Night","Mixed"},RANDBETWEEN(1,3))

This works, however, whenever I switch between worksheets the values change and are not fixed once randomly selected.

Anyone have any ideas?

Probably not the best solution, but you could turn off automatic calculations. — Matt Cremeens
– Matt Cremeens, Commented Feb 25, 2016 at 22:54
RANDBETWEEN is volatile, as in will recalculate every time the workbook would recalculate. So when leaving the sheet it will recalculate. The only fix is to copy and paste values. — Scott Craner
– Scott Craner, Commented Feb 25, 2016 at 22:56
I also bet if you stored all of your random numbers in a different spreadsheet and then linked to them in your formulas, they wouldn't change. — Matt Cremeens
– Matt Cremeens, Commented Feb 25, 2016 at 23:00
I would use VBA for this. This way you can trigger it whenever you want and not have to worry about disabling autocalculate. — n8.
– n8., Commented Feb 26, 2016 at 0:19

Dirk Reichel · Accepted Answer · 2016-02-26 00:13:24Z

2

Go to options -> formulas -> enable iterative calculation
Then use a formula like this in B1:

  =IF(A1="","",IF(B1="",INDEX({"Day","Night","Mixed"},RANDBETWEEN(1,3)),B1)

If you empty out A1 then B1 will also be empty. If you put anything in A1 then B1 will choose randomly and stay with it till you empty out A1 again (where B1 will also be empty again)

Alternatively just copy you formula and paste "values only"... but the formula will be gone that way...

answered Feb 26, 2016 at 0:13

Dirk Reichel

7,9991 gold badge18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan Donoghue · Accepted Answer · 2016-02-26 00:31:01Z

0

You can use a self referencing UDF, something like this would work:

Function RandInArray(MyString As String, ByRef Target As Range)
Dim MyArr As Variant
If Target.Text = 0 Then
    MyArr = Split(MyString, ",")
    RandInArray = MyArr(Application.WorksheetFunction.RandBetween(LBound(MyArr), UBound(MyArr)))
Else
    RandInArray = Target.Value
End If
End Function

In B1 I have the formula: =RandInArray("Day,Night,Mixed",B1) Note its self reference to B1

Basically the formula says if you already have a value then don't refresh it but if you don't have a value then pick one randomly from the list passed in.

If you hit F2 (edit cell) and press enter you will force it to recalculate and get a new (or the same as is the rule of the randbetween) value, if you press F9 (recalculate) it will not change.

edited Feb 26, 2016 at 0:31

answered Feb 26, 2016 at 0:25

Dan Donoghue

6,2662 gold badges20 silver badges36 bronze badges

1 Comment

Dirk Reichel Over a year ago

You will still get the circular reference warning later on if iterative calculation is not enabled. however: enabling it, makes the UDF obsolete to my eyes... better refer to another cell as a trigger (this way you can avoid the changes inside the settings)

Collectives™ on Stack Overflow

Random string from an array Excel

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related