ABS and MOD in Excel - Cell-by-Cell Formula Trace

Sample Data

Column A has some positive and negative numbers. Column B has positive divisors for MOD function.

Cell	Value
A1	-15
A2	7
A3	-3
B1	4
B2	5
B3	2

Formula Trace

=ABS(A1) + MOD(A2, B2) + ABS(A3) + MOD(A3, B3)

Step 1: ABS(A1)

Step 2: MOD(A2, B2)

Step 3: ABS(A3)

Step 4: MOD(A3, B3)

Step 5: 15 + 2 + 3 + 1

Cell Reference Map

    A    B
1  -15   4
2   7    5
3  -3    2

Formula uses A1, A2, A3, B2, B3 cells.

The formula references cells A1, A2, A3 for values and B2, B3 as divisors for MOD.

Result

    A    B    C
1  -15   4   21
2   7    5
3  -3    2

Cell C1 shows the formula result 21.

The formula result 21 is displayed in cell C1 after calculating ABS and MOD functions.

Sheet Trace Quiz - 3 Questions

What is the result of ABS(A1) if A1 is -15?

A 15

B -15

C 0

D -1

Key Result

ABS returns positive value; MOD returns positive remainder of division.

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