TUTORIAL 4 - Spreadsheets

TUTORIAL 4 -

Spreadsheets

By TechMan

Open Ended

(a) What is the formula in cell B14?

a.) What is the formula in cell B14?

​

=SUM(​B10:B13)

​

​Note: =B10+B11+B12+B13 is not the best possible formula as if more rows of expenditure are added then the formula in B14 would have to be changed.

​Question 1:

Open Ended

(b) What is the formula in cell B16?

(b) What is the formula in cell B16?

​

=B7-B14

​Question 1:

Open Ended

(c) The formula in cell B16 is copied into cell C16. What is the formula in cell C16?

(c) The formula in cell B16 is copied into cell C16. What is the formula in cell C16?

​

=C7-C14

​Question 1:

Multiple Choice

2. Cells F1, F2 and F3 in a worksheet contain the values 2, 40 and 10 respectively. What value will be displayed in a cell containing the formula =F1*F2/10+F3?

• [A] 4

• [B] 9

• [C] 10

• [D] 18

• [E] 90

Multiple Choice

3. Cell C20 in a worksheet contains the formula =MIN(C2:C19). If the entire row 12 is deleted, as it contains no data, what will now be the formula in cell C19?

• [A] =MIN(C2:C19)

• [B] =MIN(C2:C18)

• [C] =MIN(C2:C11,C13:C19)

• [D] =MIN(C1:C18)

• [E] =MIN(C3:C19)

Multiple Choice

Questions 4 and 5 refer to the scenario below:

A teacher has a worksheet with 200 students’ names and test marks entered in rows 2 to 201. A portion of the worksheet is shown below:

4. The selected cells (columns A and E) can be referred to as __________.

• [A] an adjacent cell range

• [B] contiguous cells

• [C] a non-adjacent cell range

• [D] relative cells

• [E] absolute cells

Multiple Choice

Questions 4 and 5 refer to the scenario below:

A teacher has a worksheet with 200 students’ names and test marks entered in rows 2 to 201. A portion of the worksheet is shown below:

5. What formula should be entered into cell B202 to calculate the average Test 1 mark?

• [A] =TOTAL(B2:B201)/200

• [B] =AVERAGE(B1:B202)

• [C] =AVERAGE(B2:B201)

• [D] =TOTAL(B2+B201)/200

• [E] None of the above

Open Ended

6. What is the difference between a worksheet and workbook in MS Excel?

6. What is the difference between a worksheet and workbook in MS Excel?​

​

- A workbook is a Microsoft Excel document. A workbook is a collection of worksheets, whereas a worksheet is an individual area or sheet for entering data ​

Open Ended

7. When writing formulas, why is it preferable to use cell references rather than just typing in values?

7. When writing formulas, why is it preferable to use cell references rather than just typing in values?

- Because when an entry in a worksheet cell is changed, all formulas that use that cell are automatically updated.

​

- It is not necessary to change all the cells that refer to that entry manually so it is easy to update.

​

- It maintains data integrity as limits the number of changes to be made & so the number of possible errors. ​

Open Ended

8. How does clearing a column of data differ from deleting a column?

8. How does clearing a column of data differ from deleting a column?​

​- Clearing a column deletes the contents of the cells in the column but does not affect the position of other cells in the workbook.

​

- Deleting a column removes that column of cells from the worksheet and other columns are shifted in the direction of the deleted column.

Open Ended

9. How can you create a series of even numbers from 2 to 100 in column A of your worksheet?

9. How can you create a series of even numbers from 2 to 100 in column A of your worksheet?

• Enter 2 into cell A1 and 4 into cell A2

​

• Select cells A1 and A2

​

• Move the mouse until the fill handle appears at the bottom right corner of cell A2

​

• Hold down the mouse button and drag the fill handle down until 100 appears in cell A50

​

​

Multiple Choice

11. The cell C2 in the worksheet shown above contains the formula =F1*G3. When the cell C2 is copied to cell D2, the resulting formula in cell D2 is ________.

• [A] =G2*H4

• [B] =G1*H3

• [C] =G1*G3

• [D] =F1*G3

• [E] =F2*G4

Multiple Choice

12. In the worksheet below, cells A1 and A2 are selected and then the fill handle dragged down column A. Cell A4 will then display ________. Similarly cell D1 is selected and the fill handle dragged down column D. Cell D3 will display ________.

• Cell A4   Cell D3

  [A]   12   Monday

• Cell A4   Cell D3

  [B]   13   Monday

• Cell A4   Cell D3

  [C] 13 Wednesday

• Cell A4   Cell D3

  [D] 15 Wednesday

• [E] None of the above

Multiple Choice

13. In the worksheet shown below, the formula =SUM(B1:B5) is entered into cell B6. What will be displayed in cell B6?

• [A] =SUM(B1:B5)

• [B] 57

• [C] 58

• [D] #NAME?

• [E] It is impossible to determine from the information given

Multiple Choice

14. In the worksheet shown below, which formula is entered into cell D2 to calculate the cost of fabric?

• [A] =SUM(B2:C2)

• [B] =B2C2

• [C] =R30.00*10

• [D] =B2*C2

• [E] =SUM(B2*C2)