AAT L3 Spreadsheets for Accounting 1 — Questions and Answers
Question 1: In Microsoft Excel, which function calculates the sum of a range of cells that meet a specified condition?
- SUM
- SUMIF (Correct answer)
- COUNTIF
- AVERAGE
Correct answer: SUMIF
SUMIF adds up all cells in a range that meet a single criterion — for example, summing all sales for a particular region or product. For multiple criteria, SUMIFS is used.
Question 2: The Excel formula =VLOOKUP(A2,B:D,3,FALSE) will:
- Count the number of times A2 appears in column B
- Return the value in the 3rd column of the range B:D where column B matches A2, using exact match (Correct answer)
- Average all values in column D
- Return an error if A2 is not found in column B
Correct answer: Return the value in the 3rd column of the range B:D where column B matches A2, using exact match
VLOOKUP looks down the first column (B) for the value in A2 (exact match due to FALSE), then returns the corresponding value from the 3rd column of the range (column D).
Question 3: Absolute cell references in Excel (e.g., $A$1) are used to:
- Prevent a cell from being edited
- Lock the row and column reference so the reference does not change when the formula is copied (Correct answer)
- Create a named range for the cell
- Apply conditional formatting to the cell
Correct answer: Lock the row and column reference so the reference does not change when the formula is copied
Absolute references ($ prefix) fix the cell address; when a formula containing $A$1 is copied, the reference always points to cell A1 regardless of where the formula is pasted.
Question 4: The Excel function =IF(B3>100,'Yes','No') will return 'Yes' when:
- B3 equals 100
- B3 is less than 100
- B3 is greater than 100 (Correct answer)
- B3 contains text
Correct answer: B3 is greater than 100
The IF function evaluates the logical test (B3>100); if TRUE it returns 'Yes'; if FALSE it returns 'No'. 'Yes' appears only when B3 is strictly greater than 100.
Question 5: A pivot table in Excel is used to:
- Create a chart from data
- Summarise, analyse, and rearrange large data sets interactively (Correct answer)
- Apply macros to data
- Format cells automatically based on values
Correct answer: Summarise, analyse, and rearrange large data sets interactively
Pivot tables enable users to summarise large datasets quickly by aggregating data (sum, count, average) and arranging it by different fields, making analysis and reporting much faster.
Question 6: Which Excel feature automatically formats cells based on their content (e.g., highlighting cells above a certain value in red)?
- Cell styles
- Conditional formatting (Correct answer)
- Data validation
- Pivot charts
Correct answer: Conditional formatting
Conditional formatting applies formatting rules (colour, font, borders) to cells automatically based on their values or formulas — for example, turning negative values red or highlighting cells above a threshold.
AAT L3 Spreadsheets for Accounting 1
In Microsoft Excel, which function calculates the sum of a range of cells that meet a specified condition?