randomly picking up an array element from an array of elem..

Sanjog · External Since: Aug 08, 2012 Posts: 1

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 ?

--
Sanjog

James Ravenswood · External Since: Jun 16, 2010 Posts: 11

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)

joeu2004 · External Since: Jul 13, 2011 Posts: 50

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