Why statistical functions summarize data in Excel - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to calculate the average of numbers in cells A1 to A5.

Excel

=AVERAGE([1])

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

ACOUNT(A1:A5)

BSUM(A1:A5)

CA1+A5

DA1:A5

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM or COUNT inside AVERAGE instead of a range.

Adding cells instead of using a range.

The AVERAGE function needs a range like A1:A5 to calculate the mean of those cells.

2fill in blank

medium

Complete the formula to find the highest value in cells B2 to B10.

Excel

=MAX([1])

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

AB2-B10

BB2:B10

CMAX(B2:B10)

DB2,B10

Attempts:

3 left

💡 Hint

Common Mistakes

Using a dash instead of a colon for the range.

Passing multiple arguments separated by commas incorrectly.

The MAX function requires a range like B2:B10 to find the largest number in those cells.

3fill in blank

hard

Fix the error in the formula to count how many cells in C1 to C8 contain numbers.

Excel

=COUNT([1])

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

AC1:C8

BC1-C8

CCOUNT(C1:C8)

DC1,C8

Attempts:

3 left

💡 Hint

Common Mistakes

Using a dash instead of a colon for the range.

Including the COUNT function inside itself.

The COUNT function needs a proper range like C1:C8 to count numeric cells correctly.

4fill in blank

hard

Fill both blanks to create a formula that calculates the median of values in D1 to D12.

Excel

=MEDIAN([1]:[2])

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

AD1

B:

CD12

D,

Attempts:

3 left

💡 Hint

Common Mistakes

Using a comma instead of a colon to separate cells.

Only putting one cell reference without a range.

So, D1, :, and D12 are needed to form the range.

5fill in blank

hard

Fill all four blanks to create a formula that counts how many cells in E1 to E15 are greater than 10.

Excel

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

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

AE1

B:

C">10"

DE15

Attempts:

3 left

💡 Hint

Common Mistakes

Using commas instead of a colon for the range.

Not putting the condition in quotes.

Using the wrong comparison operator.

Practice

1. Why do we use statistical functions like AVERAGE and SUM in Excel?

easy

A. To change the color of cells automatically

B. To quickly get a summary like average or total from many numbers

C. To create charts and graphs

D. To sort data alphabetically

Solution

1. Step 1: Understand the purpose of statistical functions

   Functions like AVERAGE and SUM help summarize data by calculating key values quickly.

2. Step 2: Identify the correct use case

   Changing colors, creating charts, or sorting are not done by these functions; they focus on summarizing numbers.

3. Final Answer:

   To quickly get a summary like average or total from many numbers -> Option B

4. Quick Check:

   Statistical functions summarize data = To quickly get a summary like average or total from many numbers [OK]

Hint: Statistical functions give quick summaries, not formatting [OK]

Common Mistakes:

• Confusing statistical functions with formatting tools
• Thinking they create charts directly
• Assuming they sort data

2. Which of these is the correct syntax to find the average of numbers in cells A1 to A5 in Excel?

easy

A. =AVERAGE(A1:A5)

B. =SUM(A1-A5)

C. =AVERAGE{A1:A5}

D. =AVG(A1:A5)

Solution

1. Step 1: Recall the correct function name and syntax

   The function to find average is AVERAGE and it uses parentheses with a colon to specify a range.

2. Step 2: Check each option

   =AVERAGE(A1:A5) uses correct function and range syntax. =SUM(A1-A5) uses SUM but with wrong range format. =AVERAGE{A1:A5} uses braces which is invalid. =AVG(A1:A5) uses AVG which is not an Excel function.

3. Final Answer:

   =AVERAGE(A1:A5) -> Option A

4. Quick Check:

   Correct average syntax = =AVERAGE(A1:A5) [OK]

Hint: Use =AVERAGE(range) with colon for ranges [OK]

Common Mistakes:

• Using braces {} instead of parentheses ()
• Using wrong function names like AVG
• Using minus sign instead of colon in range

3. Given the numbers in cells A1 to A4 as 10, 20, 30, and 40 respectively, what will =MAX(A1:A4) return?

medium

A. 10

B. 25

C. 40

D. 100

Solution

1. Step 1: Understand what MAX function does

   The MAX function returns the largest number in the given range.

2. Step 2: Identify the largest number in A1:A4

   The numbers are 10, 20, 30, and 40. The largest is 40.

3. Final Answer:

   40 -> Option C

4. Quick Check:

   MAX of 10,20,30,40 = 40 [OK]

Hint: MAX returns the highest number in the range [OK]

Common Mistakes:

• Confusing MAX with SUM or AVERAGE
• Adding numbers instead of finding max
• Choosing the smallest number by mistake

4. The formula =COUNT(A1:A5) returns 0 even though cells A1 to A5 contain numbers. What is the likely problem?

medium

A. Cells contain text, not numbers

B. COUNT function only counts blank cells

C. Formula syntax is incorrect

D. Cells are formatted as dates

Solution

1. Step 1: Understand what COUNT does

   COUNT counts only cells with numbers, ignoring text and blanks.

2. Step 2: Analyze why COUNT returns 0

   If cells look like numbers but COUNT returns 0, they are likely stored as text, not numeric values.

3. Final Answer:

   Cells contain text, not numbers -> Option A

4. Quick Check:

   COUNT counts numbers only = Cells contain text, not numbers [OK]

Hint: COUNT counts numbers, not text or blanks [OK]

Common Mistakes:

• Assuming COUNT counts all non-empty cells
• Thinking COUNT counts blank cells
• Believing formatting affects COUNT

5. You have sales data in cells B2:B10. You want to find the average sales but only for values greater than 100. Which formula correctly summarizes this data?

hard

A. =AVERAGE(B2:B10)

B. =SUM(B2:B10)/COUNT(B2:B10)

C. =COUNTIF(B2:B10, ">100")

D. =AVERAGEIF(B2:B10, ">100")

Solution

1. Step 1: Understand the goal

   You want the average of only those sales greater than 100, not all sales.

2. Step 2: Identify the correct function

   AVERAGEIF calculates average with a condition. =AVERAGEIF(B2:B10, ">100") uses it correctly with ">100" condition.

3. Step 3: Check other options

   =AVERAGE(B2:B10) averages all values. =SUM(B2:B10)/COUNT(B2:B10) manually averages all values. =COUNTIF(B2:B10, ">100") counts values >100 but does not average.

4. Final Answer:

   =AVERAGEIF(B2:B10, ">100") -> Option D

5. Quick Check:

   Conditional average needs AVERAGEIF = =AVERAGEIF(B2:B10, ">100") [OK]

Hint: Use AVERAGEIF for averages with conditions [OK]

Common Mistakes:

• Using AVERAGE without condition
• Confusing COUNTIF with AVERAGEIF
• Trying to manually calculate average with SUM/COUNT ignoring condition