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")