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.

Practice

1. What does a table reference like Sales[Amount] mean in Excel?

easy

A. It refers to a cell named 'Amount' in the worksheet.

B. It refers to the entire 'Sales' table.

C. It refers to a range named 'SalesAmount'.

D. It refers to the 'Amount' column in the 'Sales' table.

Solution

1. Step 1: Understand table and column naming

   In Excel, Sales[Amount] means the 'Amount' column inside the table named 'Sales'.

2. Step 2: Differentiate from other references

   This is different from referring to the whole table or a named range; it specifically targets one column.

3. Final Answer:

   It refers to the 'Amount' column in the 'Sales' table. -> Option D

4. Quick Check:

   TableName[ColumnName] = Column in Table [OK]

Hint: TableName[ColumnName] always means one column in that table [OK]

Common Mistakes:

• Thinking it refers to the whole table
• Confusing with named ranges
• Assuming it refers to a single cell

2. Which of these is the correct syntax to sum the 'Price' column in a table named 'Products'?

easy

A. =SUM(Products.Price)

B. =SUM(Products[Price])

C. =SUM(Products{Price})

D. =SUM(Products-Price)

Solution

1. Step 1: Recall correct table reference syntax

   Excel uses square brackets to refer to columns inside tables, so Products[Price] is correct.

2. Step 2: Check formula structure

   The SUM function wraps the table column reference correctly as =SUM(Products[Price]).

3. Final Answer:

   =SUM(Products[Price]) -> Option B

4. Quick Check:

   SUM(TableName[ColumnName]) = Correct sum formula [OK]

Hint: Use square brackets for columns inside tables in formulas [OK]

Common Mistakes:

• Using dot notation instead of brackets
• Using curly braces instead of brackets
• Using minus sign instead of brackets

3. Given a table named Orders with a column Quantity containing values 2, 5, 3, what is the result of =SUM(Orders[Quantity])?

medium

A. 2

B. Error

C. 10

D. 5

Solution

1. Step 1: Identify values in the 'Quantity' column

   The values are 2, 5, and 3 in the 'Quantity' column of the 'Orders' table.

2. Step 2: Calculate the sum of these values

   Sum = 2 + 5 + 3 = 10.

3. Final Answer:

   10 -> Option C

4. Quick Check:

   SUM(2,5,3) = 10 [OK]

Hint: Sum adds all values in the specified table column [OK]

Common Mistakes:

• Thinking it returns a single cell value
• Expecting an error due to table reference
• Adding only the first value

4. You wrote the formula =SUM(Sales[Amount] but it shows an error. What is the likely fix?

medium

A. Add the missing closing parenthesis: =SUM(Sales[Amount])

B. Change brackets to parentheses: =SUM(Sales(Amount))

C. Remove the table name: =SUM([Amount])

D. Replace SUM with SUMIF: =SUMIF(Sales[Amount])

Solution

1. Step 1: Check formula syntax

   The formula is missing a closing parenthesis at the end, causing a syntax error.

2. Step 2: Correct the formula by adding the missing parenthesis

   Adding the closing parenthesis fixes the syntax: =SUM(Sales[Amount]).

3. Final Answer:

   Add the missing closing parenthesis: =SUM(Sales[Amount]) -> Option A

4. Quick Check:

   Balanced parentheses fix formula errors [OK]

Hint: Always check for matching parentheses in formulas [OK]

Common Mistakes:

• Using wrong bracket types
• Removing table name incorrectly
• Changing function without reason

5. You have a table named Inventory with columns Item and Stock. You want to calculate total stock only for items named "Apple" using a formula with table references. Which formula is correct?

hard

A. =SUMIFS(Inventory[Stock], Inventory[Item], "Apple")

B. =SUM(Inventory[Stock] = "Apple")

C. =SUMIF(Inventory[Stock], "Apple", Inventory[Item])

D. =SUMIF(Inventory[Item], Inventory[Stock], "Apple")

Solution

1. Step 1: Understand the goal and function

   You want to sum the 'Stock' column only where 'Item' equals "Apple". The correct function for multiple criteria is SUMIFS.

2. Step 2: Check formula syntax with table references

   The correct syntax is =SUMIFS(Inventory[Stock], Inventory[Item], "Apple"), summing 'Stock' where 'Item' matches "Apple".

3. Final Answer:

   =SUMIFS(Inventory[Stock], Inventory[Item], "Apple") -> Option A

4. Quick Check:

   SUMIFS(sum_range, criteria_range, criteria) with table refs [OK]

Hint: Use SUMIFS with table columns for conditional sums [OK]

Common Mistakes:

• Mixing SUMIF arguments order
• Using SUM instead of SUMIFS for conditions
• Swapping criteria and sum ranges