ABS and MOD in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to get the absolute value of cell A1.

Excel

= [1](A1)

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

AMOD

BSUM

CABS

DROUND

Attempts:

3 left

💡 Hint

Common Mistakes

Using MOD instead of ABS

Using SUM which adds numbers

Using ROUND which rounds numbers

The ABS function returns the absolute value of a number, removing any negative sign.

2fill in blank

medium

Complete the formula to find the remainder when cell B1 is divided by 3.

Excel

= [1](B1, 3)

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

ASUM

BMOD

CROUND

DABS

Attempts:

3 left

💡 Hint

Common Mistakes

Using ABS which returns absolute value

Using ROUND which rounds numbers

Using SUM which adds numbers

The MOD function returns the remainder after division of one number by another.

3fill in blank

hard

Fix the error in the formula to correctly calculate the absolute value of cell C1.

Excel

= [1](C1)

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

AABS

BMOD

CSUM

DAVERAGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using MOD which returns remainder

Using SUM which adds numbers

Using AVERAGE which calculates mean

ABS is the correct function to get absolute value, not MOD or others.

4fill in blank

hard

Fill both blanks to create a formula that returns the absolute value of the remainder when D1 is divided by 4.

Excel

= [1]([2](D1, 4))

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

AABS

BMOD

CSUM

DROUND

Attempts:

3 left

💡 Hint

Common Mistakes

Reversing ABS and MOD

Using SUM or ROUND instead of MOD

Not using ABS to remove negative sign

First, MOD(D1, 4) finds the remainder. Then ABS(...) makes sure the result is positive.

5fill in blank

hard

Fill all three blanks to create a formula that calculates the absolute value of the remainder when the sum of E1 and E2 is divided by 5.

Excel

= [1]([2](E1 [3] E2, 5))

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

AABS

BMOD

C+

D-

Attempts:

3 left

💡 Hint

Common Mistakes

Using '-' instead of '+'

Reversing ABS and MOD

Not using ABS to ensure positive result

First, add E1 and E2 with '+'. Then MOD(..., 5) finds remainder. Finally, ABS(...) makes it positive.

Practice

1. What does the Excel function ABS(-15) return?

easy

A. 15

B. -15

C. 0

D. Error

Solution

1. Step 1: Understand the ABS function

   ABS returns the positive value of a number, removing any minus sign.

2. Step 2: Apply ABS to -15

   ABS(-15) removes the minus sign and returns 15.

3. Final Answer:

   15 -> Option A

4. Quick Check:

   ABS(-15) = 15 [OK]

Hint: ABS always gives positive distance from zero [OK]

Common Mistakes:

• Thinking ABS keeps the minus sign
• Confusing ABS with MOD
• Expecting zero for negative input

2. Which of these is the correct syntax to get the remainder of 17 divided by 5 in Excel?

easy

A. =MOD(5, 17)

B. =MOD(17-5)

C. =ABS(17, 5)

D. =MOD(17, 5)

Solution

1. Step 1: Recall MOD function syntax

   MOD(number, divisor) returns remainder of number divided by divisor.

2. Step 2: Apply syntax to 17 and 5

   MOD(17, 5) returns remainder when 17 is divided by 5.

3. Final Answer:

   =MOD(17, 5) -> Option D

4. Quick Check:

   MOD(17, 5) = 2 [OK]

Hint: MOD(number, divisor) order matters: number first [OK]

Common Mistakes:

• Swapping arguments in MOD
• Using ABS instead of MOD
• Missing second argument in MOD

3. What is the result of the formula =MOD(ABS(-23), 7) in Excel?

medium

A. 2

B. -2

C. 3

D. Error

Solution

1. Step 1: Calculate ABS(-23)

   ABS(-23) removes minus sign, result is 23.

2. Step 2: Calculate MOD(23, 7)

   23 divided by 7 is 3 remainder 2, so MOD(23,7) = 2.

3. Final Answer:

   2 -> Option A

4. Quick Check:

   MOD(ABS(-23),7) = 2 [OK]

Hint: Calculate ABS first, then MOD for remainder [OK]

Common Mistakes:

• Forgetting ABS removes minus
• Calculating MOD with negative number
• Mixing quotient with remainder

4. The formula =MOD(A1, 4) returns 0 for cell A1 containing -8. Why is this incorrect and how to fix it?

medium

A. MOD syntax error; fix with =MOD(A1;4)

B. MOD works fine; no fix needed

C. MOD returns negative remainder; fix with =MOD(ABS(A1),4)

D. MOD returns wrong result for negatives; fix with =MOD(A1+4,4)

Solution

1. Step 1: Understand MOD with negative numbers

   MOD in Excel returns remainder with same sign as divisor, so MOD(-8,4) = 0 is correct but sometimes unexpected.

2. Step 2: Fix negative input for positive remainder

   Using ABS removes the negative sign and ensures a positive remainder: MOD(ABS(A1),4) returns 0 for A1=-8.

3. Final Answer:

   MOD returns negative remainder; fix with =MOD(ABS(A1),4) -> Option C

4. Quick Check:

   MOD(-8,4) = 0 but may be unexpected; MOD(ABS(-8),4) = 0 [OK]

Hint: Use ABS to get positive remainder when input is negative [OK]

Common Mistakes:

• Using ABS removes sign but changes meaning
• Wrong syntax with semicolon
• Assuming MOD always positive

5. You want to group a list of numbers in column A into groups of 3 using Excel formulas. Which formula in cell B1 correctly assigns group numbers starting from 1 for A1?

hard

A. =MOD(A1,3)+1

B. =INT((A1-1)/3)+1

C. =ABS(A1)/3

D. =MOD(A1-1,3)+1

Solution

1. Step 1: Understand grouping by 3

   Grouping means numbers 1-3 in group 1, 4-6 in group 2, etc. We divide (A1-1) by 3 and take integer part.

2. Step 2: Calculate group number formula

   INT((A1-1)/3)+1 gives group 1 for A1=1, group 2 for A1=4, etc.

3. Final Answer:

   =INT((A1-1)/3)+1 -> Option B

4. Quick Check:

   Group 1 for 1-3, group 2 for 4-6 [OK]

Hint: Use INT((number-1)/group_size)+1 for grouping [OK]

Common Mistakes:

• Using MOD for group number
• Dividing without INT
• Using ABS which is unrelated