Why clean data enables analysis in Google Sheets - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to sum values in cells A1 to A5.

Google Sheets

=SUM([1])

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

AA1+A5

BSUM(A1, A5)

CA1:A5

DA1-A5

Attempts:

3 left

💡 Hint

Common Mistakes

Using plus signs instead of a range.

Trying to sum individual cells inside SUM incorrectly.

The correct way to sum a range of cells from A1 to A5 is to use A1:A5 inside the SUM function.

2fill in blank

medium

Complete the formula to count how many cells in B1 to B10 are not empty.

Google Sheets

=COUNTA([1])

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

AB1-B10

BB1:B10

CB1+B10

DB1;B10

Attempts:

3 left

💡 Hint

Common Mistakes

Using minus or plus signs instead of colon for ranges.

Separating cells with semicolons which is invalid here.

The COUNTA function counts non-empty cells in a range. The range must be written as B1:B10.

3fill in blank

hard

Fix the error in this formula to calculate the average of cells C1 to C5.

Google Sheets

=AVERAGE([1])

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

AC1:C5

BC1-C5

CC1,C5

DC1;C5

Attempts:

3 left

💡 Hint

Common Mistakes

Using minus signs instead of colon for ranges.

Separating cells with commas or semicolons inside the range.

The correct range for the AVERAGE function is C1:C5. Using a colon specifies the continuous range.

4fill in blank

hard

Fill both blanks to create a formula that counts cells in D1 to D10 that are greater than 50.

Google Sheets

=COUNTIF([1], [2])

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

AD1:D10

BD1-D10

C">50"

D>50

Attempts:

3 left

💡 Hint

Common Mistakes

Not putting the condition in quotes.

Using minus instead of colon for the range.

The COUNTIF function needs a range and a condition as a text string. The range is D1:D10 and the condition is ">50" with quotes.

5fill in blank

hard

Fill all three blanks to create a formula that sums values in E1 to E10 only if they are less than 100.

Google Sheets

=SUMIF([1], [2], [3])

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

AE1:E10

B"<100"

DE1-E10

Attempts:

3 left

💡 Hint

Common Mistakes

Using minus instead of colon for ranges.

Not putting the condition in quotes.

Using different ranges for range and sum range incorrectly.

The SUMIF function needs a range, a condition as a text string, and the sum range. Here, the range and sum range are both E1:E10, and the condition is "<100".

Practice

1. Why is it important to clean data before analyzing it in Google Sheets?

easy

A. Because cleaning data makes the sheet look colorful

B. Because cleaning data deletes all the numbers

C. Because clean data helps avoid errors and gives accurate results

D. Because clean data takes more space in the sheet

Solution

1. Step 1: Understand the purpose of data cleaning

   Cleaning data removes mistakes like extra spaces or wrong values that cause errors.

2. Step 2: Connect cleaning to analysis accuracy

   Accurate data means formulas and charts show correct information.

3. Final Answer:

   Because clean data helps avoid errors and gives accurate results -> Option C

4. Quick Check:

   Clean data = Accurate results [OK]

Hint: Clean data avoids errors and improves trust in results [OK]

Common Mistakes:

• Thinking cleaning changes data meaning
• Believing cleaning deletes important data
• Assuming cleaning only changes colors

2. Which formula helps remove extra spaces from text in Google Sheets?

easy

A. =TRIM(A1)

B. =CLEAN(A1)

C. =REMOVE(A1)

D. =SPACES(A1)

Solution

1. Step 1: Identify the function that removes extra spaces

   The TRIM function removes extra spaces before and after text.

2. Step 2: Check other options for correctness

   CLEAN removes non-printable characters, REMOVE and SPACES are not valid functions.

3. Final Answer:

   =TRIM(A1) -> Option A

4. Quick Check:

   TRIM removes spaces = =TRIM(A1) [OK]

Hint: Use TRIM to clean spaces around text quickly [OK]

Common Mistakes:

• Using CLEAN instead of TRIM for spaces
• Typing wrong function names like REMOVE
• Confusing TRIM with other text functions

3. Given this data in A1:A4:
10
20
30
" "
What is the result of =SUM(A1:A4) if the last cell contains a space character?

medium

A. 60

B. Error

C. 30

D. 0

Solution

1. Step 1: Understand how SUM treats spaces

   SUM ignores text and spaces, only adds numbers.

2. Step 2: Add numeric values only

   10 + 20 + 30 = 60; space is ignored.

3. Final Answer:

   60 -> Option A

4. Quick Check:

   SUM ignores spaces = 60 [OK]

Hint: SUM ignores spaces and text, adds only numbers [OK]

Common Mistakes:

• Expecting error due to space
• Counting space as zero
• Confusing text with zero

4. You have this formula:
=A1/B1
But it shows #DIV/0! error when B1 is zero. What is the fix?

medium

A. =IF(B1="", "Error", A1/B1)

B. =IFERROR(A1*B1, "Error")

C. =A1/B1

D. =IFERROR(A1/B1, "Error")

Solution

1. Step 1: Identify the error cause

   #DIV/0! occurs when dividing by zero (B1=0).

2. Step 2: Use IFERROR to handle it

   IFERROR wraps the risky division and returns "Error" if any error occurs.

3. Final Answer:

   =IFERROR(A1/B1, "Error") -> Option D

4. Quick Check:

   IFERROR catches #DIV/0! = "Error" [OK]

Hint: Wrap risky formulas like division in IFERROR to handle errors [OK]

Common Mistakes:

• Checking for empty string "" instead of numeric zero
• Removing error handling entirely
• Multiplying instead of dividing by mistake

5. You have a list with some cells containing extra spaces and some with #N/A errors. Which combination of formulas cleans spaces and replaces errors with zero?

hard

A. =IFERROR(CLEAN(A1), 0)

B. =TRIM(IFERROR(A1, 0))

C. =IF(TRIM(A1) = "", 0, A1)

D. =CLEAN(IFERROR(A1, ""))

Solution

1. Step 1: Replace errors with zero first

   IFERROR(A1, 0) changes errors to 0 so TRIM can work safely.

2. Step 2: Remove extra spaces after error fix

   TRIM removes spaces from the cleaned value.

3. Final Answer:

   =TRIM(IFERROR(A1, 0)) -> Option B

4. Quick Check:

   Fix errors then trim spaces = =TRIM(IFERROR(A1, 0)) [OK]

Hint: Use IFERROR inside TRIM to fix errors then clean spaces [OK]

Common Mistakes:

• Trimming before fixing errors causes errors
• Using CLEAN instead of TRIM for spaces
• Replacing errors with empty text instead of zero