Why formulas automate calculations in Excel - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to add values in cells A1 and B1.

Excel

=A1 [1] B1

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

A-

B+

C*

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using - instead of +

Using * or / which multiply or divide

The plus sign (+) adds the values in A1 and B1.

2fill in blank

medium

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

Excel

=AVERAGE([1])

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

AA1+A5

BA1-B5

CA1:A5

DA1*5

Attempts:

3 left

💡 Hint

Common Mistakes

Using minus or plus signs instead of colon

Using multiplication which is not 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 multiply cell A1 by 10.

Excel

=A1 [1] 10

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

A+

B/

C-

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Using + or - which add or subtract

Using / which divides

The asterisk (*) is the correct operator for multiplication in Excel formulas.

4fill in blank

hard

Fill both blanks to create a formula that sums cells B1 to B5 and then divides by 5.

Excel

=[1]([2])/5

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

ASUM

BAVERAGE

CB1:B5

DA1:A5

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVERAGE function instead of SUM

Using wrong cell ranges

SUM(B1:B5) adds all values in B1 to B5. Dividing by 5 calculates the average manually.

5fill in blank

hard

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

Excel

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

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

AC1:C10

B>

C50

DB1:B10

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong cell range

Using < instead of >

Forgetting quotes around >50 in actual Excel

COUNTIF(C1:C10, ">50") counts cells in C1:C10 with values greater than 50.

Practice

1. Why do formulas in Excel start with an = sign?

easy

A. To tell Excel to calculate the expression

B. To make the cell bold

C. To add a comment in the cell

D. To change the cell color

Solution

1. Step 1: Understand the role of = in Excel

   The = sign tells Excel that the cell contains a formula, not just text or a number.

2. Step 2: Recognize what happens without =

   Without =, Excel treats the input as plain text or number and does not calculate anything.

3. Final Answer:

   To tell Excel to calculate the expression -> Option A

4. Quick Check:

   Formulas start with = to calculate [OK]

Hint: Remember: = means 'calculate this' in Excel [OK]

Common Mistakes:

• Thinking = changes formatting
• Confusing = with comments
• Believing = adds colors

2. Which of these is the correct way to write a formula that adds values in cells A1 and B1?

easy

A. =A1+B1

B. A1+B1

C. =SUM(A1 B1)

D. SUM=A1+B1

Solution

1. Step 1: Check formula syntax for addition

   The correct formula must start with = and use + to add cells: =A1+B1.

2. Step 2: Identify errors in other options

   A1+B1 misses =, =SUM(A1 B1) has wrong syntax for SUM, SUM=A1+B1 misplaces = sign.

3. Final Answer:

   =A1+B1 -> Option A

4. Quick Check:

   Correct formula syntax =A1+B1 [OK]

Hint: Always start formulas with = and use + for addition [OK]

Common Mistakes:

• Omitting the = sign
• Using wrong function syntax
• Placing = inside function name

3. If cell A1 contains 5 and B1 contains 3, what will be the result of the formula =A1*B1?

medium

A. 8

B. Error

C. 53

D. 15

Solution

1. Step 1: Identify the operation in the formula

   The formula uses * which means multiplication.

2. Step 2: Multiply the values in A1 and B1

   5 multiplied by 3 equals 15.

3. Final Answer:

   15 -> Option D

4. Quick Check:

   5 * 3 = 15 [OK]

Hint: Remember * means multiply in Excel formulas [OK]

Common Mistakes:

• Adding instead of multiplying
• Concatenating numbers as text
• Expecting a syntax error

4. A user wrote the formula =SUM(A1:A5 but it shows an error. What is the likely problem?

medium

A. Wrong function name

B. Missing closing parenthesis

C. Cells A1 to A5 are empty

D. Formula should start with + instead of =

Solution

1. Step 1: Check formula syntax for SUM function

   The SUM function requires parentheses around the range, so it must be =SUM(A1:A5).

2. Step 2: Identify missing syntax element

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

3. Final Answer:

   Missing closing parenthesis -> Option B

4. Quick Check:

   SUM needs matching parentheses [OK]

Hint: Always close parentheses in functions [OK]

Common Mistakes:

• Forgetting closing parenthesis
• Using + instead of =
• Assuming empty cells cause errors

5. You want to calculate the total price by multiplying quantity in B2 by price per item in C2. Which formula will update automatically if you change quantity or price?

hard

A. =SUM(B2,C2)

B. B2*C2

C. =B2*C2

D. =B2+C2

Solution

1. Step 1: Choose formula that multiplies quantity and price

   The correct formula multiplies B2 and C2 using =B2*C2.

2. Step 2: Confirm formula updates automatically

   Formulas starting with = recalculate when referenced cells change, so total updates automatically.

3. Final Answer:

   =B2*C2 -> Option C

4. Quick Check:

   Formula with = and * updates automatically [OK]

Hint: Use = and cell references to auto-update calculations [OK]

Common Mistakes:

• Omitting = so no calculation happens
• Using SUM instead of multiplication
• Adding instead of multiplying