PC World - USA (2020-04)

124 PCWorld APRIL 2020

HERE’S HOW EXCEL FUNCTIONS AND TOOLS

things. In Excel, the RAND function generates

a random number between 0 and 1. Note;

however, that every time you enter new data

and press the Enter key, the list of random

numbers you just created changes. If you

need to maintain your random numbers lists,

you must format the cells as values.

1. Enter the function =RAND() in
   columns A3 through A14. Select that
   column and press Ctrl+C (for copy) or click
   the Copy button under the Home tab and
   choose Copy from the drop-down menu.
   Move your cursor to cell B3 and select
   Home > Paste > Paste Special. Click the
   Values button from the Paste Special dialog
   window, then click OK.


2. Now the list contains values instead of
   functions, so it will not change. Notice (in
   the formula bar) that the random numbers
   have 15 digits after the decimal (Excel
   defaults to 9), which you can change, if
   necessary (as displayed in cell F3). Just click
   the Increase Decimal button in the Number
   group under the Home tab.


3. If you prefer to work with whole
   numbers, enter this formula in cell F3:
   =INT(RAND()*999) and you get a 3-digit
   random number. Copy the formula down
   through F12, then add another 9 to the
   string to add another digit to your random
   number—e.g., four nines equal four digits,
   five nines equal five digits. Again, you must
   copy the list and Paste As Values to maintain
   a static list.


4. COUNT FUNCTIONS
   Use the COUNT function to count the
   number of numeric values in a range of cells;
   for example: C4:C15 contains the quantity of
   garden tools Mr. McGregor needs to order
   for his shop. Note that the answer is 10 (out
   of 12), because the COUNT function does
   not include blank cells. However, if you enter
   a zero, a numeric code, or a date, Excel
   counts it as an “occupied” cell and includes
   it in its answer.
   Enter 10 numbers into column C
   (Quantity). Replace one number with a space
   (or a tap on the spacebar), then replace
   another number with a semicolon, and then
   enter a date into cell C7.
   Enter this formula at the bottom of the
   number list (C16): =COUNT(C4:C15). The
   answer is 10 (out of 12) because Excel
   counted all the numbers and the date, but
   ignored the blank cell (containing the space)
   and the punctuation in cell C8.
   Use the COUNTA function if you want to
   include numeric values, logical or error
   values, text, a space (from the spacebar),
   punctuation, symbols, or any other character
   on your keyboard.


5. Enter 12 dollar amounts into column D
   (Price). Replace one cell with a question mark,
   another cell with a symbol, and another cell
   with some text.


6. Enter this formula in D16:
   =COUNTA(C4:C15). The answer is 12 (out of

12. because Excel included all the “non-