Reference errors and troubleshooting in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to correctly reference cell A1.

Excel

=SUM([1])

Drag options to blanks, or click blank then click option'

A1A

BCellA1

CA1

DA_1

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the column letter and row number.

Using invalid characters in the cell reference.

The correct cell reference format is the column letter followed by the row number, like A1.

2fill in blank

medium

Complete the formula to fix the #REF! error caused by a deleted cell reference.

Excel

=SUM(B1:[1])

Drag options to blanks, or click blank then click option'

AC0

BZ1

CA1

DB10

Attempts:

3 left

💡 Hint

Common Mistakes

Using invalid cell references like C0.

Choosing a cell outside the intended range.

The range must be valid and continuous. B10 is a valid cell reference to complete the range.

3fill in blank

hard

Fix the error in the formula by completing the missing reference.

Excel

=VLOOKUP(A2, [1], 2, FALSE)

Drag options to blanks, or click blank then click option'

ASheet1!A:B

BA2:B10

CA:B

DSheet1!1:2

Attempts:

3 left

💡 Hint

Common Mistakes

Using a range without the sheet name when referencing another sheet.

Using row numbers instead of columns for the table array.

The table array must include the sheet name and the correct columns. Sheet1!A:B correctly references columns A and B on Sheet1.

4fill in blank

hard

Fill both blanks to create a formula that sums values in column C only if column B equals "Yes".

Excel

=SUMIF([1], [2], C:C)

Drag options to blanks, or click blank then click option'

AB:B

B"Yes"

CC:C

D"No"

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column for the range.

Not putting the criteria text in quotes.

The range to check is column B (B:B), and the criteria is the text "Yes".

5fill in blank

hard

Fill all three blanks to create a formula that counts cells in column D that are greater than 100.

Excel

=COUNTIF([1], [2] & [3])

Drag options to blanks, or click blank then click option'

AD:D

B">"

C100

D"D:D"

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the range in quotes incorrectly.

Not combining the operator and number properly.

The range is column D (D:D), the operator is ">", and the number is 100. The operator and number are combined as a string.