Why clean data enables analysis in Google Sheets - Formula Trace Breakdown

Sample Data

This table shows names, ages, and scores of people. Some data is not clean: 'N/A' and 'missing' are text instead of numbers.

Cell	Value
A1	Name
B1	Age
C1	Score
A2	Alice
B2	25
C2	85
A3	Bob
B3	30
C3	90
A4	Charlie
B4	N/A
C4	80
A5	David
B5	28
C5	missing
A6	Eve
B6	22
C6	88

Formula Trace

=AVERAGE(B2:B6)

Step 1: B2:B6 values = [25, 30, "N/A", 28, 22]

Step 2: Ignore non-numeric values in B2:B6

Step 3: Sum numeric values: 25 + 30 + 28 + 22

Step 4: Count numeric values: 4

Step 5: Calculate average: 105 / 4

Cell Reference Map

    A       B       C
1 | Name  | Age   | Score
2 | Alice |  25   |  85  
3 | Bob   |  30   |  90  
4 | Charlie| N/A  |  80  
5 | David |  28   | missing
6 | Eve   |  22   |  88  

Formula references B2:B6 (Age column)

The formula =AVERAGE(B2:B6) uses the Age column values from rows 2 to 6.

Result

    A       B       C       D
1 | Name  | Age   | Score | Avg Age
2 | Alice |  25   |  85   |       
3 | Bob   |  30   |  90   |       
4 | Charlie| N/A  |  80   |       
5 | David |  28   | missing|      
6 | Eve   |  22   |  88   | 26.25 

Cell D6 shows the average age ignoring non-numeric data.

The average age calculated by the formula is 26.25, ignoring the 'N/A' text in the Age column.

Sheet Trace Quiz - 3 Questions

What does the formula =AVERAGE(B2:B6) do with the text 'N/A' in cell B4?

A It treats 'N/A' as zero in the average

B It ignores 'N/A' and calculates average of numbers only

C It causes an error in the formula

D It counts 'N/A' as a number

Key Result

AVERAGE(range) calculates the mean of numeric values, ignoring text or non-numeric cells.