Radha explores different formulas for calculating the average of the numbers…
2024
Radha explores different formulas for calculating the average of the numbers shown in the worksheet below.
A | B | C | D | |
|---|---|---|---|---|
1 | 12 | |||
2 | 5 | |||
3 | 10 | |||
4 |
Which of formulae (A)–(E), when entered in cell A4, would give an incorrect result?
(A)
=AVERAGE(A1-C3)(B)
=AVERAGE(A1:C3)(C)
=SUM(A1:C3)/3(D)
=SUM(A1:C3)/9(E)
=SUM(A1,B2,C3)/3
Choose the correct answer from the options given below.
Answer: B. (A) and (D) only — ConceptA spreadsheet range such as A1:C3 includes every cell in the rectangle from A1 through C3. Functions such as AVERAGE ignore blank cells, while SUM adds…
- A.
(B) and (C) only
- B.
(A) and (D) only
- C.
(C) and (E) only
- D.
(A), (B), and (D) only
Attempted by 2 students.
Show answer & explanation
Correct answer: B
Concept
A spreadsheet range such as A1:C3 includes every cell in the rectangle from A1 through C3. Functions such as AVERAGE ignore blank cells, while SUM adds the numeric cells in the range.
The arithmetic mean is the sum of the observations divided by their count. A minus sign performs subtraction; it does not denote a cell range.
Application
The non-blank values are A1 = 12, B2 = 5, and C3 = 10. Their sum is 12 + 5 + 10 = 27, so their average is 27 ÷ 3 = 9.
For
=AVERAGE(A1-C3), Excel first computes A1-C3 = 12-10 = 2; averaging that single result gives 2.For
=AVERAGE(A1:C3), the function averages the three numeric cells and ignores the blanks, giving 9.For
=SUM(A1:C3)/3, the calculation is 27 ÷ 3 = 9.For
=SUM(A1:C3)/9, the calculation is 27 ÷ 9 = 3.For
=SUM(A1,B2,C3)/3, the explicitly listed values total 27, and 27 ÷ 3 = 9.
Cross-check
The target average is 9. Formulae (B), (C), and (E) each produce 9, whereas formulae (A) and (D) produce 2 and 3 respectively.
Therefore, the formulae that give an incorrect result are (A) and (D) only.