Random Selection of cells and values averaged.

**rahulk** · 08-01-2009, 08:49 PM

Hi in the excel sheet appended below, I am trying to randomly select a 7 cell range column wise from an array B1:M30 - (e.g.- B2:B9 ;F11:F18 etc ) and want to average the values for the output. So far, I have only managed to randomly select one cell.

Q: How do you randomly select a range from a big array column wise and average the values.

This is part of an analysis and is crucial. I have a deadline tomorrow am literally begging for help.
Thanks,
Rahul

[email protected]

**martindwilson** · 08-01-2009, 09:31 PM

=AVERAGE(OFFSET(INDIRECT(ADDRESS(RANDBETWEEN(1,24),RANDBETWEEN(2,13))),0,0,7,1))
modify that to
=AVERAGE(OFFSET(INDIRECT(ADDRESS(N1,O1)),0,0,7,1)) then put randbetween in N1,O1 so you can also retrieve address
assuming of course last range must be minimum of 7 rows so last range can only be from row 24 to 30 inclusive
see attached