Finding numbers within a range on a x,y grid

[asunwatcher]

Hi there,

I have a list of places each with a grid reference (longitude / latitude). They are not arranged in any order. I am given a starting grid reference and need to select a place at random that is within a specified distance (this is given in the units of the grid).

I see the steps as follows:

1) Randbetween (1, number of places that fall within the given area) e.g. 2

2) Return the value of the 2nd place within the area.

Any ideas?

 

[asunwatcher]

So I'm just working out the logic and I can't see a way around using a VDU, which, I think, would work along these lines:

1. Find out how many locations are within my required range - this can be done simply using a sumif statement

=SUMIFS(G:G,H:H,"<="&(C4+E4),I:I,"<="&(D4+E4), H:H,">="&(C4-E4),I:I,">="&(D4-E4))

Row G: a row of 1's beside each grid reference
Row H: x - grid reference
Row I: y - grid ref

C4 - starting x-ref
D4 - starting y-ref
E4 - range for x and y

2. Generate a random number using randbetween(1, number of places that match from 1. )

3. This is where I'm stuck

i = 0

While i < RandomNumber

j = 1

IF (H(j) "<="&(C4+E4)) AND (I (j) "<="& (D4+E4)) AND (H(j) ,">="&(C4-E4)) AND (I(j),">="&(D4-E4))

THEN i = i+1 and j = j + 1

ELSE j = j+1

End

How do I turn that into a VDU?