Value fields and aggregation in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate total sales using SUM function

You have a list of sales amounts in cells B2:B10. Which formula correctly calculates the total sales?

A =SUM(B2:B10)

B =SUM(B2+B10)

C =SUM(B2:B10, B11)

D =SUM(B2;B10)

💡 Hint

Use the function that adds all numbers in a range.

The SUM function adds all numbers in the specified range. Option A correctly sums all values from B2 to B10. Option A sums only two cells, not the range. Option A includes an extra cell B11 which may be empty or unrelated. Option A uses a semicolon which is invalid in Excel formulas.

❓ Function Choice

intermediate

Choose the correct aggregation function for average

You want to find the average value of numbers in cells C2:C15. Which function should you use?

A SUM(C2:C15)

B AVERAGE(C2:C15)

C MAX(C2:C15)

D COUNT(C2:C15)

💡 Hint

Think about which function calculates the mean value.

The AVERAGE function calculates the mean of the numbers in the range. SUM adds them, COUNT counts how many numbers there are, and MAX finds the largest number.

🎯 Scenario

advanced

Summing values with conditions using SUMIF

You have sales data in column D and product categories in column C. You want to sum sales only for the category "Electronics". Which formula will do this?

A =SUMIF("Electronics", C2:C20, D2:D20)

B =SUMIF(D2:D20, "Electronics", C2:C20)

C =SUMIF(C2:C20, D2:D20, "Electronics")

D =SUMIF(C2:C20, "Electronics", D2:D20)

💡 Hint

Remember the order: range to check, condition, then sum range.

The SUMIF function syntax is SUMIF(range, criteria, sum_range). Option D correctly sums sales in D2:D20 where the category in C2:C20 equals "Electronics". Other options mix the order or arguments incorrectly.

❓ data_analysis

advanced

Finding the maximum value ignoring blanks

Column E contains sales numbers but some cells are blank. Which formula correctly finds the maximum sales value ignoring blanks?

A =MAX(IF(ISNUMBER(E2:E30), E2:E30))

B =MAX(IF(E2:E30<>"", E2:E30))

C =MAX(E2:E30)

D =MAX(E2:E30, "")

💡 Hint

Use a formula that checks if cells contain numbers before finding max.

Option A uses an array formula with IF(ISNUMBER(...)) to ignore blanks or text. Option A includes blanks but they don't affect max. Option A is an array formula but comparing to empty string may cause errors. Option A is invalid syntax.

🧠 Conceptual

expert

Understanding aggregation behavior with pivot tables

In a pivot table, you add a value field showing sales amounts. By default, what aggregation does Excel apply to this value field?

A Count of sales amounts

B Average of sales amounts

C Sum of sales amounts

D Maximum of sales amounts

💡 Hint

Think about what Excel usually does with numbers in pivot tables.

By default, Excel sums numeric values in pivot table value fields. If the field contains text, it counts instead. So for sales amounts (numbers), the default aggregation is sum.

Practice

1. What does the SUM function do in Excel when applied to a range of numbers?

easy

A. Calculates the average of the numbers in the range

B. Counts how many numbers are in the range

C. Finds the largest number in the range

D. Adds all the numbers in the range together

Solution

1. Step 1: Understand the purpose of SUM

   The SUM function adds all numeric values in the selected range.

2. Step 2: Compare with other functions

   COUNT counts numbers, MAX finds largest, AVERAGE calculates mean, so SUM is addition.

3. Final Answer:

   Adds all the numbers in the range together -> Option D

4. Quick Check:

   SUM = addition [OK]

Hint: SUM always adds numbers, not counts or averages [OK]

Common Mistakes:

• Confusing SUM with COUNT
• Thinking SUM finds max or average
• Using SUM on text values expecting count

2. Which of these formulas correctly counts the number of numeric entries in cells A1 to A5?

easy

A. =COUNT(A1:A5)

B. =MAX(A1:A5)

C. =AVERAGE(A1:A5)

D. =SUM(A1:A5)

Solution

1. Step 1: Identify function purpose

   COUNT counts numeric entries in a range, ignoring text or blanks.

2. Step 2: Check other options

   SUM adds numbers, AVERAGE calculates mean, MAX finds largest number, so only COUNT counts numbers.

3. Final Answer:

   =COUNT(A1:A5) -> Option A

4. Quick Check:

   COUNT counts numbers [OK]

Hint: COUNT counts numbers, SUM adds them [OK]

Common Mistakes:

• Using SUM instead of COUNT to count numbers
• Confusing COUNT with COUNTA (which counts all non-empty)
• Using MAX or AVERAGE expecting count

3. Given the values in cells B1 to B4 as 10, 20, 30, and 40, what is the result of =AVERAGE(B1:B4)?

medium

A. 25

B. 20

C. 30

D. 100

Solution

1. Step 1: Sum the values in B1:B4

   10 + 20 + 30 + 40 = 100

2. Step 2: Divide sum by count of numbers

   There are 4 numbers, so 100 ÷ 4 = 25

3. Final Answer:

   25 -> Option A

4. Quick Check:

   Average = sum/count = 25 [OK]

Hint: Average = total sum divided by count [OK]

Common Mistakes:

• Adding only some numbers
• Dividing by wrong count
• Confusing average with sum or max

4. You want to find the smallest number in range C1:C5, but your formula =MIN(C1:C5 gives an error. What is the fix?

medium

A. Change range to C1-C5

B. Replace MIN with MAX function

C. Add the missing closing parenthesis: =MIN(C1:C5)

D. Use SUM instead of MIN

Solution

1. Step 1: Identify syntax error

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

2. Step 2: Correct the formula

   Adding the closing parenthesis fixes the syntax: =MIN(C1:C5)

3. Final Answer:

   Add the missing closing parenthesis: =MIN(C1:C5) -> Option C

4. Quick Check:

   All functions need matching parentheses [OK]

Hint: Check for missing parentheses in formulas [OK]

Common Mistakes:

• Using dash (-) instead of colon (:) for range
• Replacing MIN with unrelated functions
• Ignoring syntax errors

5. You have sales data in cells D1 to D6: 100, 200, 0, 150, blank, and 300. Which formula correctly calculates the average sales ignoring blank and zero values?

hard

A. =AVERAGE(D1:D6)

B. =SUMIF(D1:D6,">0")/COUNTIF(D1:D6,">0")

C. =SUM(D1:D6)/COUNT(D1:D6)

D. =MAX(D1:D6)

Solution

1. Step 1: Understand the problem

   We want average ignoring zeros and blanks, so only values > 0 count.

2. Step 2: Analyze each formula

   A and C include zeros and blanks; D finds max, not average. B sums values > 0 and divides by count of values > 0, correctly ignoring zeros and blanks.

3. Final Answer:

   =SUMIF(D1:D6,">0")/COUNTIF(D1:D6,">0") -> Option B

4. Quick Check:

   Use SUMIF and COUNTIF to filter values [OK]

Hint: Use SUMIF and COUNTIF to exclude zeros/blanks [OK]

Common Mistakes:

• Using AVERAGE which includes zeros
• Dividing SUM by COUNT including zeros
• Using MAX instead of average