Calculated fields in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to calculate the total price by multiplying quantity in A2 by price in B2.

Google Sheets

=A2[1]B2

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

A/

B+

C-

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Using + instead of * will add values instead of multiplying.

Using / or - will give wrong results.

The multiplication operator * calculates total price by multiplying quantity and price.

2fill in blank

medium

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

Google Sheets

=AVERAGE([1])

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

AA1:A5

BA1-A5

CA1+A5

DA1,A5

Attempts:

3 left

💡 Hint

Common Mistakes

Using a minus sign or comma instead of colon for range.

Adding cells instead of selecting a range.

The range A1:A5 selects all cells from A1 to A5 for averaging.

3fill in blank

hard

Fix the error in the formula to calculate the percentage of B2 over A2.

Google Sheets

=[1]/A2*100

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

AB2*

BA2

CB2

DB2/

Attempts:

3 left

💡 Hint

Common Mistakes

Using A2 in numerator instead of B2.

Adding extra operators causing syntax errors.

The formula divides B2 by A2 and multiplies by 100 to get the percentage.

4fill in blank

hard

Fill both blanks to create a formula that sums values in B2 to B10 only if corresponding A2 to A10 cells equal "Yes".

Google Sheets

=SUMIF([1], [2], B2:B10)

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

AA2:A10

B"No"

C"Yes"

DB2:B10

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong range for condition.

Using "No" instead of "Yes" as condition.

The SUMIF function sums values in B2:B10 where cells in A2:A10 equal "Yes".

5fill in blank

hard

Fill all three blanks to create a formula that counts how many cells in C2:C20 are greater than 50.

Google Sheets

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

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

AC2:C20

B>

C50

D"<50"

Attempts:

3 left

💡 Hint

Common Mistakes

Using separate arguments for operator and number instead of combined string.

Using less than operator instead of greater than.

The COUNTIF function counts cells in C2:C20 where values are greater than 50. The condition is written as a string ">50".

Practice

1. What does a calculated field in Google Sheets do?

easy

A. Deletes data from the spreadsheet

B. Creates new values by using formulas on existing cells

C. Changes the font style of cells

D. Saves the spreadsheet automatically

Solution

1. Step 1: Understand the purpose of calculated fields

   Calculated fields use formulas to create new values based on existing data in cells.

2. Step 2: Compare options with this purpose

   Only Creates new values by using formulas on existing cells describes creating new values using formulas, which matches the definition.

3. Final Answer:

   Creates new values by using formulas on existing cells -> Option B

4. Quick Check:

   Calculated field = new values from formulas [OK]

Hint: Calculated fields always create new values from existing data [OK]

Common Mistakes:

• Confusing formatting changes with calculated fields
• Thinking calculated fields delete data
• Assuming calculated fields save files

2. Which of these formulas correctly calculates the total price if quantity is in A2 and price per item is in B2?

easy

A. =A2*B2

B. =A2+B2

C. =A2-B2

D. =A2/B2

Solution

1. Step 1: Identify the calculation needed

   Total price = quantity x price per item, so multiply A2 by B2.

2. Step 2: Match formula to calculation

   Only formula =A2*B2 multiplies the two cells correctly.

3. Final Answer:

   =A2*B2 -> Option A

4. Quick Check:

   Multiply quantity and price = total price [OK]

Hint: Use * to multiply quantity and price for total [OK]

Common Mistakes:

• Using + instead of * for multiplication
• Dividing instead of multiplying
• Subtracting values instead of multiplying

3. Given the formula =C2*D2+E2 where C2=5, D2=3, and E2=10, what is the result?

medium

A. 25

B. 15

C. 25.0

D. 18

Solution

1. Step 1: Calculate multiplication part

   Multiply C2 and D2: 5 * 3 = 15.

2. Step 2: Add E2 value

   Add 15 + 10 = 25.

3. Final Answer:

   25 -> Option A

4. Quick Check:

   5*3+10 = 25 [OK]

Hint: Multiply first, then add according to formula order [OK]

Common Mistakes:

• Adding before multiplying
• Ignoring order of operations
• Confusing cell values

4. The formula =A2*B2+C2 gives wrong results. Which fix is correct if you want to add B2 and C2 first, then multiply by A2?

medium

A. =A2*B2*C2

B. =(A2*B2)+C2

C. =A2+B2+C2

D. =A2*(B2+C2)

Solution

1. Step 1: Understand the desired calculation

   You want to add B2 and C2 first, then multiply by A2.

2. Step 2: Use parentheses to change order

   Formula =A2*(B2+C2) adds B2 and C2 first, then multiplies by A2.

3. Final Answer:

   =A2*(B2+C2) -> Option D

4. Quick Check:

   Parentheses change order to add first [OK]

Hint: Use parentheses to control calculation order [OK]

Common Mistakes:

• Ignoring parentheses and wrong order
• Adding all values instead of multiplying
• Multiplying all values without addition

5. You have a list of sales in column A and a fixed tax rate of 8% in cell B1. Which formula in column B calculates the total price including tax for each sale in A2, A3, etc.?

hard

A. =A2 + B1

B. =A2 / B1

C. =A2 * (1 + B1)

D. =A2 * B1

Solution

1. Step 1: Understand the calculation for total price with tax

   Total price = sale amount + tax amount. Tax amount = sale x tax rate.

2. Step 2: Write formula combining sale and tax

   Total price = sale x (1 + tax rate). So formula is =A2*(1+B1).

3. Final Answer:

   =A2 * (1 + B1) -> Option C

4. Quick Check:

   Multiply sale by (1 + tax rate) for total [OK]

Hint: Multiply by (1 + tax rate) to add tax [OK]

Common Mistakes:

• Adding tax rate directly instead of multiplying
• Multiplying only by tax rate (missing original amount)
• Dividing by tax rate