ABS and MOD in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate the absolute difference

You have two numbers in cells A1 and B1. Which formula correctly calculates the absolute difference between them?

A =ABS(A1-B1)

B =ABS(A1)+ABS(B1)

C =MOD(A1-B1)

D =MOD(A1,B1)

💡 Hint

Think about how to get the positive difference regardless of which number is bigger.

ABS returns the positive value of a number or expression. Using ABS(A1-B1) gives the absolute difference. MOD requires two arguments and returns the remainder, so it is not suitable here.

📊 Formula Result

intermediate

Find the remainder of division

Which formula correctly returns the remainder when the number in A1 is divided by the number in B1?

A =MOD(A1-B1)

B =ABS(A1/B1)

C =MOD(A1,B1)

D =ABS(MOD(A1,B1))

💡 Hint

The MOD function returns the remainder of division.

MOD(A1,B1) returns the remainder when A1 is divided by B1. ABS(A1/B1) returns the absolute value of the division result, not the remainder. MOD with one argument is invalid.

❓ Function Choice

advanced

Choose the correct formula for positive remainder

You want to find the remainder of A1 divided by B1, but always want a positive result even if A1 is negative. Which formula achieves this?

A =MOD(ABS(A1),B1)

B =ABS(MOD(A1,B1))

C =ABS(A1)-B1*INT(ABS(A1)/B1)

D =MOD(A1,B1)

💡 Hint

MOD in Excel always returns a result with the same sign as the divisor (B1).

Excel's MOD function returns a positive remainder if the divisor (B1) is positive, even if the dividend (A1) is negative. Using ABS on MOD or on A1 changes the meaning and can give incorrect results.

🎯 Scenario

advanced

Using ABS and MOD to check even or odd

You want to create a formula that returns TRUE if the number in A1 is odd and FALSE if even. Which formula works correctly?

A =MOD(A1,2)<>0

B =MOD(A1,2)=1

C =MOD(ABS(A1),2)=1

D =ABS(MOD(A1,2))=1

💡 Hint

Remember that MOD returns 0 for even numbers and 1 or -1 for odd numbers depending on sign.

MOD(A1,2) returns 0 for even numbers and 1 or -1 for odd numbers depending on sign of A1. Checking if MOD(A1,2) is not zero (<>0) correctly identifies odd numbers regardless of sign. MOD(A1,2)=1 fails for negative odd numbers.

❓ data_analysis

expert

Analyze formula results with negative numbers

Given the values: A1 = -7, B1 = 3. What is the result of the formula =MOD(A1,B1)?

A 4

B 2

C 0

D -1

💡 Hint

MOD returns a remainder with the same sign as the divisor (B1).

MOD(-7,3) returns 2 because -7 divided by 3 is -3 remainder 2. The remainder is always between 0 and divisor-1 when divisor is positive.