Microsoft Office Tutorials and References

In Depth Information

Manipulating Worksheet Data

Excel offers a wide range of tools you can use to summarize worksheet data. This section

shows you how to select rows at random using the
RAND
and
RANDBETWEEN
functions,

how to summarize worksheet data using the
SUBTOTAL
and
AGGREGATE
functions, and how

to display a list of unique values within a data set.

Selecting List Rows at Random

In addition to filtering the data that is stored in your Excel worksheets, you can choose rows

at random from a list. Selecting rows randomly is useful for choosing which customers will

receive a special offer, deciding which days of the month to audit, or picking prize winners

at an employee party.

To choose rows randomly, you can use the
RAND
function, which generates a random

value between 0 and 1, and compare the value it returns with a test value included in the

formula. As an example, suppose Consolidated Messenger wanted to offer approximately

30 percent of its customers a discount on their next shipment. A formula that returns a

TRUE
value 30 percent of the time would be
RAND<=0.3
; that is, whenever the random

value was between 0 and 0.3, the result would be
TRUE
. You could use this formula to

select each row in a list with a probability of 30 percent. A formula that displayed
TRUE

when the value was equal to or less than 30 percent, and
FALSE
otherwise, would be

=IF(RAND()<=0.3,”True”,”False”)
.

If you recalculate this formula 10 times, it’s very unlikely that you would see exactly three

TRUE
results and seven
FALSE
results. Just as flipping a coin can result in the same result

10 times in a row by chance, so can the
RAND
function’s results appear to be off if you

only recalculate it a few times. However, if you were to recalculate the function 10

thousand times, it is extremely likely that the number of
TRUE
results would be very

close to 30 percent.

Tip
Because the
RAND
function is a volatile function (it recalculates its results every time

you update the worksheet), you should copy the cells that contain the
RAND
function in a

formula and paste the formulas’ values back into their original cells. To do so, select the cells

that contain the
RAND
formulas and press Ctrl+C to copy the cell’s contents. Then, on the

Home tab, in the Clipboard group, in the Paste list, click Paste Values to replace the formula

with its current result. If you don’t replace the formulas with their results, you will never have

a permanent record of which rows were selected.

The
RANDBETWEEN
function generates a random whole number within a defined

range. For example, the formula
=RANDBETWEEN(1,100)
would generate a random

integer value from 1 to 100, inclusive. The
RANDBETWEEN
function is very useful for

creating sample data collections for presentations. Before the
RANDBETWEEN
function