Table references in formulas in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to sum the values in the 'Sales' column of the table named 'Orders'.

Excel

=SUM(Orders[[1]])

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

APrice

BTotal

CAmount

DSales

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column name that does not exist in the table.

Forgetting to include the column name inside square brackets.

The correct column name in the table 'Orders' to sum is 'Sales'.

2fill in blank

medium

Complete the formula to calculate the average of the 'Quantity' column in the table named 'Inventory'.

Excel

=AVERAGE(Inventory[[1]])

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

AQuantity

BPrice

CStock

DAmount

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column name that does not contain numeric values.

Leaving out the square brackets around the column name.

The 'Quantity' column contains the numbers to average in the 'Inventory' table.

3fill in blank

hard

Fix the error in the formula to reference the 'Price' column in the table named 'Products'.

Excel

=SUM(Products[1])

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

A[Price]

B(Price)

C{Price}

D<Price>

Attempts:

3 left

💡 Hint

Common Mistakes

Using parentheses or braces instead of square brackets.

Omitting the brackets entirely.

Table column references require square brackets around the column name.

4fill in blank

hard

Fill both blanks to create a formula that sums the 'Amount' column in the table named 'Expenses' for rows where the 'Category' is 'Travel'.

Excel

=SUMIFS(Expenses[[1]], Expenses[[2]], "Travel")

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

AAmount

BCategory

CDate

DCost

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the sum range and criteria range.

Using incorrect column names.

The sum range is the 'Amount' column, and the criteria range is the 'Category' column.

5fill in blank

hard

Fill all three blanks to create a formula that calculates the average 'Score' from the table 'Results' for entries where 'Passed' is TRUE.

Excel

=AVERAGEIFS(Results[[1]], Results[[2]], [3])

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

AScore

BPassed

CTRUE

DFALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'FALSE' instead of 'TRUE' as criteria.

Putting TRUE in quotes, which makes it a text string.

The formula averages the 'Score' column where the 'Passed' column is TRUE.