MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Iterate Multiple Random Results


October 16, 2017 - by Bill Jelen

Iterate Multiple Random Results

Using Excel's What-If Data Table to generate a bunch of random results. Even if you have a tricky formula that is the result of several steps, the Data Table will allow you to generate hundreds of answers to the model without repeating the model 100's of times.

Watch Video

  • 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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2155 generate multiple random
  • results from one formula hey welcome
  • back to the MrExcel NetCast I'm Bill
  • Gellin well in addition to the the
  • podcast and writing books I read a
  • monthly article article for strategic
  • finance magazine as working on next
  • month's article where I was showing them
  • how to use power query to split a column
  • of semi colon separated data to rows and
  • I needed to generate some fake data for
  • that and why I didn't open open the file
  • from episode 20 97 I have no idea I just
  • wanted to create some fake data so
  • anywhere from 2 to 12 products in one
  • cell and in the process of doing that I
  • used a lot of tricks from the podcast
  • text join Phil apple banana cherry
  • random walk f4 to redo and I think along
  • the way I discovered some interesting
  • things of how to speed up this process
  • now first off it would have been great
  • if I could have just created one huge
  • massive array formula that would have
  • generated that data I could have copied
  • that formula down but I couldn't reach
  • my copy of control shift enter and I was
  • just looking for something simpler that
  • morning I'm a big fan of ran between I
  • use ran between all of the time so if we
  • had a list of 12 products and then here
  • generated a series of answers using ran
  • between so the index of a 1 to a 12
  • asking for a random number from 1 to 12
  • well every time I press f9 I get a
  • different list of products and then I
  • want a different number of products in
  • each one so here ran between 2 through 7
  • or 2 through 12 or whatever the upper
  • limit is and then using tax joy that
  • awesome new function in office 365
  • separated by semicolon ignore the blanks
  • and then we're going from e2 right there
  • down to anywhere in eetu to eat well
  • based on the sixth value in this case
  • we'll generate that list all right but
  • the reason I don't like this is that I
  • kept pressing f9 ossi I started getting
  • repeats and you know it allegedly this
  • is orders coming from a website and why
  • would someone order elderberry
  • elderberry just doesn't make sense right
  • so I didn't like the chance they're
  • getting date-date I wanted to have a
  • unique list so here's what I decided I
  • was going to do first off I was gonna
  • create a list of the 12 products and I
  • have this memorized as a custom
  • list so I can just generate a nice
  • alphabetic list of items and then I
  • wanted anywhere from two you know to
  • about seven of these and so what I did
  • here as I say equal if the Rand Rand is
  • an awesome function that generates a
  • decimal from zero to 1 is less than
  • point six so in other words in about
  • sixty percent of the cases I want you to
  • bring that product over here to column B
  • otherwise give me nothing quote quote I
  • don't copy that down and what that's
  • gonna do for me is generate a list of
  • products they'll never be any repeats
  • there's no chance of any repeats and
  • every time I press f9 I get a different
  • list of products and yeah it looks like
  • we're getting you know the the right
  • number of products each time alright so
  • now that we have that the new function
  • in Excel in office 365 is text join I
  • love this the delimiter is going to be a
  • semicolon and then ignore empty it
  • doesn't matter I actually ya know it
  • really does matter here that's the
  • important thing we're gonna get over
  • empty true and then here's our list of
  • products like in that okay so there we
  • are we have our our product list for the
  • first row but I need to generate a whole
  • bunch of these and this is where we
  • actually get to the issue the issue that
  • I was trying to solve in this particular
  • case now if I would just copy that
  • formula down alright if I took that
  • original formula and came here and
  • edited this choose the : press f4 to
  • make sure that I'm having an absolute
  • reference and copy it down you'll see
  • that I end up with identical items all
  • the way down it's not very interesting
  • fake data alright so that's not gonna
  • work what I need to do is I need to take
  • the result of this formula and create a
  • whole bunch of them alright so initially
  • I did this I did ctrl C and then I come
  • here and I'm gonna do paste special
  • values or I guess that's just paste and
  • paste values like that
  • alright and what's fascinating to me and
  • I've talked about this once on the
  • podcast before and everyone in the
  • YouTube comments was well of course
  • that's gonna work no what's fascinating
  • to me is I copied cell C 14 and so you
  • would think that when I copied C 14 that
  • text from c14 would have been copied to
  • the clipboard but it's not it's pointing
  • to C 14 right so the first time that I
  • pasted I got cherry date elderberry but
  • he now see the C 14 the marching ants
  • has changed to apple cherry fig and
  • someone to come down here and I'm gonna
  • paste values again and I'm always
  • shocked that it changed to the new value
  • all right so if I just could just paste
  • values paste values paste values paste
  • values it would generate a new answer
  • every single time this time when I paste
  • values apple banana date fig iceberg
  • jackfruit but it's a hassle to grab the
  • mouse and come up here and choose paste
  • and choose values so I'm gonna use the
  • awesome redo function not undo redo
  • which is f4 so f4 paste the new value
  • when I press f4 get but antedate
  • elderberry lime so it's just simple f4
  • down arrow f4 down arrow f4 down arrow
  • right and life is great there I have
  • enough fake data for the article all
  • right but even that is a hassle alright
  • so the method that I learned from a good
  • friend of mine who's now deceased
  • professor Simon Banega taught me this if
  • we have a model and this is essentially
  • a model that is using Rand or ran
  • between and generating you know result
  • what you can do is you can have multiple
  • versions of that result happen all right
  • and we have to start from the cell to
  • the left of the result of our model
  • choose that cell and the cell that
  • contains your formula and then however
  • many you want let's say I needed a
  • hundred of these or 132 of these just
  • copy all right select all the way down
  • and we're going to come here to the data
  • tab the data tab what-if analysis data
  • table all right now I use this all the
  • time to show how to create multiple
  • scenarios but in this case we don't
  • really have anything for row input cell
  • for the column input cell just choose
  • any empty cell it doesn't matter what
  • cell it is and this is going to run this
  • model 132 times each time essentially
  • pressing f9 to generate new random
  • values like that
  • click OK and
  • it works I absolutely loved that doubt
  • this is live every time I press f9 I get
  • a new set of hundred thirty two of these
  • so just copy ctrl C and come out here
  • paste special values and we have our
  • fake list of products and we're ready to
  • essentially do what was back in episode
  • 20 97 split a column of XM I call the
  • next time I call an accent my colon
  • two rows highly recommend you check out
  • that video it's a great video or the
  • November issue November 2017 issue of
  • strategic finance magazine it'll be
  • online in early November alright so all
  • of these methods are in the book power
  • excel with MrExcel 2017 edition click
  • that I on the top right hand corner to
  • check it out recap of what we talked
  • about today the goal is to create a
  • sample data set with semi colon
  • separated products always two or more
  • products up to a max of twelve so I
  • stored a list of products in a custom
  • list so you can easily generate a single
  • column of products using RAM between how
  • that would have worked we have
  • duplicates in the list so I'm using the
  • rand function to say is this product in
  • or not then the text join function to
  • concatenate the non blanks with
  • semicolons in between but there isn't a
  • big question now you have one result how
  • do you make multiple results using the
  • rand function well that's surprising
  • that one copy and then multiple paste
  • values will give you different answers
  • now because it's always wreak
  • recalculating after each paste and you
  • can speed that using f4 to redo the
  • paste values not a bad way to go but the
  • super fast way thanks to Professor
  • Bonita use the what-if tools and a data
  • table with an empty cell as the column
  • input cells and you'll be able to
  • generate hundreds of random results very
  • very quickly alright there you go well
  • hey I want to thank you for stopping by
  • we'll see you next time for another net
  • cast from MrExcel

Download File

Download the sample file here: Podcast2155.xlsm

Title Photo: carufrannco / Pixabay