CODEWITHSUNDEEP
excelexcel-formulaformula
Lemons
Lemons

Reputation: 176

Random string from an array Excel

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?

Upvotes: 3

Views: 2645

Answers (2)

Dan Donoghue
Dan Donoghue

Reputation: 6196

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.

Upvotes: 0

Dirk Reichel
Dirk Reichel

Reputation: 7979

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

Upvotes: 2

Related Questions