Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Math

MOD

REST in Dutch Excel

Returns what is left over after dividing one number by another, which makes it the tool for anything that repeats in cycles.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: D2 holds the formula
Row	A	B	C	D
1	Entry no.	Amount	Unit	Remainder
2	25-0412	€12,500.00€ 12.500,00	1,0001.000	500500=MOD(B2, C2)=MOD(B2; C2)
3	25-0413	€48,000.00€ 48.000,00	1,0001.000
4	25-0414	€7,342.00€ 7.342,00	1,0001.000
5	25-0415	€30,250.00€ 30.250,00	500500

Result: 500

=MOD(B2, C2)

=MOD(B2; C2)

What MOD does and when to use it

What it does: it returns the remainder after a division, for example the 6 bottles left over when 150 bottles go into crates of 24.

When to use it: for anything that repeats in a cycle: every nth row, a rota of three people, hours past midnight, even or odd numbers, check digits. For the whole number of times the divisor fits, use INT or QUOTIENT instead.

How Excel evaluates it

Excel evaluates number and divisor first. If divisor is 0 it stops with #DIV/0!. Otherwise it calculates number - divisor × INT(number / divisor): it finds how many whole divisors fit, rounding that count down, and subtracts them.

Because INT rounds down rather than toward zero, the result always has the sign of the divisor: MOD(-3, 24) is 21, not -3. That is exactly what a clock needs, since 3 hours before 00:00 is 21:00.

Watch out: Excel gives the remainder the sign of the divisor.

Mental model

A clock face with as many positions as the divisor: you walk number steps around it, forward for positive and backward for negative, and MOD is where you end up.

Syntax

Official MOD documentation

MOD(number, divisor)

MOD(number; divisor)

Arguments
Argument	Type	Meaning
`number`	Required	The number you divide; its remainder is what you get back.
`divisor`	Required	The cycle length; the result lies between 0 and this, with its sign.

Return value:The remainder of number divided by divisor, with the sign of divisor

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Round amounts in journal entries

Journal entry testing on the auditfile of Veldhuis Installatie BV: entries with round amounts deserve a closer look. For each entry you check how much is left over after dividing the amount by a round unit, 1000 for most entries. A remainder of 0 means the amount is a whole multiple of that unit.

Sheet excerpt: D2 holds the formula
Row	A	B	C	D
1	Entry no.	Amount	Unit	Remainder
2	25-0412	€12,500.00€ 12.500,00	1,0001.000	500500=MOD(B2, C2)=MOD(B2; C2)
3	25-0413	€48,000.00€ 48.000,00	1,0001.000
4	25-0414	€7,342.00€ 7.342,00	1,0001.000
5	25-0415	€30,250.00€ 30.250,00	500500
6

Formula cell (D2)Input

Formula in D2

=MOD(B2, C2)

=MOD(B2; C2)

Result: 500500

Step by step

Excel reads number from B2 (12500) and divisor from C2 (1000). The divisor is not 0, so it can continue.
It works out how many whole units fit: 12500 / 1000 = 12.5, rounded down to 12.
It subtracts those units: 12500 - 12 × 1000 = 12500 - 12000 = 500. D2 shows 500: the amount is not round.
Copy down. D3: 48000 / 1000 = 48 exactly, so 48000 - 48000 = 0: a round amount, to follow up. D4: 7342 / 1000 = 7.342, 7 whole units, 7342 - 7000 = 342.
D5 uses unit 500: 30250 / 500 = 60.5, 60 whole units, 30250 - 30000 = 250. Change C5 to 250 and D5 becomes 0, because 30250 is exactly 121 × 250.

Watch out: A row without a unit in column C gives #DIV/0!, because an empty cell counts as 0.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

A remainder is by definition what does not make up a whole unit, so it is always smaller than the unit: 0 to 999 for 1000. The number of whole units and the remainder together rebuild the amount: 12 × 1000 + 500 = 12500. A remainder of 0 is therefore exactly the test for 'a multiple of the unit'.

More to watch out for

Credit amounts are often negative in the export. MOD(-7342, 1000) returns 658, not 342, because the result takes the sign of the divisor. Test on ABS(B2) when you compare debit and credit lines.

Variations

=MOD(B2, C2) = 0

=MOD(B2; C2) = 0

Turns the remainder into a flag: TRUE for D3, the only round amount at unit 1000. Filter on TRUE to list the entries for follow-up.

Intermediate

Fiscal period for a client with a broken financial year

Veldhuis Installatie BV has a financial year that starts on 1 April. The auditfile export gives each posting its calendar month, but the client's period reports and your cut-off test work with fiscal periods 1 to 12. Subtracting the start month gives 0 for April and negative numbers for January to March; the formula has to wrap them back into 1 to 12, and it is copied down the list.

Sheet excerpt: C2 holds the formula
Row	A	B	C	E	F
1	Entry no.	Calendar month	Fiscal period	Year starts in month	44
2	25-0102	22	1111=MOD(B2 - $F$1, 12) + 1=MOD(B2 - $F$1; 12) + 1
3	25-0457	44
4	25-0881	1212
5	25-0123	33
6	25-0690	99
7

Formula cell (C2)Input

Formula in C2

=MOD(B2 - $F$1, 12) + 1

=MOD(B2 - $F$1; 12) + 1

Result: 1111

Step by step

Excel evaluates number first: B2 - $F$1 = 2 - 4 = -2. February is two months before the start of the financial year, and no period is numbered -2.
MOD(-2, 12) finds how many whole years of 12 fit: -2 / 12 = -0.1667, rounded down to -1.
It subtracts them: -2 - 12 × (-1) = -2 + 12 = 10. The remainder takes the sign of the divisor, 12, so it is positive. Adding 1 gives 10 + 1 = 11: February is fiscal period 11.
The + 1 is needed because MOD counts from 0 while periods count from 1. Without it April would be period 0.
Copy down; $F$1 stays fixed. Four examples:
- April: 4 - 4 = 0, MOD(0, 12) = 0, period 1
- December: 12 - 4 = 8, period 9
- March: 3 - 4 = -1, MOD(-1, 12) = 11, period 12
- September: 9 - 4 = 5, period 6
Change F1 to 1 (a calendar financial year) and February becomes MOD(2 - 1, 12) + 1 = 1 + 1 = 2: the fiscal period equals the calendar month.

Watch out: MOD gives the period, not the financial year. January 2026 is period 10, but it belongs to the year that started on 1 April 2025. If you filter on year as well, derive the year separately.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

Months form a cycle of 12, and MOD with divisor 12 maps every whole number, positive or negative, onto 0 to 11. Because the divisor is positive, the months before the start month come out as the end of the financial year without any IF. The absolute $F$1 keeps the start month in one cell, so the same formula serves a client with another year-end.

More to watch out for

The auditfile may already hold a period column built on the client's own calendar, including special periods such as 13 for closing entries. Compare your result with it first, and do not overwrite it.

Variations

=IF(B2 >= $F$1, B2 - $F$1 + 1, B2 - $F$1 + 13)

=IF(B2 >= $F$1; B2 - $F$1 + 1; B2 - $F$1 + 13)

The same result without MOD: 11 for February. Longer and more prone to mistakes, but it shows what the wrap does.

Advanced

Distribute sample invoices over the audit team

You selected six sales invoices from the XAF export of Veldhuis Installatie BV for vouching. Three team members share the work, taking one invoice each in turn. You want one formula that fills in the name for each invoice and keeps cycling through the three names, however long the sample grows.

Sheet excerpt: C2 holds the formula
Row	A	B	C	E
1	Invoice no.	Amount	Tested by	Team
2	VF-25-1041	€18,450.00€ 18.450,00	Sanne de Vries=INDEX($E$2:$E$4, MOD(ROW(A2) - ROW($A$2), 3) + 1)=INDEX($E$2:$E$4; MOD(ROW(A2) - ROW($A$2); 3) + 1)	Sanne de Vries
3	VF-25-1077	€2,310.00€ 2.310,00		Daan Bakker
4	VF-25-1102	€9,875.00€ 9.875,00		Fatma Yilmaz
5	VF-25-1130	€64,200.00€ 64.200,00
6	VF-25-1158	€5,120.00€ 5.120,00
7	VF-25-1183	€31,790.00€ 31.790,00
8

Formula cell (C2)Input

Formula in C2

=INDEX($E$2:$E$4, MOD(ROW(A2) - ROW($A$2), 3) + 1)

=INDEX($E$2:$E$4; MOD(ROW(A2) - ROW($A$2); 3) + 1)

Result: Sanne de Vries

Step by step

Excel evaluates the innermost functions first. ROW(A2) is 2 and ROW($A$2) is 2, so the difference is 0: this is the first invoice of the sample.
MOD(0, 3) is 0. Adding 1 gives position 1, because INDEX counts from 1 while MOD counts from 0.
INDEX($E$2:$E$4, 1) returns the first name in the team list: Sanne de Vries, in C2.
Copy down. In C3 the difference is 1, MOD(1, 3) = 1, position 2: Daan Bakker. In C4 it is 2, MOD(2, 3) = 2, position 3: Fatma Yilmaz.
In C5 the difference is 3, and MOD(3, 3) = 0: the cycle starts again at position 1, Sanne de Vries for invoice VF-25-1130. C6 gives Daan Bakker and C7 Fatma Yilmaz.
Add a fourth person in E5, change $E$2:$E$4 to $E$2:$E$5 and the 3 to 4, and the work is spread over four people without touching the invoice list.

Watch out: MOD(ROW(A2), 3) + 1 without subtracting the first row looks equivalent but starts at position 3: Fatma Yilmaz gets the first invoice. No error appears, the whole distribution is shifted, and inserting a row above the table shifts it again.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

ROW minus the ROW of the first data cell is a counter that starts at 0 for the first invoice and goes up by 1 per row. MOD with the team size folds that counter into 0, 1, 2, 0, 1, 2, and the + 1 converts it into the 1-based positions INDEX expects. The absolute $A$2 anchors the counter, so inserting rows above the table does not shift the distribution.

More to watch out for

Change the team size in the range but forget the 3, and MOD keeps cycling through three names: the fourth person never gets an invoice, without any warning. Replace the 3 with ROWS($E$2:$E$4) so both follow the same range.

Variations

=INDEX($E$2:$E$4, MOD(ROW(A2) - ROW($A$2), ROWS($E$2:$E$4)) + 1)

=INDEX($E$2:$E$4; MOD(ROW(A2) - ROW($A$2); ROWS($E$2:$E$4)) + 1)

The team size is read from the range, so adding a name to the team list is enough. The result for C2 is the same: Sanne de Vries.

=MOD(ROW(A2) - ROW($A$2), 3) = 0

=MOD(ROW(A2) - ROW($A$2); 3) = 0

Cycle of 3 as a test: TRUE for the 1st, 4th, 7th invoice, the basis of a systematic selection of every third item from a population.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Validate supplier account numbers with an eleven-test

In the creditors master file of Veldhuis Installatie BV some suppliers still have a 9-digit legacy account number that was built to pass the eleven-test: multiply the digits by 9, 8, 7 down to 1, add the products, and the total must be divisible by 11. A typo or two swapped digits then fails. Before you test payments you want to flag every number that fails, including numbers with the wrong length.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Account no.	Supplier	Valid number
2	123456789	Bakker Staalhandel BV	TRUE=LET( txt, A2, digits, --MID(txt, SEQUENCE(9), 1), weights, SEQUENCE(9, 1, 9, -1), IF(LEN(txt) <> 9, FALSE, MOD(SUMPRODUCT(digits, weights), 11) = 0) )=LET( txt; A2; digits; --MID(txt; SEQUENCE(9); 1); weights; SEQUENCE(9; 1; 9; -1); IF(LEN(txt) <> 9; FALSE; MOD(SUMPRODUCT(digits; weights); 11) = 0) )
3	123456798	De Groot Transport BV
4	538469994	Smit Kantoorinrichting BV
5	12345678	Jansen Schoonmaak BV
6	853194130	Van Dijk IT Services BV
7	853194180	Peters Verpakkingen BV
8

Formula cell (C2)InputNote: The account numbers are stored as text, so a number that starts with 0 keeps its leading zero.

Formula in C2

=LET(
  txt, A2,
  digits, --MID(txt, SEQUENCE(9), 1),
  weights, SEQUENCE(9, 1, 9, -1),
  IF(LEN(txt) <> 9, FALSE, MOD(SUMPRODUCT(digits, weights), 11) = 0)
)

=LET(
  txt; A2;
  digits; --MID(txt; SEQUENCE(9); 1);
  weights; SEQUENCE(9; 1; 9; -1);
  IF(LEN(txt) <> 9; FALSE; MOD(SUMPRODUCT(digits; weights); 11) = 0)
)

Result: TRUE

Step by step

LET names the number: txt is A2, 123456789. digits takes MID(txt, SEQUENCE(9), 1): SEQUENCE(9) is {1; 2; ...; 9}, so MID pulls out each character, and the double minus turns the texts into numbers {1; 2; 3; 4; 5; 6; 7; 8; 9}.
weights is SEQUENCE(9, 1, 9, -1): 9 rows, 1 column, starting at 9, step -1, so {9; 8; 7; 6; 5; 4; 3; 2; 1}.
IF first checks the length. LEN(txt) is 9, so it moves to the check. SUMPRODUCT(digits, weights) = 1×9 + 2×8 + 3×7 + 4×6 + 5×5 + 6×4 + 7×3 + 8×2 + 9×1 = 9 + 16 + 21 + 24 + 25 + 24 + 21 + 16 + 9 = 165.
MOD(165, 11): 165 / 11 = 15 exactly, so the remainder is 0, and 0 = 0 is TRUE. C2 shows TRUE: the number is valid.
Copy down. A3 has the last two digits swapped: the total becomes 166, MOD(166, 11) = 1, so FALSE. A4 totals 264 and A6 totals 209, both multiples of 11, so TRUE. A7 has an 8 instead of a 3 in position 8: 209 + 5 × 2 = 219, MOD(219, 11) = 10, FALSE.
Row 5 holds 12345678, 8 characters. LEN is 8, so IF returns FALSE without using digits. Without that guard, MID would return empty text for position 9, the double minus would turn it into #VALUE!, and the cell would show an error instead of a clear FALSE.

Watch out: The mirror-image mistake is weights in the wrong direction, SEQUENCE(9), which gives 1 to 9 instead of 9 to 1. For 123456789 the total becomes 285 and MOD(285, 11) is 10, so a valid number is rejected, and some invalid numbers will pass.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

MOD with 11 reduces the weighted total to a remainder from 0 to 10, and a valid number is one whose remainder is 0. Because each position has a different weight and 11 is prime, changing one digit or swapping two neighbours always changes the remainder, which is why the test catches the usual typing errors. LET splits the work into named arrays, so the weights can be checked by eye.

Do not use this construction for long numbers such as IBANs: Excel keeps only 15 significant digits, so a 20-digit number used directly in MOD has already lost its last digits. The alternative is to process such a number in chunks of a few digits, carrying the remainder of each chunk into the next; more work to build, but exact at any length.

More to watch out for

A number of nine zeros totals 0, and MOD(0, 11) is 0, so 000000000 passes. Add a test such as txt <> "000000000" if placeholder numbers are typed in the master file.
Swapping the arguments, MOD(11, total), returns 11 for every total above 11, never 0, and every number is rejected.

Variations

=MOD(SUMPRODUCT(--MID(A2, {1;2;3;4;5;6;7;8;9}, 1), {9;8;7;6;5;4;3;2;1}), 11) = 0

=MOD(SUMPRODUCT(--MID(A2; {1;2;3;4;5;6;7;8;9}; 1); {9;8;7;6;5;4;3;2;1}); 11) = 0

Works in every Excel version, with array constants instead of SEQUENCE and without the length guard. Shorter, but a number of 8 characters gives #VALUE! instead of FALSE.

Common mistakes

Common mistakes
Symptom	Cause	Fix
MOD(-7, 3) gives 2, not -1	MOD(-7, 3) returns 2, and you expected -1.	If you need the sign of the number, use -7 - 3 * TRUNC(-7 / 3), which gives -1.
#DIV/0! where the divisor cell is empty	A column of MOD results shows #DIV/0! in the rows where the cycle length cell is empty.	An empty divisor counts as 0. Fill it, or lock the reference with $ so every row reads the same cell.
Night shift shows ##### or a negative time	A night shift from 22:00 to 06:00 calculated as end - start shows ##### or a negative number.	Times are fractions of a day, and 0.25 - 0.9167 is negative. MOD(end - start, 1) wraps it into the day: 0.3333, which is 8 hours.

Used in combinations

Round-sum journal entries with FILTER, MOD, ROUND and ABSFlag ledger lines whose amount is an exact multiple of 1,000 (and above a minimum): ABS removes the sign, ROUND clears floating-point noise, MOD tests the multiple and FILTER lists the lines.Excel 2021 or later (This combination needs Excel 2021 or later)