randomly picking up an array element from an array of elements
 

I have an array of x,y points which are inputted in a single cell
for example
array 1 = (0.2,0.4) , (0.23,0.34),(.87,.32),(-.33,-.54).... there are such 50 points in array 1.

There are different numeric triggers which are matched with the arrays.
For example
trigger 1 = array 1
trigger 2 = array 2
so on...

When the event trigger 1 = array 1 is true then I want to read these 50 points in array 1 and randomly pick one set of (x,y) and write the value in a different cell. How to do that in excel ?

Hi, any chance of an example workbook with a "before" and "after" along with explanations of how the result was reached?

Would make it far quicker and easier to provide you with a useful answer.

randomly picking up an array element from an array of elements
 

If the points are stored in cells A1 thru A50, then to pick one fo these at random:

=INDEX(A1:A50,RANDBETWEEN(1,50),1)

randomly picking up an array element from an array of elements
 

"Sanjog" wrote:
I have an array of x,y points which are inputted in a single cell
for example
array 1 = (0.2,0.4) , (0.23,0.34),(.87,.32),(-.33,-.54)....
there are such 50 points in array 1.

You clearly explained that all 50 coordinates (x-y pairs) are in a
__single_cell__, not an array of cells.

That is unfortunate. Since the coordinates seem to have a variable form, I
think it would be easier to use VBA to parse the "array" of coordinates.


"Sanjog" wrote:
There are different numeric triggers which are matched with the arrays.
For example
trigger 1 = array 1
trigger 2 = array 2
so on...
When the event trigger 1 = array 1 is true then I want to read
these 50 points in array 1 and randomly pick one set of (x,y)
and write the value in a different cell. How to do that in excel ?

The fastest way to get an applicable solution to your problem is to write
examples in the language you are talking about, namely Excel formulas and
cell references or VBA.

I have no idea what you mean by "event trigger 1 = array 1".

Suppose "array 1" is in A1, "array 2" is in A2 etc up to "array 10" in A10,
and the trigger is the value 1, 2 etc up to 10 in B1.

Then you might write:

=randCoord(INDEX(A1:A10,B1))

where randCoord is the following VBA function:

Option Explicit

Function randCoord(arr As String)
Dim n As Long, x As Long, v As Variant
v = Split(arr, ",") ' v(0),v(1),...,v(n-1)
n = UBound(v)
x = Int((n \ 2) * Rnd())
randCoord = Trim(v(2 * x)) & "," & Trim(v(2 * x + 1))
End Function

Note: This does not ensure unique random selection. You did not ask for
that.