登录查看更多内容

How to use the RANDBETWEEN Function in Google Sheets

工程关注我们，每天学习??

?? Learning & Engineering

发布日期: 2024年3月28日

Returns a uniformly random integer between two values, inclusive.

The RANDBETWEEN function in Google Sheets generates a random integer between two specified values (inclusive). This function is versatile and can be used in various scenarios, including simulations, random sampling, creating data for testing, and more.

??Purchase our book to improve your Excel productivity

Benefits of Using the RANDBETWEEN Function

Data Simulation: Easily generate random data sets for modeling or statistical analysis.
Sample Selection: Facilitate random sample selection from larger data sets for research or audits.
Testing and Debugging: Create test scenarios with random inputs to ensure the robustness of formulas and scripts.
Game Development: Incorporate randomness into games or simulations for varied outcomes.

Master the RANDBETWEEN function in Google Sheets with our expert guide. Learn to simulate sales data, analyze performance, and make informed decisions

Step-by-Step Guide

Step 1: Understanding the Syntax

RANDBETWEEN(low, high)low: The lowest integer value from which the random number can be generated.high: The highest integer value to which the random number can be generated.

Step 2: Applying the RANDBETWEEN Function

Select the Output Cell: Click on the cell where you want the random number to appear.
Input the Function: Enter =RANDBETWEEN(, followed by your low and high values, separated by a comma.

??Purchase our book to improve your Excel productivity

Example

Imagine you're a retail analyst tasked with creating a simulation of sales data for the upcoming quarter to assess potential sales performance and inventory needs. The store has 10 products, and for each product, you need to generate random daily sales figures. Based on these figures, you will categorize each product's performance into tiers.

Step 1: Set Up Your Spreadsheet

First, organize your spreadsheet to accommodate the simulation:

A1: Product ID
B1 - M1: Day 1 through Day 30 (representing a month)
N1: Total Sales
O1: Performance Tier

Rows 2-11 will represent each of the 10 products.

Step 2: Generating Random Daily Sales Figures

In cells B2 to M11, use the RANDBETWEEN function to generate random sales figures between 1 and 100 units for each product on each day:=RANDBETWEEN(1, 100)
Drag to fill this formula across the range B2:M11 for all products and days.

Step 3: Calculating Total Monthly Sales

In column N (Total Sales), sum up the daily sales for each product:=SUM(B2:M2)
Drag the fill handle down from N2 to N11 to calculate total sales for each product.

Step 4: Categorizing Sales into Performance Tiers

Define sales performance tiers based on total monthly sales in column O:=IFS(N2 > 2000, "High", N2 > 1500, "Medium", TRUE, "Low")
Adjust the 2000 and 1500 thresholds as needed based on your simulation requirements.
Drag down from O2 to O11 to apply performance tiers for each product.

Advanced Tips

Refreshing Random Values: Note that RANDBETWEEN recalculates and produces a new value every time the worksheet recalculates. To keep values static, copy and use Paste special > Paste values only.
Combining with Other Functions: Use RANDBETWEEN with CHOOSE or INDEX to randomly select items from a list.
Creating Random Text Strings: Combine CHAR and RANDBETWEEN to generate random letters or symbols.
Random Sampling: To randomly sample rows from a larger dataset, use RANDBETWEEN to assign each row a random number, then sort or filter by these numbers.