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). 

Example

=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.

Example

=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.

Example

=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.

Example

=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.

Example 

=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.

• Topic Participants

• Jaya Shankari S

• Sticky Posts

• Tip #18: 6 Trendlines and when to use them in your spreadsheet data?

  Charts are a great tool for visualizing and interpreting large chunks of data in spreadsheets. Zoho Sheet offers you 35+ chart options, along with AI-powered chart recommendations based on the data set you select. There are various chart elements that

• 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.

• Recent Topics

• Accordion in tabs to create FAQs, etc.

  Accordion elements do not seem to be able to be placed in the tabs. It would be useful to be able to do this. Thank you.

• Which are the IP addresses to use for 'split delivery' with Office 365? (Zoho mail inbound gateway)

  Hi, I'm trying to set up 'split delivery' (email routing) with Office 365. I'm following the instructions to set up Office 365 as the primary server (https://www.zoho.com/mail/help/adminconsole/coexistence-with-office365.html) One of the prerequisites

• Zoho Projects 2024 Recap

  Dear Users, As we conclude another remarkable year, it's the time to reflect on the journey we've just completed. The year 2024, defined by significant milestones, challenges, achievements, and important lessons. Every moment has contributed to the story

• Custom Fields at Line Level

  Hi, is there an ability to add custom fields at line level? I need to track the start and the end date for each product within an invoice and I can't seem to find an option to do this.

• Zoho API Error Code 7019 when adding job.

  Hello, I am following the documentation found here. https://www.zoho.com/people/api/timesheet/adding-jobs.html Regardless of how I try and post the data (including just using the example requests), I receive back the response {'response': {'message':

• How to see changes with ZOHO.CRM.API.updateRecord(config) without reload page

  hello got a widget in account, trigger with a button i copy data to account when click on a button, in my popup All is working well. But i need to reload the page to see the update. How can i see the changes without reloading page, only when close the

• How to call a Creator function which is in a different Creator application?

  How to call a Creator function which is in a different Creator application?

• Unable to send message; Reason: 554 5.1.8 Email Outgoing Blocked

  My account is mino@flawless-frames.com, or flawlessframesstudio@gmail.com Could you please unblock my account, I've got restricted from sending more emails

• Stock Count

  The stock count is a nice new feature, but we cannot figure out how to: 1. Use it without assigning to a person, we have a team or one of multiple do stock counts as do most any company. 2. Add any extra fields to what the "counter" sees. The most important

• Move a Contact from Current Account to a NEW Account

  I do not believe the functionality to Move a Contact from a Current Account to a New Account is not available. Please someone tell me I am missing something! I have been through designing, developing, using and selling CRM systems for over 25 years and had this functionality20+ years ago in other CRMs.  In the real world people move from one organisation to another. In the sales, finance and technical world it is nice to see the communication history with that person in their old account and also

• Force Specific Layout for CRM Contacts Portal

  Hello: We're in trial on ZOHO One and looking at the CRM Portal (just for the contacts module). We have a client layout set up for Contacts that is working well for our internally, but for the portal we don't want to require (make mandatory) some of the

• Automatic Removal of Departments and Groups for Inactive Employees in Zoho One

  Hi Zoho One Team, We hope you're doing well. Currently, when an employee is marked as inactive in Zoho One, they remain listed as a member of their department and associated groups. This creates a challenge in maintaining accurate records and ensuring

• Change eMail Template for Event-Invitations

  Hello ZOHO-CRM Team How I can change the eMail Template for Event-Invitations? I work with the German Version of the Free Version. I know how I can modify eMail alerts or Signature Templates, but where I can other eMails modify you send out? Thank you for your answer. Regards, Juerg

• Zoho Social integration with Zoho Flow

  Is there any plans for Zoho Social integration with Zoho Flow?

• Zoho CRM Widget and translations

  Hi everyone! We're building a Widget with zoho-extension-toolkit, how is localization supposed to work? "zet init" created a translations/en.json file, but what should go inside it and how is it supposed to be accessed from the Widget/javascript? Thanks

• Bienvenue à Zoho FSM : l'optimisation des opérations locales qui offre une expérience de service impeccable

  Nous sommes ravis de vous présenter Zoho FSM, la plateforme de gestion des services terrain de bout en bout. Les solutions de gestion des services locaux s'adressent aux organisations qui effectuent des activités d'installation, de réparation et de maintenance

• Adding tag to specific record as an acion in a workflow

  Hi! I've created the following workflow in the module 'Leads'. When a record meets the criteria, there should be a tag added to the specific record in the module 'Contacts'. In the module 'Leads', there is a look-up field named 'Kandidaat' which is connected

• Trying to catch error with ZOHO.CRM.HTTP.get (Response Code)

  Hello, I'm trying to get response header from ZOHO.CRM.HTTP.get, in order to catch error like 404 or something else but it seems that ZOHO.CRM.HTTP.get() method only returns the body of the response, and I see no way to access the headers returned. Is

• FSM - How to ADD PHOTOS to Estimates & Invoices

  How can you add photos to estimates and invoices that are being emailed to the client so the can see what you are estimating and your completed work?

• Free developer edition of Zoho CRM

  A question for Zoho and other developers: How can you set up a demonstration version of a Zoho CRM implementation to show employers/clients what can be achieved? Do you pay for Zoho CRM Enterprise/Zoho One for this purpose? Does Zoho offer a free version

• Can I add Conditional merge tags on my Templates?

  Hi I was wondering if I can use Conditional Mail Merge tags inside my Email templates/Quotes etc within the CRM? In spanish and in our business we use gender and academic degree salutations , ie: Dr., Dra., Sr., Srta., so the beginning of an email / letter

• Where are scheduled emails stored?

  After you schedule an email to go out through the CRM, how do you go about changing that scheduled email? Or even where can I see the list of emails scheduled to go out? They are not listed in my Zoho Mail account in Outbox which has been the only answer

• CRM Home Page Dashboard, how can i add zoho desk cases?

  How can i see which tickets are in my group as a dashboard component on the home tab in zoho crm? I don't see any way of adding this.

• Custom Module missing SDK function fetchRelatedRecords(...) in a Client Script

  Good day, We have added a new module with a Multi-Lookup relation to Contacts.  When we tried to use the fetchRelatedRecords(id, related_list_api_name) function to get Related Records it is missing for our new custom module. https://js.zohocdn.com/crm/5124797/documentation/DotSDK/Modules.html

• Assistance with Setting Default Values for Zoho Chat Custom Fields

  I am currently using the Zoho Chat JavaScript API to successfully add custom fields to the chat interface. While the implementation of these fields has been smooth, I am now looking to set default values for these custom fields. However, I couldn't find

• Subform Client Script

  Good day,  I have a subform where users can subscribe to various magazines. I would like to prevent the user from selecting the same magazine twice when adding a new row. Is there a way to prevent the user from doing this? (Can it be done via a client

• Checklist/ save to onedrive/ a group of items invoicing in Zoho FSM

  hi, is there a way to add a specific checklist to any WO without passing eachtime by the model customization? can we save file such picture directly in our sharepoint ak onedrive? is there any way to add a group of item pre defined to make invoicing easier

• Serious question: Are there actually "solo-preneurs"/small business owners who made Zoho-one work well for them?

  L.S. After already many years of continued struggle with Zoho-One, I am seriously wondering if there are actually solo-preneurs (one person small business owners - without a large, dedicated IT dept.) who got it (Zoho-One) to work well for their businesses.

• Calendar Bookings in Recruit

  Hi there, We have recently started using Zoho recruit and although it has some great functionality there are a few gaps that are causing real headaches. One of those being how interviews are scheduled. The majority of our hiring managers are field based

• Getting Error : Developer Tool Detected

  Hi Team, Getting the error during open the portal, error attached on the same ticket. Please check and help us to resolve the same.

• Send Zoho Creator Template by Email or sendemail

  Hello All Question:- How we can send the Zoho creator email template using the send email by the workflow or using the Function? by the Workflow sendmail [ from: zoho.adminuserid to: "zohodeveloper@yopmail.com" subject: "Test Template" message: "Test

• Upgraded to Zoho One but Zoho Meeting still says Free Plan

  I signed-up for the Zoho One plan. When exploring the applications included, I came across a problem with Zoho Meeting. It says it's the free plan. I emailed support but they sent me a link that doesn't work and, when I found the article on my own, it

• 新年のご挨拶、直近のイベントスケジュール

  🎍🐍謹賀新年🎍🐍 ユーザーの皆さま、明けましておめでとうございます！ コミュニティチームの中野です。 本年もよろしくお願い致します。 昨年のZoho Japanコミュニティでは、東京・大阪・名古屋・福岡 4都市でのユーザー交流会開催や、自社最大イベント「Zoholics Japan 2024」でZoho Championの皆さまとのパネルディスカッション、10月の東京ユーザー交流会では本社CRMプロダクトマネージャーを招きロードマップ解説セッションの実施、さらにZohoアンバサダープログラムのローンチ（近日公開予定）など、新たな取り組みに挑戦しました。

• Request to Customize Module Bar Placement in New Zoho CRM UI

  Hello Support and Zoho Community, I've been exploring the new UI of Zoho CRM "For Everyone" and have noticed a potential concern for my users. We are accustomed to having the module names displayed across the top, which made navigation more intuitive

• Client script: Can not choose a date field for an onChange field event

  Hi Zoho Team Why can't I choose one of my date fields to trigger an onChange event? Is this a client script limitation, or something wrong with my instance? If it is a limitation, is this mentioned in the documentation anywhere? Thanks. Marcus

• How to upload mass inventory quantity adjustment with batch number?

  I wanted to clean-up our inventory list; however, I have trouble performing the task as I don't know how to mass upload inventory adjustment with batch numbers. I don't want to do it manually as there are if not thousands at hundreds of inventory tr

• Upgrade User

  Hi Sir/Madame, I have 7 users in my organisation but I need upgrade more space only 2 users. Is it possibe? Thanks

• Dashboard Auto-refresh

  Why is there no Auto-refresh on the Dashboard? People have been asking for the last 6 years for this function from what I can see in my reasearch, it seems like a very simple intergration for Zoho to achieve. on Sept. 2, 2014, this was added: Support

• Console error with widget in View mode, not present in Edit Mode with ZOHO.CRM.API.updateRecord(config)

  hello i have that function to update an account records (from a widgets created with sigma) function handleCopyButtonCRMClick() { console.log('handleCopyButtonCRMClick'); /* * Fetch Information of Record passed in PageLoad * and insert the response into

• add meeting link

  i want to add A meeting link in the mail when the user book the appointment then with the conformation mail i want to add meeting link there

• Next Page