ABS and MOD in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ABS function do in Excel?

The ABS function returns the absolute value of a number. It changes negative numbers to positive and leaves positive numbers unchanged.

beginner

How does the MOD function work in Excel?

The MOD function returns the remainder after dividing one number by another. For example, MOD(7, 3) returns 1 because 7 divided by 3 leaves a remainder of 1.

beginner

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

Use =ABS(A1) to get the absolute value of the number in cell A1.

beginner

What is the result of =MOD(10, 4)?

The result is 2 because 10 divided by 4 equals 2 with a remainder of 2.

intermediate

Why would you use ABS and MOD together in a spreadsheet?

You might use ABS to make sure numbers are positive before using MOD to find remainders, especially if you want consistent results regardless of sign.

What does =ABS(-15) return?

A Error

B -15

C 0

D 15

ABS returns the positive value of a number, so ABS(-15) is 15.

What is the result of =MOD(9, 4)?

A 0

B 2

C 1

D 3

9 divided by 4 is 2 with a remainder of 1, so MOD(9,4) returns 1.

Which formula gives the remainder when 20 is divided by 6?

A =MOD(20,6)

B =20-6

C =ABS(20/6)

D =20%6

MOD(20,6) returns the remainder of 20 divided by 6.

If A1 contains -8, what does =ABS(A1) return?

A 8

B -8

C 0

D Error

ABS converts negative numbers to positive, so ABS(-8) is 8.

What does =MOD(15, 5) return?

A 3

B 0

C 5

D 1

15 divided by 5 has no remainder, so MOD(15,5) returns 0.

Explain how the ABS function works and give a real-life example of when you might use it in a spreadsheet.

Think about when you want to ignore negative signs.

Describe the MOD function and how it can help in tasks like scheduling or grouping items.

Think about dividing things into groups or repeating sequences.

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