Microsoft Excel Tutorial: Iterate Through Multiple Random Results
Welcome to another episode of the MrExcel netcast, where we explore all things Excel. In today's episode, we will be discussing how to generate multiple random results from one formula. This is a useful skill to have when working with large datasets or creating fake data for testing purposes.
In this episode, I will be sharing some tips and tricks that I discovered while working on my monthly article for Strategic Finance magazine. I needed to generate fake data for the article, and in the process, I used some familiar Excel functions such as TEXTJOIN, RANDBETWEEN, and F4 to redo. However, I also stumbled upon some interesting ways to speed up the process, which I will be sharing with you today.
First, I tried to create one massive array formula to generate the data, but I couldn't reach my copy of CTRL+SHIFT+ENTER. So, I decided to use RANDBETWEEN, a function that I use all the time. By combining RANDBETWEEN with a list of products and using TEXTJOIN, I was able to generate a list of random products with a variable number of items in each cell. However, I noticed that there was a chance of getting duplicate products, which I didn't want in my fake data.
To solve this issue, I came up with a formula using the RAND function to check if a product should be included in the list or not. This eliminated the chance of getting duplicate products. But then, I faced the challenge of generating multiple results using this formula. I tried copying and pasting, but that was a hassle. So, I used the F4 function to redo the paste values, which made the process a bit faster. But, the ultimate solution came from a method I learned from my late friend, Professor Simon Benninga.
Professor Benninga taught me how to use the WHAT-IF tools and a data table to run a model multiple times, essentially pressing F9 to generate new random values each time. This was a game-changer for me, and I am excited to share this method with you today. By using this method, I was able to generate hundreds of random results in just a few clicks. This is a great technique to have in your Excel arsenal, and I highly recommend you try it out.
In conclusion, in this episode, we discussed how to generate multiple random results from one formula. We explored different methods, including using RANDBETWEEN, TEXTJOIN, and the F4 function to redo paste values. But, the most efficient way to generate multiple results was by using the WHAT-IF tools and a data table, a method I learned from Professor Benninga. I hope you found this episode helpful, and don't forget to check out my book, Power Excel With MrExcel, for more tips and tricks. Thank you for watching, and I'll see you in the next episode of the MrExcel netcast.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
#excel
#microsoft
#microsoftexcel
#exceltricks
#excelchallenge
#excelformula
#evergreen
#randomdata
This video answers these common search terms:
how to randomize text in excel
how to randomize a list of text in excel
how to pick random names from list in excel
how to random pick names on excel
how to choose random names in excel
how to randomly pick names in excel
how to select random names in excel
how to randomly place names in a grid in excel
how to produce random data in excel?
Table of Contents:
(00:00) Generating Fake Data Using Excel
(00:15) Using Power Query to Split Data
(00:26) Tricks for Speeding Up the Process
(01:07) Using RANDBETWEEN to Generate Random Results
(02:07) Creating Unique Lists with RAND and TEXTJOIN
(04:00) Using PASTE VALUES and F4 to Generate Multiple Results
(05:29) The Fastest Method: Using a What-If Data Table
(07:11) Clicking Like really helps the algorithm
Goal is to create sample data with product;product;product;product
Goal is to always have 2 or more products, up to a max of 12
Store a list of products in a custom list so you can easily generate a column of single products
Using RANDBETWEEN() might return duplicate items in the list
Use RAND() function to decide if this product is included or not
Use TEXTJOIN() to concatenate the non-blanks with semi-colons in between
Now that you have one result, how do you make many results
Surprising that one Copy and multiple Paste Values will paste the current result of the formula
Speed the Paste Values by using F4 to Re do
But - a super fast way: Use What-If Tools and a Data Table with an empty cell as the Column Input Cell
Thanks to Professor Simon Benninga for this method
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Информация по комментариям в разработке