Spreadsheets

Practice questions

Which error appears when a lookup cannot find a match?

  1. #NAME?
  2. #DIV/0!
  3. ####
  4. #N/A
Answer and explanation

Answer: D

#N/A means a value is not available, as when a lookup finds no match.

A1 = 60 and A2 = 80. What does =A1+A2/2 return?

  1. 100
  2. 70
  3. 140
  4. 90
Answer and explanation

Answer: A

A2/2 = 40 is done first, then 60 + 40 = 100. The brackets in =(A1+A2)/2 would give 70.

A1:A5 hold 10, 20, 30, 40, 50 but =SUM(A1:A4) is typed. What does it return?

  1. 150
  2. 90
  3. 100
  4. 50
Answer and explanation

Answer: C

Only the first four values are added: 10 + 20 + 30 + 40 = 100. The last value is left out.

=SUMM(B2:B6) shows an error. Which one?

  1. #VALUE!
  2. #NAME?
  3. #REF!
  4. #N/A
Answer and explanation

Answer: B

The program does not recognise the function name SUMM, so it shows #NAME?.

A formula shows #DIV/0!. The most likely cause is:

  1. A misspelt function
  2. A deleted column
  3. Text in a number cell
  4. Division by zero
Answer and explanation

Answer: D

#DIV/0! means a division by zero or by an empty cell.

=IF(B2>=40,"P","F") is evaluated for B2 = 35. The result is:

  1. P
  2. F
  3. 35
  4. TRUE
Answer and explanation

Answer: B

35 >= 40 is FALSE, so the third part, F, is returned.

=MAX(B2:B9)-MIN(B2:B9) returns:

  1. 35
  2. 90
  3. 45
  4. 55
Answer and explanation

Answer: D

The largest mark is 90 and the smallest is 35, so 90 - 35 = 55.

What does =ROUND(445/8,1) return?

  1. 55.6
  2. 55.7
  3. 56
  4. 55.62
Answer and explanation

Answer: A

445 / 8 = 55.625, which rounds to 55.6 at one decimal place.

What does =SUMIF(B2:B9,">50") return?

  1. 284
  2. 244
  3. 324
  4. 445
Answer and explanation

Answer: A

52 + 78 + 90 + 64 = 284. The marks 40 and 47 are not above 50.

With the marks above, what does =COUNTIF(B2:B9,">=40") return?

  1. 5
  2. 8
  3. 6
  4. 7
Answer and explanation

Answer: C

The marks 52, 78, 40, 90, 64 and 47 meet the test, so the count is 6. The mark 40 is included.

Two cells hold 40 and 35. They are selected and the fill handle is dragged to make five cells. The fifth cell holds:

  1. 25
  2. 15
  3. 30
  4. 20
Answer and explanation

Answer: D

The step is -5, so the cells read 40, 35, 30, 25, 20.

B1 holds =A1 and is copied one cell to the left, into A1. What does that cell show?

  1. #REF!
  2. 0
  3. #DIV/0!
  4. A1
Answer and explanation

Answer: A

The reference would have to point left of column A, which does not exist, so the result is #REF!.

B2 holds =$A2+B$1 and is copied to D4. D4 holds:

  1. =$A2+D$1
  2. =$A4+D$1
  3. =$A4+B$1
  4. =A4+D1
Answer and explanation

Answer: B

The locked column of $A stays but its row moves to 4; the locked row of B$1 stays but its column moves to D.

B2 holds =A2*$D$1. After copying to B6, B6 holds:

  1. =A6*$D$1
  2. =A6*$D$5
  3. =A2*$D$1
  4. =A6*D1
Answer and explanation

Answer: A

A2 moves down 4 rows to A6, while $D$1 is absolute and stays.

F2 holds =C2*D2. After copying down to F4, F4 holds:

  1. =C2*D4
  2. =C4*D2
  3. =C4*D4
  4. =C3*D3
Answer and explanation

Answer: C

Both references are relative, so both rows shift by 2: =C4*D4.

What is =100/10/2?

  1. 20
  2. 5
  3. 50
  4. 0.5
Answer and explanation

Answer: B

Division goes from left to right: 100 / 10 = 10, then 10 / 2 = 5.

With A1 = 12 and A2 = 8, what is =A1+A2*2?

  1. 40
  2. 20
  3. 28
  4. 24
Answer and explanation

Answer: C

Multiplication first: 8 x 2 = 16; then 12 + 16 = 28. Adding first would wrongly give 40.

For the same cells, what is =AVERAGE(A1:A6)?

  1. 13.33
  2. 6.67
  3. 8
  4. 10
Answer and explanation

Answer: D

The total is 40 and four numbers are counted (the zero is included), so 40 / 4 = 10.

For the same cells, what does =COUNT(A1:A6) return?

  1. 4
  2. 6
  3. 3
  4. 5
Answer and explanation

Answer: A

The numbers are 12, 8, 20 and 0, so COUNT gives 4. COUNTA would give 5 because it also counts the text.

A1 to A6 hold 12, 8, the text n/a, 20, a blank and 0. What is =SUM(A1:A6)?

  1. 20
  2. 48
  3. 40
  4. 32
Answer and explanation

Answer: C

Only the numbers are added: 12 + 8 + 20 + 0 = 40.

Privacy · Terms · Refunds · Delivery