multiple outcomes of randomized dataset

New Contributor

Hi, I have a question whether, and if yes how, it is possible to automatically generate a list of multiple outcomes of a randomized dataset? My situation: I built a randomized dataset, so everytime it is recalculated the complete dataset is different. The outcome of the dataset is the comparison of food waste. This means, with each re-calculation there is another difference. I would like to do statistical tests on this difference, but it takes a lot of time to copy paste the outcomes a 1000 times after it is re-calculated again. Is there a way that excel can automatically generate a list of the first let's say 100 outcomes of a randomized dataset? (So in the example datasheet i want to create a list of 100 possible outcomes of the yellow cell) Thanks in advance! 

5 Replies



I'm quite sure the answer is "Yes"


But exactly HOW would depend very much on HOW you have arrayed your data. If you're able to do this without disclosing proprietary data, it would help us help you if you were to attach a copy of the spreadsheet you have developed. If that can't be done literally, then some mockup that is laid out as you have it.


That aside, without seeing what you have, and assuming that you are somehow graphing the results, I would visualize creating 100 (or a 1,000) columns with the RAND function in each cell (or RANDARRAY) ....from my own very infrequent use of RAND, I am aware that it recalculates any time you change anything on the sheet, so the results might be always changing.


I hasten to add that I'm by no means an expert on this subject. I've used random functions infrequently, and often just to scramble a list of names for assembling a small group of people in, uh, random combinations.


So there no doubt are others in the techcommunity who would be more equipped to answer you. I think they, too, would benefit from seeing the data you're working with, if that can be shared.

@mathetes Thank you so much for the quick response! I created a simplified worksheet to give more insight in the problem. In the sheet the data point I like to work with is marked yellow. It depends on the data above it, which are random numbers, so every time it is re calculated, the yellow cell gets a different value. I would like to get the first 100 (recalculated) values of the yellow cell.



And it would seem to me that just making multiple copies of that column, as I've done here, accomplishes the desired result. I didn't do 100 copies, but you get the idea I'm sure.

@mathetes Hmm yeah, unfortunately that is not possible for my real data set since that one is a little more complicated. But I'll find a way, thank you very much for your help!



Well, my very first sentence on my very first response did say that it would depend on how your data are arrayed. I guess that's the issue. It sounds like a macro might be in order (unless a rearrangement of the data is possible). I'm not a macro person; sorry.