All versionsExamples need Excel 2024 or laterMath

RANDBETWEEN

ASELECTTUSSEN in Dutch Excel

Returns a random whole number between two bounds, both included, and picks a new one every time the sheet recalculates.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCDEFG
1Entry no.JournalPeriodDebitPeriod to test77=RANDBETWEEN(1, 12)=RANDBETWEEN(1; 12)
225-0112MEM11€1,250.00€ 1.250,00
325-0388MEM33€18,400.00€ 18.400,00
425-0701MEM66€5,000.00€ 5.000,00
525-1043MEM99€760.50€ 760,50
625-1290MEM1212€22,000.00€ 22.000,00

Result: 7

=RANDBETWEEN(1, 12)
=RANDBETWEEN(1; 12)

What RANDBETWEEN does and when to use it

What it does: it draws a whole number at random between two limits, both included, for example a random period from 1 to 12 for a journal entry test.

When to use it: to fill a sheet with test data, simulate dice or a lottery, or pick a random row for a spot check. For decimals use RAND; for a whole block at once, RANDARRAY.

How Excel evaluates it

Excel reads bottom and top and evaluates any expression inside them first. It rounds bottom up and top down to whole numbers, then draws one integer from that closed range with equal probability for each value.

The draw happens again whenever the workbook recalculates:

  • when you enter anything in any cell
  • when you press F9
  • when you open the file
  • when you sort or filter

Each cell with the function draws independently, so two neighboring cells can show the same number. There is no way to ask for the previous value back.

Watch out: The result is not stored: RANDBETWEEN draws again every time Excel recalculates. To keep a draw, copy the cells and paste them as values over themselves.

Mental model

A die with as many faces as there are numbers between the bounds, rolled again every time anyone touches the sheet.

Terms used on this page: worksheet, workbook, array, range, cell

Syntax

Official RANDBETWEEN documentation
RANDBETWEEN(bottom, top)
RANDBETWEEN(bottom; top)
ArgumentTypeMeaning
bottomRequiredThe smallest number the result may be; it can come out exactly.
topRequiredThe largest number the result may be; it can come out exactly.

Return value:A random whole number from bottom to top

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

Draw a random period for journal entry testing

You are testing manual entries in the XAF auditfile of Veldhuis Installatie BV and want to start with one period that is chosen at random, not the one you happen to like. The ledger has periods 1 to 12, so you need one whole number between 1 and 12, both included. The list shows a few of the manual MEM entries you will look at once the period is drawn.

ABCDEFGH
1Entry no.JournalPeriodDebitPeriod to test77=RANDBETWEEN(1, 12)=RANDBETWEEN(1; 12)
225-0112MEM11€1,250.00€ 1.250,00
325-0388MEM33€18,400.00€ 18.400,00
425-0701MEM66€5,000.00€ 5.000,00
525-1043MEM99€760.50€ 760,50
625-1290MEM1212€22,000.00€ 22.000,00
7
Formula cell (G1)Input

Formula in G1

=RANDBETWEEN(1, 12)
=RANDBETWEEN(1; 12)

Result: 77Random: your result will differ

Step by step
  1. Excel reads bottom (1) and top (12). Both are whole numbers already, so nothing is rounded.
  2. It draws one whole number from 1 to 12 inclusive: 12 possible values, each with a chance of 1 in 12. Say it draws 7; your sheet will show a different number, and that is expected.
  3. Period 7 is now the period to test. Filter the Period column of the auditfile on 7 and look at the MEM entries in it. The list above has no entry in period 7, but the full auditfile does.
  4. Type anything in an empty cell, say J1, and press Enter. The period changes, because entering data triggers a recalculation and RANDBETWEEN redraws. Press F9 and it changes again.
  5. When you accept the draw, copy G1 and paste it as a value over itself, and note the date in your working paper. The formula bar now shows 7 instead of the formula, so the period you tested cannot change behind your back.

Watch out: If you swap the arguments, RANDBETWEEN(12, 1), the result is #NUM!. bottom must not be larger than top.

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

Why it works

Every whole number from bottom to top has the same probability, so no period is favored and the choice can be defended as random. The freezing step matters because the function is volatile: nothing in the sheet remembers yesterday's draw, and a period you cannot reproduce is not evidence of what you tested.

More to watch out for

  • RANDBETWEEN(0, 12) can return 0, and RANDBETWEEN(1, 13) can return 13. Neither is a period in the ledger, so use exactly the lowest and highest period that exist.

Variations

=RANDARRAY(3, 1, 1, 12, TRUE)
=RANDARRAY(3; 1; 1; 12; TRUE)

Spills three periods from one formula, for a test of three periods; the last argument TRUE asks for whole numbers. The same period can come up twice. Excel 2021 or Microsoft 365 only.

Intermediate

Pick a random sales invoice for vouching

You must select one sales invoice at random from the VRK journal of Veldhuis Installatie BV to vouch to the contract and the delivery note, in a way you can show to be random rather than 'the one at the top'. The list grows every time the client sends a new extract, so the upper bound must follow the number of rows instead of being typed in. The chosen invoice number should appear in a cell and then be frozen in the working papers.

ABCDEFG
1Invoice no.RelationAmountInvoices in list66
2F25-1101D1001€12,450.00€ 12.450,00Random row
3F25-1102D1004€389.50€ 389,50Selected invoiceF25-1104=INDEX(A2:A7, RANDBETWEEN(1, COUNTA(A2:A7)))=INDEX(A2:A7; RANDBETWEEN(1; COUNTA(A2:A7)))
4F25-1103D1002€21,000.00€ 21.000,00
5F25-1104D1007€7,525.25€ 7.525,25
6F25-1105D1003€6,400.00€ 6.400,00
7F25-1106D1001€31,200.00€ 31.200,00
8
Formula cell (F3)Input

Formula in F3

=INDEX(A2:A7, RANDBETWEEN(1, COUNTA(A2:A7)))
=INDEX(A2:A7; RANDBETWEEN(1; COUNTA(A2:A7)))

Result: F25-1104Random: your result will differ

Step by step
  1. Excel evaluates the innermost function first. COUNTA(A2:A7) counts the non-empty cells in the invoice column: 6. That becomes top.
  2. RANDBETWEEN(1, 6) draws a whole number from 1 to 6. Say it draws 4; yours will differ.
  3. INDEX(A2:A7, 4) returns the fourth cell of A2:A7, which is A5: F25-1104, the invoice of 7,525.257.525,25 for relation D1007. That is the invoice to vouch.
  4. Compare with a typed upper bound, RANDBETWEEN(1, 6): when F25-1107 is added in A8 with the next extract and the range is extended, the typed 6 would never select the new row. COUNTA follows the list. F1 shows the count for the reader; the formula does not depend on it.
  5. Every recalculation picks a new invoice. Before you start the vouching, copy F3 and paste it as a value into the working paper, and note the date. That frozen value is the evidence of which invoice was selected.
  6. Delete the contents of A7 and COUNTA drops to 5, so the draw runs from 1 to 5 and A7 can no longer be picked.

Watch out: If the invoice column has a blank in the middle, COUNTA counts 5 while the range is 6 rows long, and the last invoice can never be selected. Keep the list contiguous or count with ROWS(A2:A7) instead.

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

Why it works

RANDBETWEEN produces a position, and INDEX turns a position into the value at that position, so the pair converts 'a random number' into 'a random invoice'. COUNTA supplies the upper bound from the data itself, which keeps the selection fair as the list changes. The freeze step is not optional in audit work: a volatile selection that cannot be reproduced is not evidence.

More to watch out for

  • Two auditors selecting 'one random invoice' with separate formulas can land on the same one. For a sample of several items without repeats, see the Master example.

Variations

=RANDBETWEEN(1, COUNTA(A2:A7))
=RANDBETWEEN(1; COUNTA(A2:A7))

Show only the row number, in F2, and read the invoice off by eye; useful when you want to document the number drawn as well as the invoice.

Advanced

Random posting dates and round amounts around year end

You are building a small test file to check that your cut-off and journal entry filters work, before you run them on the real auditfile.

Each row needs a posting date somewhere between 15 December 2025 and 15 January 2026, and a round debit amount that is a multiple of 500, the kind of amount a filter for round amounts should catch. Two random draws per row: one for the date and one for the number of steps of 500.

ABCDEFGH
1Entry no.JournalPosting dateDebitWindow start15 Dec 2025
2T-001MEM3 Jan 20263 Jan 2026=RANDBETWEEN($G$1, $G$2)=RANDBETWEEN($G$1; $G$2)Window end15 Jan 2026
3T-002MEMRound step500500
4T-003VRK
5T-004INK
6T-005MEM
7
Formula cell (C2)Input

Formula in C2

=RANDBETWEEN($G$1, $G$2)
=RANDBETWEEN($G$1; $G$2)

Result: 3 Jan 2026Random: your result will differ

Step by step
  1. Dates in Excel are whole numbers counting days from 1 January 1900, so a random date is a random whole number. G1 holds 15 December 2025, serial number 46006, and G2 holds 15 January 2026, serial number 46037.
  2. RANDBETWEEN($G$1, $G$2) draws a whole number from 46006 to 46037: 46037 - 46006 + 1 = 32 possible values, one per day of the window. Say it draws 46025.
  3. The cell shows 46025 until you give C2 a date format; then it reads 3 January 2026, because 46025 - 46006 = 19 days after 15 December (16 days to reach 31 December, then 3 more). That is a Saturday, so a weekend filter should flag this row. Your date will differ, but it always lies in the window.
  4. In D2 the amount uses a second draw: =RANDBETWEEN(1, 20) * $G$3 picks a number of steps from 1 to 20 and multiplies by 500. Say it draws 7: 7 * 500 = 3,5003.500. The result is always one of 500, 1,0001.000, 1,5001.500 up to 10,00010.000, never 3,4703.470.
  5. Copy C2:D2 down to row 6. Each of the ten cells draws independently; two entries may get the same date, and the file is not sorted by date, as in a real extract.
  6. Change G2 to 31 January 2026 (serial 46053) and the window grows to 48 days. Once the test file looks right, select C2:D6 and paste as values, otherwise the file changes every time you test a filter.

Watch out: If G1 or G2 is typed as text (an apostrophe in front, or a date Excel did not recognize), RANDBETWEEN returns #VALUE!. Check with ISNUMBER(G1).

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

Why it works

Because a date is a serial number, RANDBETWEEN with two date cells as bounds draws a day directly, and both bounds are included so the first and last day of the window can both occur. Multiplying a small random draw by a step size is the standard way to get random multiples: the randomness is in the number of steps, the arithmetic guarantees the unit. Absolute references keep every row reading the same window and step.

More to watch out for

  • RANDBETWEEN(500, 10000) for the amount looks equivalent but silently produces 3,4703.470 and other amounts that are not round. The multiple has to come from multiplication, not from the bounds.
  • A cell that shows a date but holds a RANDBETWEEN formula is a trap for the next person: sorting the test file by date recalculates it, and the sort order is immediately wrong. Freeze before you share the file.

Variations

=RANDBETWEEN(1, 20) * $G$3
=RANDBETWEEN(1; 20) * $G$3

The amount formula for D2, shown separately: random number of steps times the step size.

=WORKDAY($G$1 - 1, RANDBETWEEN(1, NETWORKDAYS($G$1, $G$2)))
=WORKDAY($G$1 - 1; RANDBETWEEN(1; NETWORKDAYS($G$1; $G$2)))

Random working days only: NETWORKDAYS counts the 24 weekdays in the window, RANDBETWEEN picks one, and WORKDAY steps that many working days from the day before the window. Public holidays such as 25 December are not excluded unless you add a holiday list.

MasterExcel 2024 or later

Draw a sample of distinct debtors, with a controlled draw

You must select 3 different debtors out of 8 for balance confirmations. Three separate RANDBETWEEN cells can pick the same debtor twice, and a rejected sample must be re-drawn in a way the file records.

The construction works in three steps:

  • give every row one random key
  • shuffle the whole list by those keys and take the first 3, so duplicates are impossible
  • keep the keys in a helper column, so they can be frozen as evidence
ABCDEFG
1RelationNameRandom keySample size33
2D1001Brinkman Installatie BV82138213SelectedD1005=LET( ids, A2:A9, keys, C2:C9, n, F1, shuffled, SORTBY(ids, keys), TAKE(shuffled, n) )=LET( ids; A2:A9; keys; C2:C9; n; F1; shuffled; SORTBY(ids; keys); TAKE(shuffled; n) )
3D1002Bakker Groothandel BV19071907D1002
4D1003Smit Transport BV55605560D1007
5D1004De Wit Bouw BV77427742
6D1005Jansen Horeca BV302302
7D1006Haddad Import BV99819981
8D1007Raman Logistics BV44784478
9D1008Visser Metaal BV61156115
10
Formula cell (F2)InputSpilled resultNote: Column C holds =RANDBETWEEN(1, 10000) in every row, frozen as values after the draw; the numbers shown are one such draw.

Formula in F2

=LET(
  ids, A2:A9,
  keys, C2:C9,
  n, F1,
  shuffled, SORTBY(ids, keys),
  TAKE(shuffled, n)
)
=LET(
  ids; A2:A9;
  keys; C2:C9;
  n; F1;
  shuffled; SORTBY(ids; keys);
  TAKE(shuffled; n)
)

Result: D1005 / D1002 / D1007D1005 / D1002 / D1007

Step by step
  1. Column C was filled first with =RANDBETWEEN(1, 10000) in each row and then pasted as values, so the keys are constants: 8213, 1907, 5560, 7742, 302, 9981, 4478, 6115. With 10,00010.000 possible values and 8 rows, a duplicate key is unlikely but possible; see the pitfalls.
  2. LET names the inputs: ids is A2:A9, keys is C2:C9, n is 3 from F1.
  3. shuffled is SORTBY(ids, keys): the relation codes sorted by their key, ascending. The smallest key is 302 (row 6, D1005), then 1907 (D1002), 4478 (D1007), 5560 (D1003), 6115 (D1008), 7742 (D1004), 8213 (D1001), 9981 (D1006).
  4. TAKE(shuffled, 3) keeps the first three of that order: D1005, D1002, D1007. They spill from F2 to F4. Three different debtors, because each code appears once in the list and sorting does not duplicate anything.
  5. Change F1 to 5 and the spill grows to D1005, D1002, D1007, D1003, D1008: the same shuffle, two more taken from it. That is what makes the sample extensible without re-drawing, for example when the confirmations do not cover enough of the balance.
  6. To re-draw, put =RANDBETWEEN(1, 10000) back in C2:C9, let it recalculate once, and paste as values again. Because the keys are constants in between, the selection in F2:F4 is stable while you work and the file shows exactly which keys produced it.

Watch out: The mirror-image mistake is leaving the live RANDBETWEEN formulas in column C. The formula still works, but every keystroke re-shuffles the list and the three codes in F2:F4 change under your hands; whoever reviews the file later sees a different sample from the one you confirmed.

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

Why it works

A random key per row plus a sort is a shuffle: every ordering is equally likely, and taking the first n of a shuffle is a sample without replacement. Keeping RANDBETWEEN in the helper column, and freezing it, separates the randomness (which must be recorded) from the selection logic (which must be reproducible).

Do not use this construction for a single random item on a list that changes with every extract; INDEX with RANDBETWEEN, as in the Intermediate example, is simpler there.

The alternative is RANDARRAY inside SORTBY with no helper column, which is shorter but leaves nothing frozen: the sample changes on every recalculation and cannot serve as audit evidence.

More to watch out for

  • Two rows with the same key are sorted in their original order, which slightly favors the upper row. With 8 rows and keys up to 10,00010.000 that happens about 0.3% of the time; use RANDBETWEEN(1, 1000000) on long lists.
  • If F1 is larger than the number of rows, TAKE returns the whole list; it does not error. A sample of 10 from 8 debtors silently becomes 8.

Variations

=TAKE(SORTBY(A2:A9, RANDARRAY(ROWS(A2:A9))), F1)
=TAKE(SORTBY(A2:A9; RANDARRAY(ROWS(A2:A9))); F1)

No helper column: RANDARRAY supplies the keys inside the formula. Shorter, and re-drawn on every recalculation, so it suits demos rather than audit files.

=INDEX(A2:A9, RANDBETWEEN(1, ROWS(A2:A9)))
=INDEX(A2:A9; RANDBETWEEN(1; ROWS(A2:A9)))

One random debtor only. Three copies of this can pick the same debtor; that is the problem the master construction solves.

Common mistakes

SymptomCauseFix
A different sample every dayYou picked a random sample on Monday, and on Tuesday the sample is different, so the work done on it no longer matches.Opening the file recalculated every RANDBETWEEN. Once a sample is final, turn the numbers into constants with Paste Values.
10 appears although you wanted 1 to 9RANDBETWEEN(1, 10) is used to simulate a value from 1 to 9, and 10 keeps turning up.Both bounds are included. For 1 to 9 write RANDBETWEEN(1, 9). Think of the arguments as the lowest and highest allowed result, not as a half-open interval.
Workbook slow on every editA sheet with a column of 50,000 RANDBETWEEN cells has become slow: every edit anywhere takes a second.Volatile functions recalculate on every change, together with everything that depends on them. Freeze the column as values once the data is generated, or keep the generator on a separate sheet that you delete afterwards.
Never 0.5 or 2.5, only 1 or 2RANDBETWEEN(0.5, 2.5) never returns 0.5 or 2.5, and only ever shows 1 or 2.The function returns whole numbers only, and it moves the bounds inward to the nearest integers. For random decimals use RAND() * (top - bottom) + bottom, or RANDARRAY with the decimal option.

Used in combinations