Microsoft Office Tutorials and References

In Depth Information

**Manipulating worksheet data**

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 by using the
RAND
and
RANDBETWEEN

functions, how to summarize worksheet data by 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 displays
TRUE
when the value is 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 exactly three
TRUE
results

and seven
FALSE
results would occur. 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,000

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.