Рет қаралды 79
There are many reasons for generating random numbers. You might want to create a sales forecast, test a financial model, or even build a lottery simulator.
Excel has three functions for this purpose: RAND, RANDBETWEEN, and RANDARRAY.
We will focus on how RANDARRAY can be used on its own and in conjunction with other functions.
Let's take a look at the three examples in the video.
𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝟭
=RANDARRAY(10,,1,10,TRUE)
By providing a [rows] number of 10, a [min] of 1, a [max] of 10, and an [integer] of TRUE, 10 random whole numbers from 1-10 are returned.
Pressing F9 recalculates the workbook, therefore refreshing the figures. However, duplicate numbers are highly likely to appear each time, as this method does nothing to prevent them.
𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝟮
=SORTBY(
SEQUENCE(10),
RANDARRAY(10)
)
A list of 10 non-repeating numbers from 1 to 10 is generated using a combination of SORTBY, SEQUENCE, and RANDARRAY.
SEQUENCE(10) guarantees an array of unique elements: {1;2;3;4;5;6;7;8;9;10}, and SORTBY orders these according to the 10 decimal values RANDARRAY produces. Each is greater than or equal to 0 and less than 1 (e.g. 0.458252576150794).
The values given in SEQUENCE and RANDARRAY must match; otherwise, a hashtag#VALUE error occurs.
𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝟯
=TAKE(
SORTBY(
SEQUENCE(50),
RANDARRAY(50)
),
10
)
This is based on the same formula as Example 2, except it's housed in TAKE to allow for greater flexibility with the range of possible numbers (1-50) while still only returning 10 of them.
#exceleration #excel #globalexcelsummit
---
The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.
Find us on:
🌐 Website: globalexcelsum...
👔 LinkedIn: / global-excel-summit
🆇 X: x.com/ExcelSummit
🤳 TikTok: / globalexcelsummit
📸 Instagram: / globalexcelsummit
🟦 Facebook: / globalexcelsummit
🧵 Threads: www.threads.ne...