Index and Rand Excel Function Help

You can put this formula in C2:

=RANDBETWEEN(1,49)

to get a random number between 1 and 49, and then you can use this formula (e.g. in C4):

=AVERAGE(INDIRECT("A"&C2&":A"&C2+11))

to get the average from that cell to the next 11.

Hope this helps.

Pete

Or try this ...

=AVERAGE(OFFSET($A$1,RANDBETWEEN(0,48),,11,1))

Pete,
Randbetween(1,49) would generate a random number between 1-49, but in this case, I want excel to choose a random cell that already has a value. I do not want a random number. Thanks!

But that random number is then used in the other formulae to determine the average, which is what I thought you wanted. If the random number is, say, 17, then the other formula is effectively:

=AVERAGE(A17:A28)

Hope this helps.

Pete

Yeah we are on the right track; however, what I want is a random cell from A1:A49 not a random number from 1-49. But, thanks for your help! At least now I have some clues.

Originally Posted by Pete_UK

But that random number is then used in the other formulae to determine the average, which is what I thought you wanted. If the random number is, say, 17, then the other formula is effectively:

=AVERAGE(A17:A28)

Hope this helps.

Pete