=MOD(A2,B2) returns the remainder after dividing A2 by B2: =MOD(17,5) is 2, because 5 goes into 17 three times with 2 left over. =ABS(A2) returns the number without its sign, so -42 becomes 42.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Number | Divisor | MOD | Times it fits |
| 2 | 17 | 5 | 2 | 3 |
| 3 | 20 | 5 | 0 | 4 |
| 4 | 7 | 2 | 1 | 3 |
| 5 | 100 | 7 | 2 | 14 |
| 6 | -3 | 2 | 1 | -1 |
| 7 | 3 | -2 | -1 | -1 |
Column D shows the other half of the division: QUOTIENT returns the whole number of times the divisor fits, so 17 is 3 times 5 plus 2. Change B2 to 0 and MOD returns #DIV/0!, just as a division by zero does.
MOD syntax
=MOD(number, divisor)
MOD works on decimals too: =MOD(7.5,2) is 1.5, and =MOD(A2,1) returns only the decimal part of a number, such as 0.75 from 3.75. For dates with times, =MOD(A2,1) keeps the time and drops the date.
MOD with negative numbers
Rows 6 and 7 of the sheet above show Excel's rule: the result has the sign of the divisor. =MOD(-3,2) is 1, not -1, and =MOD(3,-2) is -1. Excel calculates MOD as number - divisor * INT(number / divisor), and INT always rounds down.
This is where a spreadsheet and code disagree. In JavaScript, Java, C and C# -3 % 2 is -1; Python's -3 % 2 is 1, like Excel. Google Sheets follows Excel. If you need the sign of the number instead, use =A2-B2*TRUNC(A2/B2).
Even or odd, and every other row
A number is even when MOD(number,2) is 0. That gives an "Even" or "Odd" label, and with ROW it gives a rule that shades every other row. Excel also has ISEVEN and ISODD, which return TRUE or FALSE directly.
| A | B | C | |
|---|---|---|---|
| 1 | Order | Qty | Even or odd |
| 2 | 1001 | 4 | Even |
| 3 | 1002 | 7 | Odd |
| 4 | 1003 | 10 | Even |
| 5 | 1004 | 3 | Odd |
| 6 | 1005 | 8 | Even |
| 7 | 1006 | 5 | Odd |
The conditional formatting rule =MOD(ROW($A2),2)=0 highlights rows 2, 4 and 6, the even row numbers. In Excel this is Home > Conditional Formatting > New Rule > "Use a formula to determine which cells to format", and the shorter =MOD(ROW(),2)=0 works there too. Change the 2 to 3 to shade every third row.
Every nth row: sum every third value
MOD(ROW()-ROW(first),n)=0 is TRUE for the first row of the range and then every nth row after it. Inside SUMPRODUCT it picks every third value out of a column, for example the last month of each quarter.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | Every 3rd | ||
| 2 | Jan | 120 | 310 | ||
| 3 | Feb | 135 | |||
| 4 | Mar | 150 | |||
| 5 | Apr | 110 | |||
| 6 | May | 125 | |||
| 7 | Jun | 160 |
The offsets of the rows are 0 to 5, and =2 keeps offsets 2 and 5: March and June, 150 plus 160. Change =2 to =0 to take January and April instead.
Convert minutes to hours and minutes
MOD and integer division split a total into units: hours and leftover minutes, weeks and leftover days, boxes and loose items.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Minutes | Hours | Minutes left | Label |
| 2 | 135 | 2 | 15 | 2 h 15 min |
| 3 | 59 | 0 | 59 | 0 h 59 min |
| 4 | 240 | 4 | 0 | 4 h 0 min |
| 5 | 1000 | 16 | 40 | 16 h 40 min |
1000 minutes is 16 hours and 40 minutes. For actual time values (8:30, 17:45) and shifts that cross midnight, =MOD(end-start,1) is the standard formula; see time calculations.
ABS: the difference between two numbers
=ABS(number) drops the minus sign. Its main use is the size of a difference when you do not care which value is larger: how far each forecast was from the actual result, or whether a measurement is within a tolerance.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Week | Forecast | Actual | Off by | Within 10? |
| 2 | W1 | 120 | 112 | 8 | TRUE |
| 3 | W2 | 95 | 109 | 14 | FALSE |
| 4 | W3 | 140 | 138 | 2 | TRUE |
| 5 | W4 | 80 | 93 | 13 | FALSE |
| 6 | W5 | 110 | 104 | 6 | TRUE |
B2-C2 is 8 for W1 and -14 for W2; ABS turns both into a distance. To add up the distances, =SUMPRODUCT(ABS(B2:B6-C2:C6)) works in every version of Excel; here it gives 43, the total of column D. Dividing that by the count gives the mean absolute error.
Try it: MOD and ABS
| A | B | C | |
|---|---|---|---|
| 1 | Eggs | Per carton | Left over |
| 2 | 350 | 12 |
Your turn: In C2, calculate how many eggs are left over after packing the eggs in A2 into full cartons of the size in B2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | City | Morning | Evening | Change |
| 2 | Oslo | 14 | 6 |
Your turn: In D2, show how many degrees the temperature changed between B2 and C2, as a positive number whichever reading is higher.
MOD vs QUOTIENT vs INT
| You want | Formula | 17 and 5 give | -17 and 5 give |
|---|---|---|---|
| Remainder | =MOD(A2,B2) | 2 | 3 |
| Whole times it fits, toward zero | =QUOTIENT(A2,B2) | 3 | -3 |
| Whole times it fits, rounded down | =INT(A2/B2) | 3 | -4 |
| Exact division | =A2/B2 | 3.4 | -3.4 |
For positive numbers, QUOTIENT and INT(A2/B2) agree and B2*INT(A2/B2)+MOD(A2,B2) always rebuilds the number. With a negative number only the INT pair adds back up: -4 times 5 plus 3 is -17. Mixing QUOTIENT with MOD on negative numbers is the usual cause of an off-by-one in a schedule or a split.
Frequently Asked Questions
What does MOD do in Excel?
It returns the remainder of a division: =MOD(17,5) is 2, because 5 goes into 17 three times with 2 left over. If the number divides evenly, MOD returns 0.
How do I check if a number is even in Excel?
Use =MOD(A2,2)=0, which is TRUE for even numbers, or =ISEVEN(A2). In a label: =IF(MOD(A2,2)=0,"Even","Odd").
Why does MOD return a positive number for a negative value?
Excel's MOD takes the sign of the divisor: =MOD(-3,2) is 1 and =MOD(3,-2) is -1. Many programming languages give -1 for -3 % 2, so results can differ from code.
How do I get the absolute value in Excel?
Use =ABS(A2): -42 becomes 42 and 42 stays 42. To get the size of a difference without its sign, use =ABS(B2-C2).
How do I sum absolute values in Excel?
Use =SUMPRODUCT(ABS(A2:A7)), which works in every version. In Excel 365 and 2021 =SUM(ABS(A2:A7)) also works.
