Solved how to select 10 of fields in excel spreadsheet

May 24, 2013 at 12:17:43
Specs: Windows 7
How to randomly select a percentage of rows in excel spreadsheet? I have a spreadsheet with approx. 500 rows and I want 10% of the rows to be randomly selected?

See More: how to select 10 of fields in excel spreadsheet

Report •


#1
May 24, 2013 at 13:45:28
Do you just want to Select the rows or do you want to do something with the values in the cells/rows?

Randomly selecting the rows will require a macro since a formula can't select anything.

Depending on what you want to do with the random selections, a formula might be available.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 25, 2013 at 15:20:42
✔ Best Answer
Per the info found at this site, you can create a list of 50 random numbers between 1 and 500:

http://www.get-digital-help.com/200...

The modified formula to get 50 numbers (10% of 500) is as follows:

Enter this in A2 and press Ctrl-Shift-Enter to create an array formula:

=LARGE(ROW($1:$500)*NOT(COUNTIF($A$1:A1, ROW($1:$500))), RANDBETWEEN(1,501-ROW(A1)))

Next, Autofill the formula down to A51. You should get 50 random numbers between 1 and 500, inclusive.

Now, you can use the INDIRECT function to pull the values from those 50 Rows, e.g.

Let's say you have 500 values in B2:B501 and you want to SUM a random 50 (10%) of those values:

Put this in C2 and drag it down to C51:

=INDIRECT("B"&A2)

The values in Column A will be used as the Row numbers for the INDIRECT functions Column B. You should now see 50 "random" values from Column B in C2:C51. You can now e.g. SUM the values in C2:C51, or perform any other operation on those numbers.

Note: The RANDBETWEEN function is going to recalculate and create a new list of numbers every time the sheet calculates. Therefore, let's say you create your list of random values in C2:C51. You then enter =SUM(C2:C51) and press Enter. The RANDBETWEEN function will recalculate, all of the "row numbers" will change and the SUM function will SUM the new list of values just created, not the list you saw just before hitting Enter.

You would have to use PasteSpecial...Values if you don't want the list to change every time the sheet calculates.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question