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.

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)

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