All versionsExamples need Excel 2024 or laterMath

RAND

ASELECT in Dutch Excel

Returns a random decimal number from 0 up to, but not including, 1, and draws a new one every time the sheet recalculates.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

AB
1Invoice no.Random key
22025-10410.61830,6183=RAND()=RAND()
32025-1042
42025-1043
52025-1044
62025-1045
72025-1046

Result: 0.61830,6183

=RAND()
=RAND()

What RAND does and when to use it

What it does: it draws a random decimal that is at least 0 and always below 1, for example a random key next to each invoice to draw a sample.

When to use it: as the raw material for anything random: test data, a yes or no with a given probability, or a sort key that shuffles a list. For whole numbers RANDBETWEEN is shorter. Once a draw matters, freeze it with Paste Values.

How Excel evaluates it

RAND has no inputs, so Excel draws immediately: a decimal from 0 up to but not including 1. RAND is a volatile function, so the draw happens again whenever the workbook recalculates:

  • when you enter anything anywhere
  • when you press F9
  • when you open the file
  • when you sort or filter

Every RAND in the workbook draws independently, and every formula that depends on one is recalculated with it. There is no seed: a draw cannot be repeated, only frozen.

Watch out: The value is not stored: RAND is redrawn on every recalculation, so freeze a draw (Paste Values) before you rely on it.

Mental model

A spinner on a dial marked from 0 to 1, spun again every time anyone touches the sheet; the needle can stop on 0 but never quite reaches 1.

Terms used on this page: volatile function, workbook

Syntax

RAND()
RAND()

Return value:A random decimal, at least 0 and less than 1

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, 16 KB)

Beginner

Random keys to draw an invoice sample

You test six sales invoices from the receivables ledger and want to pick three of them at random, in a way you can document in the working paper. Each invoice gets a random number next to it; sorting by that number puts the invoices in a random order, and the first three are your sample. You start with the formula in B2.

ABC
1Invoice no.Random key
22025-10410.61830,6183=RAND()=RAND()
32025-1042
42025-1043
52025-1044
62025-1045
72025-1046
8
Formula cell (B2)Input

Formula in B2

=RAND()
=RAND()

Result: 0.61830,6183Random: your result will differ

Step by step
  1. RAND takes no arguments, so Excel goes straight to the draw: one decimal from 0 up to, not including, 1.
  2. Say it draws 0.6183. Your sheet will show a different number with more decimals, and that is expected.
  3. Copy B2 down to B7. Each cell draws on its own, for example 0.6183, 0.0457, 0.9312, 0.3870, 0.5524, 0.7741. A tie is possible in theory but practically never happens, because the numbers have many decimals.
  4. Press F9 or type anything in an empty cell and all six numbers change. That is the volatile behaviour: every recalculation is a new draw.

  5. Select B2:B7, copy, and paste as values onto the same cells, then sort A1:B7 ascending by column B. With the example numbers the order is:

    • 0.0457 (2025-1042)
    • 0.3870 (2025-1044)
    • 0.5524 (2025-1045)
    • 0.6183 (2025-1041)
    • 0.7741 (2025-1046)
    • 0.9312 (2025-1043)
  6. The first three rows are the sample: 2025-1042, 2025-1044 and 2025-1045. Keep the frozen keys in the working paper as evidence of how the sample was drawn.

Watch out: Sort with live formulas in column B and the numbers change after the sort, so the sheet looks unsorted. The sample you got is still random, but nobody can reproduce or review it afterwards.

More on this example: why it works, 1 variation

Why it works

Every invoice gets a number from the same uniform draw, so every order of the six is equally likely when you sort by it, and so is every set of three at the top. Freezing before sorting matters: sorting itself triggers a recalculation, and live RAND keys would reshuffle while you sort.

Variations

=RANDARRAY(6)
=RANDARRAY(6)

Fills B2:B7 from one formula by spilling. Excel 2021 or Microsoft 365 only; freeze it the same way.

Intermediate

Random test amounts between two bounds

Before you run a journal entry testing script on the client's real XAF auditfile, you check it on a test file of dummy journal lines. The amounts must lie between 100 and 5,0005.000 euros, in whole cents, so you can see whether the round-amount and threshold tests respond as expected. The bounds sit in F1 and F2 so you can change them, and the formula is copied down the test lines.

ABCDEFG
1Entry no.DescriptionTest amountLowest€100.00€ 100,00
2TST-01Test line, supplier payment€2,122.38€ 2.122,38=ROUND($F$1 + ($F$2 - $F$1) * RAND(), 2)=ROUND($F$1 + ($F$2 - $F$1) * RAND(); 2)Highest€5,000.00€ 5.000,00
3TST-02Test line, office rent
4TST-03Test line, manual correction
5TST-04Test line, bank charges
6
Formula cell (C2)Input

Formula in C2

=ROUND($F$1 + ($F$2 - $F$1) * RAND(), 2)
=ROUND($F$1 + ($F$2 - $F$1) * RAND(); 2)

Result: €2,122.38€ 2.122,38Random: your result will differ

Step by step
  1. Excel works inside out. RAND() draws a decimal from 0 up to 1. Say it draws 0.41273; yours will differ.
  2. The width of the range is $F$2 - $F$1 = 5,0005.000 - 100 = 4,9004.900. Multiplying gives 4,9004.900 × 0.41273 = 2,022.3772.022,377: how far above the lowest amount this draw lands.
  3. Adding the lowest amount moves the result into the range: 100 + 2,022.3772.022,377 = 2,122.3772.122,377.
  4. ROUND(2,122.3772.122,377, 2) makes it whole cents: 2,122.382.122,38. Without ROUND the cell would hold 2,122.3772.122,377 and the test file would contain amounts with fractions of a cent that no journal line has.
  5. Copy down; the $ signs keep every row on F1 and F2. Every result lies from 100.00 to 5,000.005.000,00. RAND never reaches 1, so without ROUND 5,0005.000 itself is impossible; with ROUND, 5,000.005.000,00 appears only when RAND is above about 0.999999 (4,899.9954.899,995 / 4,9004.900 = 0.99999898).
  6. Change F2 to 10,00010.000 and the same draw of 0.41273 would give 100 + 9,9009.900 × 0.41273 = 100 + 4,086.0274.086,027 = 4,186.0274.186,027, rounded 4,186.034.186,03.

Watch out: Write F2 * RAND() + F1 by mistake and the amounts run from 100 to 5,1005.100: the width is the highest amount instead of the difference. Nothing errors; the top of the range is silently too high, and a threshold test at 5,0005.000 sees amounts it should never meet.

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

Why it works

min + (max - min) × RAND() stretches the interval 0 to 1 to the width you need and slides it to start at your minimum, keeping every value equally likely. ROUND is the one helper: it turns a continuous draw into whole cents, and the locked bounds let one change in F1 or F2 reshape the whole column.

More to watch out for

  • Random amounts rarely land on round numbers. To test that your round-amount check works, add a few lines with fixed amounts such as 1,000.001.000,00 by hand.

Variations

=RANDBETWEEN($F$1 * 100, $F$2 * 100) / 100
=RANDBETWEEN($F$1 * 100; $F$2 * 100) / 100

Draws whole cents directly, from 10,00010.000 to 500,000500.000, and divides by 100. Both 100.00 and 5,000.005.000,00 are as likely as any other amount.

AdvancedExcel 2021 or later

Draw the next journal to test by risk weight

In journal entry testing you want to pick which journal to examine next, with more weight on the journals that carry more risk:

  • MEM (manual entries): 40%
  • VRK: 30%
  • INK: 20%
  • BNK: 10%

Each draw must land on MEM 40% of the time, on VRK 30% of the time, and so on. Column C holds where each journal's share starts on the scale from 0 to 1.

ABCDEF
1JournalRisk weightStarts atJournal to test
2VRK30%30%0%0%MEM=XLOOKUP(RAND(), C2:C5, A2:A5, "VRK", -1)=XLOOKUP(RAND(); C2:C5; A2:A5; "VRK"; -1)
3INK20%20%30%30%
4BNK10%10%50%50%
5MEM40%40%60%60%
6
Formula cell (E2)InputNote: Each value in C is the sum of the weights above it: 0, 30%, 30% + 20% = 50%, 50% + 10% = 60%.

Formula in E2

=XLOOKUP(RAND(), C2:C5, A2:A5, "VRK", -1)
=XLOOKUP(RAND(); C2:C5; A2:A5; "VRK"; -1)

Result: MEMRandom: your result will differ

Step by step
  1. Excel evaluates the inner function first. RAND() draws a decimal from 0 up to 1. Say it draws 0.62; yours will differ.
  2. XLOOKUP looks for 0.62 in C2:C5 with match_mode -1: an exact match, or else the next smaller value. 0.62 is not in the list, so it takes the largest start at or below it: 0.6 in C5, the fourth cell.
  3. It returns the fourth cell of A2:A5, which is A5: MEM. The VRK in if_not_found can never be used, because C2 is 0 and RAND never goes below 0.

  4. Every draw from 0.6 up to, but not including, 1 lands on MEM: that stretch is 0.4 wide, so MEM comes up 40% of the time. The rest of the scale:

    • draws below 0.3 give VRK (30%)
    • draws from 0.3 to 0.5 give INK (20%)
    • draws from 0.5 to 0.6 give BNK (10%)
  5. Copy E2 down 20 rows for 20 draws. Press F9 and a new set appears; over many draws the share of each journal approaches the weights in column B. Freeze the draws you use and file them with the working paper.

Watch out: If column C holds where each journal ends (0.3, 0.5, 0.6, 1) instead of where it starts, the same formula still returns plausible codes, but every outcome shifts by one row and MEM can only appear when RAND is exactly 1, which never happens.

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

Why it works

The starts in column C cut the interval from 0 to 1 into pieces whose widths equal the weights. A uniform draw falls in each piece exactly as often as that piece is wide, and XLOOKUP with match_mode -1 finds which piece it fell in. The table need not be sorted for XLOOKUP, but the starts must be correct sums.

More to watch out for

  • If the weights in B do not add up to 100%, the last journal silently absorbs the difference. Check with SUM(B2:B5).

Variations

=INDEX(A2:A5, MATCH(RAND(), C2:C5, 1))
=INDEX(A2:A5; MATCH(RAND(); C2:C5; 1))

The same draw for versions without XLOOKUP. MATCH with 1 needs column C sorted ascending, which a column of cumulative starts always is.

MasterExcel 2024 or later

Value-weighted invoice selection from the receivables

For a monetary unit sample on the receivables list, one invoice is drawn with a chance proportional to its amount: every euro of the list is a unit that can be hit. One invoice is a zero-value settled item and must never be selected, even in the rare case that RAND returns exactly 0. The formula builds the running totals itself, draws once, and looks up the selected invoice, all inside LET.

ABCDE
1Invoice no.AmountSelected invoice
225-0412€12,500.00€ 12.500,0025-0431=LET( invoices, A2:A7, amounts, B2:B7, ends, SCAN(0, amounts, LAMBDA(acc, a, acc + a)), draw, RAND() * SUM(amounts), XLOOKUP(TRUE, ends > draw, invoices) )=LET( invoices; A2:A7; amounts; B2:B7; ends; SCAN(0; amounts; LAMBDA(acc; a; acc + a)); draw; RAND() * SUM(amounts); XLOOKUP(TRUE; ends > draw; invoices) )
325-0418€8,000.00€ 8.000,00
425-0431€21,500.00€ 21.500,00
525-0436€0.00€ 0,00
625-0447€11,000.00€ 11.000,00
725-0452€7,000.00€ 7.000,00
8
Formula cell (D2)Input

Formula in D2

=LET(
  invoices, A2:A7,
  amounts, B2:B7,
  ends, SCAN(0, amounts, LAMBDA(acc, a, acc + a)),
  draw, RAND() * SUM(amounts),
  XLOOKUP(TRUE, ends > draw, invoices)
)
=LET(
  invoices; A2:A7;
  amounts; B2:B7;
  ends; SCAN(0; amounts; LAMBDA(acc; a; acc + a));
  draw; RAND() * SUM(amounts);
  XLOOKUP(TRUE; ends > draw; invoices)
)

Result: 25-0431Random: your result will differ

Step by step
  1. LET names the ranges. ends is SCAN with a running total: 12,50012.500, 12,50012.500 + 8,0008.000 = 20,50020.500, 20,50020.500 + 21,50021.500 = 42,00042.000, 42,00042.000 + 0 = 42,00042.000, 42,00042.000 + 11,00011.000 = 53,00053.000, 53,00053.000 + 7,0007.000 = 60,00060.000. Each invoice owns the stretch from the previous end up to its own end.
  2. draw is RAND() × SUM(amounts) = RAND() × 60,00060.000, a number from 0 up to, not including, 60,00060.000. Say RAND draws 0.5381; then draw is 0.5381 × 60,00060.000 = 32,28632.286. LET calculates draw once, so the whole formula uses the same draw.
  3. ends > draw compares every end with 32,28632.286: {FALSE; FALSE; TRUE; TRUE; TRUE; TRUE}. 12,50012.500 and 20,50020.500 are not above it; 42,00042.000 is.
  4. XLOOKUP(TRUE, ..., invoices) returns the invoice number at the first TRUE, the third position: 25-0431, whose stretch runs from 20,50020.500 to 42,00042.000. Your draw will differ.
  5. The end of invoice 25-0436, 42,00042.000, equals that of 25-0431, so any draw that makes its end TRUE has already made the previous one TRUE one position earlier. The strict > also covers RAND returning exactly 0: draw 0 gives TRUE first at 25-0412, which has an amount.
  6. Chances check: 25-0431 owns 21,50021.500 of 60,00060.000 units, 21,50021.500 / 60,00060.000 = 35.8%; 25-0412 owns 12,50012.500 / 60,00060.000 = 20.8%; 25-0436 owns 0%. Freeze D2 with Paste Values as soon as the selection is made.

Watch out: The mirror-image mistake is looking up the draw with match_mode -1 on the ends (the next smaller end). For 32,28632.286 it returns the invoice whose end is 20,50020.500, 25-0418. Every selection shifts one position up:

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

Why it works

Scaling one RAND to the list total and finding the first running total above it gives every invoice a share of the scale equal to its amount, so the chance is exactly proportional. The strict comparison makes a zero-value invoice unreachable, because its stretch has no width. LET guarantees a single draw; writing RAND() twice would compare against two different numbers.

Do not use this construction when the selection must be reperformable by a reviewer: RAND has no seed and the result changes on the next recalculation. The alternative is to draw once into a separate cell, paste it as a value with the date and your initials, and point the formula at that cell instead of RAND(); it takes a manual step but leaves evidence.

A full monetary unit sample with a fixed interval and a random start is a different design; this formula draws one item at a time.

Watch out for

  • The mirror-image mistake is looking up the draw with match_mode -1 on the ends (the next smaller end). For 32,28632.286 it returns the invoice whose end is 20,50020.500, 25-0418. Every selection shifts one position up:

    • 25-0452 can never be selected (its end, 60,00060.000, is never at or below a draw)
    • 25-0412 is returned only for draws from 12,50012.500 up to 20,50020.500
    • any draw below 12,50012.500 (about 21% of draws) has no smaller end at all and returns #N/A

    The other draws still look like a fair draw.

  • Using >= instead of > lets a draw that lands exactly on 42,00042.000 pick 25-0431 by its end rather than 25-0447 by its start; with decimals this is rare, but with RAND() replaced by a whole number it happens often.
  • Every edit in the workbook draws a new invoice. Document only a frozen value.

Variations

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

An unweighted draw: every invoice has the same chance, including the zero-value 25-0436. Use it only when the amount does not matter.

Common mistakes

SymptomCauseFix
Values change with every editThe random test amounts change every time you type in the sheet, and yesterday's checks no longer match.Once the values are final, copy them and Paste Values over themselves.
Values like 0.37, and 10 never appearsRAND() * 10 is meant to give a number from 1 to 10, and values like 0.37 appear while 10 never does.Scaling multiplies the range 0 to 1 into 0 to 10, never reaching 10. For whole numbers from 1 to 10 use RANDBETWEEN(1, 10); for decimals from 1 to 10 use 1 + 9 * RAND().
Result differs from what the test saysA formula uses RAND() twice, for example IF(RAND() < 0.5, RAND(), 0), and the result is not what the test promised.Each RAND() is a separate draw. Draw once into a cell or a LET variable and refer to that.