ACCA Applied Knowledge · Management Accounting · Spreadsheets
A cost accountant has monthly sales units in cells B2:B13. She wants the average monthly sales volume, ignoring any blank cells. Which formula is most appropriate?
Use =AVERAGE(B2:B13). The AVERAGE function adds the numeric values and divides by how many numeric cells there are, so blank cells are ignored. Dividing the total by a fixed 12 would give a wrong figure if any month were blank.
- A=SUM(B2:B13)/12
- B=AVERAGE(B2:B13)Correct
- C=COUNT(B2:B13)
- D=MAX(B2:B13)/12
Explanation
AVERAGE sums only the cells containing numbers and divides by the count of those numbers, so blanks are ignored. Dividing SUM by 12 would understate the average if any cell is blank. COUNT gives the number of numeric cells, and MAX gives the highest value.
Did you get it right without looking?
One question tells you little. A timed set on Spreadsheets shows your real accuracy, how long you take and where you lose marks.
More Spreadsheets questions
- In a spreadsheet, cell B2 contains the formula =A2*$D$1 and is copied down to cell B3. Which formula will appear in B3?
- A budget model calculates total cost in cell F2 as =B2*C2+D2, where B2 is 500 units, C2 is a variable cost of $6 per unit and D2 is fixed co…
- A management accountant enters the formula =B2*C2 in cell D2 and copies it down to cell D3. Which formula will appear in D3?
- A cost spreadsheet shows unit costs in B2:B5 of 12, 15, 9 and 14. Cell C1 contains the formula =IF(AVERAGE(B2:B5)>12,"Review","OK"). What do…
- In a spreadsheet, the finance team of Harbor plc has a table of cost data with 20 rows. They want to display only the rows where the cost ce…
- A budget model has quarterly sales in B2:E2 (Q1 to Q4: 200, 240, 260, 300 units) and a unit price in cell G1 ($50). In B3 a formula =B2*G1 i…