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?

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.

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.

Ask Your Question

Weekly Poll