Spreadsheets 1 Tutorial 5

Spreadsheets Tutorial 2

By TechMan

Open Ended

1. The spreadsheet shown is used to calculate a customer’s monthly electricity bill. The customer pays R0.50 for each unit of electricity that is used plus a monthly standing charge of R50.00.

(a) What formula is entered into B8 and then copied into the remaining cells in row 8?

(a) What formula is entered into B8 and then copied into the remaining cells in row 8?

=B6-B7

Open Ended

1. The spreadsheet shown is used to calculate a customer’s monthly electricity bill. The customer pays R0.50 for each unit of electricity that is used plus a monthly standing charge of R50.00.

(b) What formula is entered into B9 and then copied into the remaining cells in row 9?

(b) What formula is entered into B9 and then copied into the remaining cells in row 9?

=B8*$B$3

Open Ended

1. The spreadsheet shown is used to calculate a customer’s monthly electricity bill. The customer pays R0.50 for each unit of electricity that is used plus a monthly standing charge of R50.00.

(c) What formula is entered into B10 and then copied into the remaining cells in row 10?

(c) What formula is entered into B10 and then copied into the remaining cells in row 10?

=B9+$E$3

Open Ended

2. Give the value of cell C1 if the following formula has been entered into C1:

(a) =B2>B1

2. Give the value of cell C1 if the following formula has been entered into C1:

(a) TRUE

​​

Open Ended

2. Give the value of cell C1 if the following formula has been entered into C1:

(b) =B2<>200

2. Give the value of cell C1 if the following formula has been entered into C1:

​(b) FALSE

​

Open Ended

2. Give the value of cell C1 if the following formula has been entered into C1:

(c) =NOT(B1=B2)

2. Give the value of cell C1 if the following formula has been entered into C1:

(c) TRUE

​

Open Ended

2. Give the value of cell C1 if the following formula has been entered into C1:

(d) =IF(A2>A1,A2,A1)

2. Give the value of cell C1 if the following formula has been entered into C1:

(d) 7​

Open Ended

2. Give the value of cell C1 if the following formula has been entered into C1:

(e) =IF(B1>=100,"B1","NOT B1")

2. Give the value of cell C1 if the following formula has been entered into C1:

(e) B1

Open Ended

3. The following part of a spreadsheet shows a Sport’s Club membership details. The input data, shown below, includes the fees for the two membership categories (student members are charged a lower fee), membership IDs, gender and category of members.

(a) What is the formula in cell D7, which can be copied down column D, to determine the membership fee?

(a) What is the formula in cell D7, which can be copied down column D, to determine the membership fee?

=IF(B7="Student",$B$3,$B$4) OR =IF(B7="Ordinary", $B$4,$B$3)

Open Ended

3. The following part of a spreadsheet shows a Sport’s Club membership details. The input data, shown below, includes the fees for the two membership categories (student members are charged a lower fee), membership IDs, gender and category of members.

(b) If the list of members is contained in rows 7 to 200 but some rows may be null entries (empty rows). What formula could be entered into cell D3 to calculate the total number of members?

(b) If the list of members is contained in rows 7 to 200 but some rows may be null entries (empty rows). What formula could be entered into cell D3 to calculate the total number of members?

=COUNTA(A7:A200)

Open Ended

3. The following part of a spreadsheet shows a Sport’s Club membership details. The input data, shown below, includes the fees for the two membership categories (student members are charged a lower fee), membership IDs, gender and category of members.

(c) What formula could be entered to calculate the total number of female members?

(c) What formula could be entered to calculate the total number of female members?

=COUNTIF(C7:C200,"F")

Open Ended

3. The following part of a spreadsheet shows a Sport’s Club membership details. The input data, shown below, includes the fees for the two membership categories (student members are charged a lower fee), membership IDs, gender and category of members.

(d) What formula could be entered to calculate the total amount paid in membership fees by men?

(d) What formula could be entered to calculate the total amount paid in membership fees by men?

=SUMIF(C7:C200,"M",D7:D200)

Open Ended

3. The following part of a spreadsheet shows a Sport’s Club membership details. The input data, shown below, includes the fees for the two membership categories (student members are charged a lower fee), membership IDs, gender and category of members.

(e) What formula could be entered to calculate the total number of members with a membership ID beginning with X?

(e) What formula could be entered to calculate the total number of members with a membership ID beginning with X?

=COUNTIF(A7:A200,"X*")

Open Ended

3. The following part of a spreadsheet shows a Sport’s Club membership details. The input data, shown below, includes the fees for the two membership categories (student members are charged a lower fee), membership IDs, gender and category of members.

(d) What formula could be entered to calculate the total amount paid in membership fees by men?

Open Ended

3. The following part of a spreadsheet shows a Sport’s Club membership details. The input data, shown below, includes the fees for the two membership categories (student members are charged a lower fee), membership IDs, gender and category of members.

(f) What formula could be entered to calculate the total number of female student members?

(f) What formula could be entered to calculate the total number of female student members?

=COUNTIFS(C7:C200,"F",B7:B200,"STUDENT")

Open Ended

4. Evaluate the following expressions:

(a) =OR(6<7,FALSE)

(a) =OR(6<7,FALSE)

TRUE

Open Ended

4. Evaluate the following expressions:

(b) =AND(8<10,9>11)

(b) =AND(8<10,9>11)

FALSE

Open Ended

4. Evaluate the following expressions:

(c) =AND(9>2,NOT(FALSE))

(c) =AND(9>2,NOT(FALSE))

TRUE

Multiple Choice

5. Cells A3, A4, A5, A6 and A7 contain the values 2, 3, 4, 5 and 6 respectively. Cells A8 and A9 are empty. Cell A10 contains the value 10. What is the result of the formula =COUNT(A3:A10)?

• [A] 5

• [B] 6

• [C] 8

• [D] 30

• [E] None of the above

Multiple Choice

6. The formula _________ has been entered into cell C5 in the worksheet shown and then copied and pasted into cell C6.

• [A] =B5*B2

• [B] =B5$B2$

• [C] =B5*$B$2

• [D] =B6*B2

• [E] =100*10%

Multiple Choice

7. If the entry in cell address A5 is greater than 500 then cell address A5 must be multiplied by 10, otherwise the value in cell address A5 must be equal to zero. What is the correct formula for the above statement?

• [A] =IF(A5>500,A5*10,0)

• [B] =IF(A5>500,"A5*10","0")

• [C] =IF(A5>500 then A5*10 else A5=0)

• [D] =IF(A5<500,0,A5*10)

• [E] =IF(A5<500,"0","A5*10")

Multiple Choice

8. In the worksheet shown below, formulas are entered into cells D2:F2 and then copied down the columns. Which of the following best describes the formula used to calculate the Tax in column E?

• [A] The Gross Pay and the Tax Rate cell references are both relative references

• [B] The Gross Pay and the Tax Rate cell references are both absolute references

• [C] The Gross Pay cell reference is a relative reference and the Tax Rate cell reference is an absolute reference

• [D] The Gross Pay cell reference is an absolute reference and the Tax Rate cell reference is a relative reference

• [E] There are no references to Gross Pay and Tax Rate used in the formula

Multiple Choice

9. To reference cell K3 on another worksheet named Quarter1 within the same workbook, you enter:

• [A] SheetQuarter1!K3

• [B] Sheet!Quarter1K3

• [C] K3!SheetQuarter1

• [D] K3!Quarter1

• [E] Quarter1!K3

Multiple Choice

10. With cell B3 selected, you activate the command Freeze Panes from the View Tab. What happens when you scroll through the worksheet?

• [A] The first three rows and column A are always visible

• [B] The first two rows and column A are always visible

• [C] The first three rows and columns A and B are always visible

• [D] The first two rows and columns A and B are always visible

• [E] The first row and column A are always visible

Multiple Choice

11. In the worksheet shown, A6 contains the formula =100*B1. What is the value of cell A6?

• [A] 1512

• [B] 1512.69

• [C] 1513

• [D] 1512.687

• [E] =100*B1

Multiple Choice

12. A workbook contains five worksheets named Quarter1, Quarter2, Quarter3, Quarter4 and Year2008. On each of the first four worksheets, cell D6 contains the VAT paid in that quarter. A formula is entered in the Year2008 worksheet to calculate the total VAT paid. (This is equal to the VAT paid in Quater1 plus the VAT paid in Quater2 plus the VAT paid in Quater3 plus the VAT paid in Quater4.) Which of the following statements is/are TRUE about that formula on worksheet Year2008?

• [A] It is necessary to name each VAT cell on each Quarter worksheet before using a SUM function

• [B] The absolute cell reference of D6 must be used in the formula

• [C] The range Sheet1:Sheet4! should be used in the formula

• [D] The cell range needed is a 3-D reference

• [E] [B] and [D]

Multiple Choice

13. In the worksheet shown below, customers are charged R10 per litre if they order up to and including 100 litres. If they order more than 100 litres, they are charged R10 per litre for the first 100 litres and only R5 per litre for the remaining litres. A formula is entered into cell C2 to calculate the cost per customer given that the number of litres ordered is in cell B2. This formula is then copied down column C. What is the formula in cell C2?

• [A] =IF(B2>$F$2, B2*$H$2, (B2-$F$2)*$H$3+$F$2*$H$2)

• [B] =IF(B2>$F2, (B2-$F$2)*$H$2+$F$2*$H$2,B2*$H$3)

• [C] =IF(B2>$F$2,((B2-$F$2)*$H$3)+($F$2*$H$2),B2*$H$2)

• [D] =IF(B2>$F2, (B2-$F2)*$H3+$F2*$H2,B2*$H2)

• [E] =IF(B2>F$2, (B2-F$2)*H$3+F$2*H$2,B2*H$2)

Open Ended

14. One of the fundamental issues surrounding the construction of graphs is the determination of which data elements should be plotted on the X-axis and which along the Y-axis. With the aid of suitable diagrams and an example, discuss the main issue that is used in making this determination.

14. One of the fundamental issues surrounding the construction of graphs is the determination of which data elements should be plotted on the X-axis and which along the Y-axis. With the aid of suitable diagrams and an example, discuss the main issue that is used in making this determination.

To create most charts or graphs, excluding pie charts, you typically use data that is plotted in two dimensions, as shown in Figure 1. The horizontal dimension is the x-axis. The vertical dimension is the y-axis. When you come to plot data, the known value goes on the x-axis and the measured (or "unknown") value on the y-axis. For example, if you were to plot the measured average temperature for a number of months,you'd set up axes as shown in Figure 2

Open Ended

15. A friend of yours is developing a spreadsheet application to help analyse his business performance. He asks for your recommendation in terms of choice of type of graph to construct. How do you respond? Provide reasoning/ justification for your answer.

15. A friend of yours is developing a spreadsheet application to help analyse his business performance. He asks for your recommendation in terms of choice of type of graph to construct. How do you respond? Provide reasoning/ justification for your answer.

There is no clear cut answer to this question because of the variety of factors that need to be considered before selecting a specific chart type.

These range from:

- the purpose for which the chart is to be constructed

- the specific subject matter,

- the format in which you are presenting the chart,

- the audience for which it is intended, and

- your own personal preferences

Open Ended

16. Discuss three (3) fundamental differences between a bar graph and a pie chart.

16. Discuss three (3) fundamental differences between a bar graph and a pie chart.

- Graphic:

Pie charts are circles that are broken into different slices based on the percentage of each category, whilebar charts use an x/y axis and columns to represent different data. The bars can be either horizontal or vertical.

​

- Calculation:

Bar charts represent raw data. For example, if one category's total is 40,000, then 40,000 is shown on the graph. Pie charts represent percentages of a whole. For example, if a category total equals 40,000, but 40,000 only accounts for 10 percent of the total, that category will make up 10 percent of the circle.

​

- Time Frame: A bar graph can show change over time, while a single pie chart cannot; only representing the given percentages at a fixed point in time.

​

Open Ended

17. When will you use a pie chart and when will you use a bar graph?

17. When will you use a pie chart and when will you use a bar graph?

Using bar graphs vs. pie charts

​

When to Use Bar Graphs

Bar graphs should be used when showing quantities that are either not necessarily linked or demonstratechange over time. For example, use a bar graph to show either company revenue totals over the past decade or raw sales figures for different departments.

​

When to use Pie Charts

Pie charts should be used when the data shows how different categories relate to the whole at a particular point in time. For example, the percentage relationship of company expense categories (each to the other) over a period of a year.

Open Ended

18. When is it necessary to use cell protection?

18. When is it necessary to use cell protection?

When you wish to prevent other users from changing certain cells in the worksheet (usually cells containing formulae).

Open Ended

19. What is the difference between the COUNTIF and SUMIF functions?

19. What is the difference between the COUNTIF and SUMIF functions?

The COUNTIF function counts the number of non-blank cells within a range that match the given criteria whereas the SUMIF function is used to add data that satisfies certain criteria.

Open Ended

20. List and describe the six error messages that can be displayed when a formula is not calculated properly.

20. List and describe the six error messages that can be displayed when a formula is not calculated properly.

Error messages

• #####

This means the cell contains a calculation result that is too wide for the cell to display. This is not a calculation error rather your column width is too small. So you need to adjust the column width.

• #VALUE!

This means that a formula contains the wrong type of argument like text or a cell reference that points to a cell containing text instead of a number. The formula needs to be edited to fix the error. For example, Entering text when the formula requires a number or a logical value, such as TRUE or FALSE and Supplying a range to an operator or a function that requires a single value, not a range

20. List and describe the six error messages that can be displayed when a formula is not calculated properly.

• #DIV/0!

This means that you have tried to divide by zero or a cell reference that points to a cell containing zero or a blank cell. For example, entering a formula that contains explicit division by zero (0) — e.g. =5/0 and using the cell reference to a blank cell or to a cell that contains zero as a divisor

• #REF!

This means that the cell reference is invalid such as the formula references a cell that has been deleted. For example, deleting cells referred to by other formulas, or pasting moved cells over cells referred to by other formulas.

20. List and describe the six error messages that can be displayed when a formula is not calculated properly.

• #NAME?

This means that MS Excel does not recognise the text in a formula. For example, using a name that does not exist, misspelling the name, misspelling the name of a function, omitting a colon (:) in a range reference or referencing another sheet not enclosed in single quotation marks.

• #NUM!

This means that a formula or function contains invalid numeric values, such as “$500” when it can only accept “500.”

THE END​

Please leave your student number on the chat before you exit the session