AAT L3 Spreadsheets for Accounting

Question 1

In Microsoft Excel, which function calculates the sum of a range of cells that meet a specified condition?

Accepted Answer

SUMIF

Answer

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:

Accepted Answer

Return the value in the 3rd column of the range B:D where column B matches A2, using exact match

Answer

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:

Accepted Answer

Lock the row and column reference so the reference does not change when the formula is copied

Answer

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:

Accepted Answer

B3 is greater than 100

Answer

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:

Accepted Answer

Summarise, analyse, and rearrange large data sets interactively

Answer

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.

AAT Level 3 Practice Test

AAT Level 3 Practice Test

AAT L3 Spreadsheets for Accounting