Tip #8: Using RAND and RANDBETWEEN functions to generate random data sets

Tip #8: Using RAND and RANDBETWEEN functions to generate random data sets

Have you ever spent hours working on a sample data record for a demonstration? We're here to show you how to do it in less time. If you want to generate a sample data set for a demonstration, or just to learn how a pivot table works, using the RAND and RANDBETWEEN functions can be a savior. With these functions, you can generate random data that includes text, numbers, dates, and even cell references.

How to use them 

The RAND function generates random values with no defined limits, while RANDBETWEEN, as its name signifies, returns random values within a specified range of values. Let's see how they can be used to generate various data sets.

Random numbers 

To get random numbers, you can use these functions the default way (i.e. without any special changes). 

=RAND() returns float values between 0 and 1
=RANDBETWEEN(10;100) returns values between 10 and 100, inclusive

Random text values 

To get random text values (letters of the alphabet), you can use the RANDBETWEEN function in combination with the CHAR function. The numbers given as the input must be between 65 to 90 for capital letters and 97 to 122 for small letters.

=CHAR(RANDBETWEEN(65;90)) returns capital letters

Random date values 

To get random date values, you can use the RANDBETWEEN function and specify the start and end dates. If the result is returned in the form of a date-time serial number, apply the Date format to the cells to get the desired result.

=RANDBETWEEN("1/2/2022";"19/2/2022") returns dates from the given date range

Random cell references

To get random cell references as the result, use RANDBETWEEN function in combination with ADDRESS function. You can also choose the reference type to be absolute or relative.

=ADDRESS(RANDBETWEEN(1;100);RANDBETWEEN(1;5);4) returns random cell references between columns A to E and rows 1 to 100.

Random values from a list 

If you wish to get random values from a specified data set or range of values, you can use RANDBETWEEN in combination with INDEX function.

=INDEX(L2:L8;RANDBETWEEN(1;5);1;1) returns random values from the cell range L2:L8.
Apart from the above mentioned functions, you can also use RANDARRAY and SEQUENCE to get an array of random and ordered data respectively! Are there any specific features you'd like to learn more about in future tips? Tell us in the comments below! 
Join the Zoho Sheet community and stay up to date on the latest features, interact with fellow users, and learn from the team behind Zoho Sheet. For more such exciting tips follow our Tips and Tricks forum.

                        Zoho Marketing Automation

                                • Sticky Posts

                                • Tip #16: Ask Zia your data-related questions and get powerful insights

                                  Zoho Sheet provides a variety of tools and functionalities to help you analyze your data from end to end. But what if you could analyze all your data sets with a single AI-powered tool? Meet Zia, our smart virtual assistant who will answer all your data-related
                                • Tip #17: Easily share spreadsheets with non-Zoho account users

                                  With efficient, collaboration-friendly options, Zoho Sheet is a popular choice among users. Sheet's external share links help you collaborate more effectively by sharing your spreadsheets with anyone outside your organization, including users who do not
                                • Tip #7: Four things you didn't know you can do with Conditional Formatting

                                  Conditional formatting helps you highlight and visualize data in your spreadsheet based on provided rules. Zoho Sheet offers three types of conditional formatting: Classic, Color Scales, and Icon Sets. If you're already familiar with how Conditional Formatting works, here are some bonus tips for Classic formats: Customize rules with formulas Zoho Sheet allows you to create personalized rules based on formulas. For example, in a task list you can use customized formulas to highlight tasks which are
                                • Tip #15: Stop guessing and start estimating with Zoho Sheet's Goal Seek

                                  Goal Seek is an ideal spreadsheet tool for devising estimates, as it helps you retrieve your desired output value when the input values are not known. In other words, Goal Seek does back-solving to find input values once you've provided an output value.
                                • Tip #10: 3 helpful ways to use data validation in your spreadsheet

                                  Data validation in spreadsheets makes data entry processes more efficient, while helping reduce inconsistencies and errors in your data sets. In this article, we'll look at three ways you can use data validations to create useful dropdown lists in your

                                Manage your brands on social media

                                        Zoho TeamInbox Resources

                                          Zoho DataPrep Resources

                                            Zoho CRM Plus Resources

                                              Zoho Books Resources

                                                Zoho Subscriptions Resources

                                                  Zoho Desk Resources

                                                    Zoho Projects Resources

                                                      Zoho Sprints Resources

                                                        Qntrl Resources

                                                          Zoho Creator Resources

                                                            Zoho WorkDrive Resources

                                                              Zoho Campaigns Resources

                                                                Zoho CRM Resources

                                                                • CRM Community Learning Series

                                                                  CRM Community Learning Series

                                                                • Tips


                                                                • Functions


                                                                • Meetups


                                                                • Kbase


                                                                • Resources


                                                                • Digest


                                                                • CRM Marketplace

                                                                  CRM Marketplace

                                                                • MVP Corner

                                                                  MVP Corner

                                                                • Word of the Day

                                                                  Word of the Day

                                                                        Design. Discuss. Deliver.

                                                                        Create visually engaging stories with Zoho Show.

                                                                        Get Started Now