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.
Search JabSto ::

Custom Search