ABS and MOD in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to analyze daily sales differences and categorize sales days based on even or odd day numbers.

📊 Data: You have a table with dates and daily sales amounts, including some days with negative sales differences indicating returns or corrections.

🎯 Deliverable: Create a report that shows the absolute value of sales differences and labels each day as 'Even Day' or 'Odd Day' based on the date.

Sample Data

Date	Sales	Previous Day Sales
2024-06-01	150	120
2024-06-02	130	150
2024-06-03	160	130
2024-06-04	140	160
2024-06-05	170	140
2024-06-06	165	170
2024-06-07	180	165
2024-06-08	175	180

1

Step 1: Calculate the sales difference for each day by subtracting Previous Day Sales from Sales.

=B2 - C2

Expected Result

For 2024-06-01: 150 - 120 = 30

2

Step 2: Calculate the absolute value of the sales difference to show the size of change without negative signs.

=ABS(B2 - C2)

Expected Result

For 2024-06-02: ABS(130 - 150) = 20

3

Step 3: Determine if the day of the month is even or odd using the MOD function on the day number extracted from the date.

=IF(MOD(DAY(A2), 2) = 0, "Even Day", "Odd Day")

Expected Result

For 2024-06-03: DAY=3, MOD(3,2)=1 -> 'Odd Day'

4

Step 4: Fill down the formulas for all rows to complete the report.

Drag formulas from rows 2 to 9

Expected Result

All rows show correct absolute sales differences and day type labels.

Final Result

Date       | Sales | Prev Sales | Sales Diff | Abs Diff | Day Type
-------------------------------------------------------------------
2024-06-01 | 150   | 120        | 30         | 30       | Odd Day
2024-06-02 | 130   | 150        | -20        | 20       | Even Day
2024-06-03 | 160   | 130        | 30         | 30       | Odd Day
2024-06-04 | 140   | 160        | -20        | 20       | Even Day
2024-06-05 | 170   | 140        | 30         | 30       | Odd Day
2024-06-06 | 165   | 170        | -5         | 5        | Even Day
2024-06-07 | 180   | 165        | 15         | 15       | Odd Day
2024-06-08 | 175   | 180        | -5         | 5        | Even Day

✓Absolute sales differences show the size of change regardless of increase or decrease.

✓Even days and odd days are clearly labeled to help analyze sales patterns by day type.

✓Some days have negative sales differences indicating returns or corrections.

Bonus Challenge

Create a new column that flags days with sales differences greater than 20 as 'High Change' and others as 'Normal'.

Show Hint

Use IF function with ABS sales difference: =IF(ABS(B2 - C2) > 20, "High Change", "Normal")

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