Random selection option, same 'random' selection in two columns

[applebanana]

Hi,

Simple example. I have data in two columns (A&B), 10 rows each, now I want to calculate the average value of two randomly selected numbers from column A in cell C1:

=AVERAGE(RandomSelection($A$1:$A$10),RandomSelection($A$1:$A$10))

Easy. For illustrative purposes, assume the value calculated is the average of cells A2 and A7.

But now I want to do a similar calculation for column B but based on the exact same randomly selected rows as for column A, it should automatically (and non-randomly) calculate the average of B2 and B7 in cell D1. How do I do this? Is it even possible?

Thank you

[mehmetcik]

Use this formula in two cells

A37 and A38 for example

Please Login or Register  to view this content.

Then these are your formulas

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[applebanana]

Thank you; the document that I am working on is obviously a lot more complex but the results that I got were what could be expected.
What is not entirely clear to me though is why the random number is 1,10; I take it that this is not related to the number of rows in the example I gave? When I change it to 0,10 it actually gives better results but I am not sure what I am doing.

Use this formula in two cells

A37 and A38 for example

Please Login or Register  to view this content.

Then these are your formulas

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Tony Valko]

What is not entirely clear to me though is why the random number is 1,10; I take it that this is not related to the number of rows in the example I gave?.

No, the 1 and 10 ARE related to the number of rows in the referenced range.

In the example the range is A1:A10. There are 10 rows in that range so the random numbers generated need to be between 1 and 10 (inclusive).

[applebanana]

Ah I see, so it is the actual total numbers of rows. I now have:

=AVERAGE(INDEX($AF$35:$AF$67,BB2),INDEX($AF$35:$AF$67,BB3),INDEX($AF$35:$AF$67,BB4),INDEX($AF$35:$AF$67,BB5),INDEX($AF$35:$AF$67,BB6))

and random number between 1,33