Der Blog für digitale Kompetenz

Der Blog für digitale Kompetenz

Excel random number generator – With Analysis function

Do you want to use Excel to create the next numbers for your lottery ticket, or would you like Excel to randomly draw the winner and the prize itself from a table? There are various options for how you can use a random number generator in Excel or draw random numbers. So today we want to look at different ways how it can work.

In doing so, we will once again reveal a hidden function in Excel that is not visible by default.

Zufallsgenerator in Excel erstellen

Topic Overview

Anzeige

Excel random number generator – With Analysis function

Do you want to use Excel to create the next numbers for your lottery ticket, or would you like Excel to randomly draw the winner and the prize itself from a table? There are various options for how you can use a random number generator in Excel or draw random numbers. So today we want to look at different ways how it can work.

In doing so, we will once again reveal a hidden function in Excel that is not visible by default.

Zufallsgenerator in Excel erstellen

Topic Overview

Anzeige

Random numbers in Excel with a formula

Random numbers in Excel with a formula

In Excel, you can generate random numbers with the formula “=RAND()“.

This formula would return a random number between 0 and 1. You can then use this random number to generate other numbers. For example, you can use the formula “=RAND() * 100” to generate a random number between 0 and 100. You can specify the number of decimal places and the formatting (e.g. currency in €) yourself in the “Start” tab.

If you want to generate a random number that falls within a certain range, you can use the formula “=RAND()*(MAX(49)*(MIN(1)))“. Here “max” would be the maximum value 49 and “min” the minimum value 1 between which the random number should lie.

If you want to generate a random number that is new every time you update or recalculate your spreadsheet, you can paste the formula into a cell and then press the F9 key to recalculate the formula and get a new random number to generate. You could also create a table that, like in our example, shows the number series of a 6 out of 49 lottery ticket, and from this with the function: “=UNIQUE(RANDOM(I$3;O$9);FALSE;TRUE)” random and unique draw numbers. For a deeper understanding of the “$” characters used, I recommend our article on absolute, relative, and mixed cell references.

Note: Since the random numbers are based on Excel formulas, they are regenerated each time the spreadsheet is updated. However, if you want a random number to be generated only once and then remain constant, you can copy and paste the random number as a value. The nested functions that we have applied here can be changed and adjusted at will. For example, you could also extend the function with “=INTEGRATE(…” if you only want whole numbers, and so on and so forth. It also helps to simply try it out to explore the limits of functionality. Find more about nested functions here with us >>>

see fig. (click to enlarge)

Zufallszahlen mit Funktion in Excel
cshow
Ads

In Excel, you can generate random numbers with the formula “=RAND()“.

This formula would return a random number between 0 and 1. You can then use this random number to generate other numbers. For example, you can use the formula “=RAND() * 100” to generate a random number between 0 and 100. You can specify the number of decimal places and the formatting (e.g. currency in €) yourself in the “Start” tab.

If you want to generate a random number that falls within a certain range, you can use the formula “=RAND()*(MAX(49)*(MIN(1)))“. Here “max” would be the maximum value 49 and “min” the minimum value 1 between which the random number should lie.

If you want to generate a random number that is new every time you update or recalculate your spreadsheet, you can paste the formula into a cell and then press the F9 key to recalculate the formula and get a new random number to generate. You could also create a table that, like in our example, shows the number series of a 6 out of 49 lottery ticket, and from this with the function: “=UNIQUE(RANDOM(I$3;O$9);FALSE;TRUE)” random and unique draw numbers. For a deeper understanding of the “$” characters used, I recommend our article on absolute, relative, and mixed cell references.

Note: Since the random numbers are based on Excel formulas, they are regenerated each time the spreadsheet is updated. However, if you want a random number to be generated only once and then remain constant, you can copy and paste the random number as a value. The nested functions that we have applied here can be changed and adjusted at will. For example, you could also extend the function with “=INTEGRATE(…” if you only want whole numbers, and so on and so forth. It also helps to simply try it out to explore the limits of functionality. Find more about nested functions here with us >>>

see fig. (click to enlarge)

Zufallszahlen mit Funktion in Excel
cshow
Ads

Random numbers in Excel using the analyze function

Random numbers in Excel using the analyze function

Since we already used the term “analysis function” in the title, we now want to go into more detail about it. By default, this data analysis feature is not enabled in Excel. To enable data analysis option in Excel, you need to follow the following steps:

  • Open Excel and click on the “File” tab.
  • Click “Options“.
  • In the left pane of the Excel Options window, select Add-Ins.
  • From the Manage drop-down menu, select Excel Add-ins and click Go.
  • Check the box next to “Data Analysis” and click OK.
  • The data analysis feature should now be available in the “Data” tab in your ribbon and you can use the various data analysis tools.

If you don’t see the Data Analysis option in the list of available add-ins, you may need to install the add-in. To do this, simply click “Browse” to locate and install the add-in installation file.

see fig. (click to enlarge)

Excel Add-In Analysefunktion Abb.1
Excel Add-In Analysefunktion Abb.2

With the analysis function, you have expanded your options in Excel a good deal again, and we can also use the analysis function to determine random numbers. For our example, let’s take a new spreadsheet and click on “Data Analysis” on the “Data” tab. We have set the number of variables to 5, and we want 20 different random numbers to be generated for each variable with a “standard deviation” or normal distribution of 2.5.

We let Excel start the random number generation in cell A1. After clicking on “Ok” we are presented with our numbers with an even distribution. This also becomes clear when we display a diagram of the numerical values in column A, as shown in the example.

see fig. (click to enlarge)

Zufallszahlen in Excel mit Datenanalyse Abb.1
Zufallszahlen in Excel mit Datenanalyse Abb.2
Zufallszahlen in Excel mit Datenanalyse Abb.3

We hope that we could help you with our little tutorial regarding random numbers in Excel, also by using the data analysis option. Feel free to experiment with this feature to discover more possibilities.

Since we already used the term “analysis function” in the title, we now want to go into more detail about it. By default, this data analysis feature is not enabled in Excel. To enable data analysis option in Excel, you need to follow the following steps:

  • Open Excel and click on the “File” tab.
  • Click “Options“.
  • In the left pane of the Excel Options window, select Add-Ins.
  • From the Manage drop-down menu, select Excel Add-ins and click Go.
  • Check the box next to “Data Analysis” and click OK.
  • The data analysis feature should now be available in the “Data” tab in your ribbon and you can use the various data analysis tools.

If you don’t see the Data Analysis option in the list of available add-ins, you may need to install the add-in. To do this, simply click “Browse” to locate and install the add-in installation file.

see fig. (click to enlarge)

Excel Add-In Analysefunktion Abb.1
Excel Add-In Analysefunktion Abb.2

With the analysis function, you have expanded your options in Excel a good deal again, and we can also use the analysis function to determine random numbers. For our example, let’s take a new spreadsheet and click on “Data Analysis” on the “Data” tab. We have set the number of variables to 5, and we want 20 different random numbers to be generated for each variable with a “standard deviation” or normal distribution of 2.5.

We let Excel start the random number generation in cell A1. After clicking on “Ok” we are presented with our numbers with an even distribution. This also becomes clear when we display a diagram of the numerical values in column A, as shown in the example.

see fig. (click to enlarge)

Zufallszahlen in Excel mit Datenanalyse Abb.1
Zufallszahlen in Excel mit Datenanalyse Abb.2
Zufallszahlen in Excel mit Datenanalyse Abb.3

We hope that we could help you with our little tutorial regarding random numbers in Excel, also by using the data analysis option. Feel free to experiment with this feature to discover more possibilities.

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Spring Specials 2024: Word & Excel Templates

Special Offers 2023: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Spring Specials 2024: Word & Excel Templates

Special Offers 2023: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones
2023-06-04T08:50:43+02:00By |Categories: Microsoft Excel, Microsoft Office, Office 365, Shorts & Tutorials|Tags: , |

Title

Ads

Popular Posts:

Search by category:

Autumn Specials:

Anzeige
Go to Top