Insert Function helps you select a function, specify its arguments, and insert the function into a formula. It is advisable to use the Insert Function whenever you are unsure of the exact function name, or the right function to use, or how to specify the arguments.
When entering a formula, you can click the Insert Function icon at the place in the formula where you need to enter a function, then follow instructions in a series of dialog boxes. Even if you know which function to use and the exact function name, but you are unsure of how to specify the arguments, you can still use the Insert Function. In this case, after typing the function name in the formula, press CTRL+A and follow the instructions.
EXERCISE 6-10
Using Insert Function to insert a function
This exercise demonstrates how to use Insert Function to insert a function into a formula. You will complete a worksheet designed to generate random numbers to play a lottery game. The lottery game requires you to pay a dollar and select six unique whole numbers between 1 and 49, inclusive. If the six numbers you selected match those produced by the lottery foundation, you stand to win millions of dollars. If you pick the wrong numbers, you forfeit your wager. Because the lottery foundation picks the six winning numbers using a random process, there is really no logical system you can use to pick the numbers. In fact, randomly generated numbers are just as good as any numbers you may select.
To select random numbers in this exercise you will use the two functions: RAND and RANDBETWEEN.
1. Open the workbook CT2L6P3.XLS. This workbook has an incomplete worksheet that is designed to generate six lottery numbers.
2. Select cell B5. You want to enter a formula to generate a random number between 1 and 49. Type = to start the formula and click Insert Function. In the Select a category list, click All. Scroll the Select a function list until you find RAND (see Exhibit 6-9). Select this function.
EXHIBIT 6-9
Insert Function for RAND
3. Click OK. The Function Arguments dialog box comes up and reports that RAND takes no arguments. Click OK to remove the dialog box. Click the formula bar and type *48+1. The formula should then read:
=RAND()*48+1
4. Press ENTER and a number between 1 and 49 is displayed. However, the result has will need to use the ROUND function to round the value to the nearest integer. 5. Select cell B5 again. Start a new formula by entering =. Click Insert Function. In the
Select a category list, click All. Scroll the Select a function list until you find ROUND. Select this function. Click OK.
6. The Function Arguments dialog box appears (Exhibit 6-10). The ROUND function takes two arguments: number and num_digits. When you click the text boxes of each of the parameters, the dialog box displays the type of input expected. In this case, it informs you that number is the number you want to round and num_digits is the number of digits to which you want to round the number. Here, number should be the formula that calculates the random number between 1 and 49, and num_digit should be 0 to round to the nearest integer.
EXHIBIT 6-10
Function Arguments dialog box for ROUND
7. In the number box, type RAND()*48+1 and in the num_digit box, type 0 (see Exhibit 6-11). Click OK. The formula in cell B5 should now be
=ROUND(RAND()*48+1,0). Press ENTER and the random number in cell B5 is an integer between 1 and 49. If you want to put in a cell reference as an argument, you can click the cell selector, select the cell you want and press ENTER to put the cell reference in the Function Arguments dialog box.
EXHIBIT 6-11
Function Arguments dialog box for ROUND, with arguments entered
8. Select the range B5 to B10. Click the Fill icon, then choose Down to copy the formula in cell B5 to cells B6 to B10. If any of the numbers is repeated, press F9 to generate a new set of random numbers.
9. If you had difficulty with this exercise, compare your workbook with CT2L6P3S.XLS. Leave the workbook open for the next exercise. Cell
EXERCISE 6-11
Using the RANDBETWEEN function
This exercise shows how to use the Function Arguments to enter arguments into a known function. You need the completed workbook from Exercise 6-10, or the solution in CT2L6P3S.XLS for this exercise.
1. In cell C3, enter Using RANDBETWEEN. Widen column C to fit this entire label in the column.
2. Select cell C5. Click the Insert Function icon and select RANDBETWEEN. 3. The Function Arguments dialog box informs you that the RANDBETWEEN
function needs two arguments: bottom and top. (If RANDBETWEEN is not on the function list, you have not yet installed or enabled the Analysis ToolPak.)
4. Type 1 in the Bottom box, and 49 in the Top box. Click OK or press ENTER. Cell C5 contains a random integer between 1 and 49. Notice that the RANDBETWEEN function does not require the use of the ROUND function.
5. Select the range C5:C10. Click the Fill icon, then choose Down to copy the formula in cell C5 to cells C6 to C10. If any of the numbers is repeated, press F9 to generate a new set of random numbers.
6. Save the completed workbook under your initials. If you had difficulty with this exercise, compare your workbook with CT2L6P4S.XLS.