To spill the Bonus formula at the 06:53 minute mark, use: =CHOOSE(RANDARRAY(C6,,1,3,1),INT(NORM.INV(RANDARRAY(C6),44,10)),RANDARRAY(C6,,1,5,1),RANDARRAY(C6,,C7,C8,1)) This is thanks to our Excel Teammate Exceλambda!!!! Go Team!!!!! P.S. This formula is in download workbook also : )
Awesome Mike! Great way to generate a list of random data from a few inputs. Nice bonus to generate different distribution logic. Just for fun, you can shoehorn everything into a single dynamic formula with the help of CHOOSE, as in: =CHOOSE({1,2,3,4},RANDARRAY(C6,,C4,C5,1),RANDARRAY(C6,,C7,C8,1),INDEX(H3:H6,RANDARRAY(C6,,1,ROWS(H3:H6),1)),ROUND(XLOOKUP(INDEX(H3:H6,RANDARRAY(C6,,1,ROWS(H3:H6),1)),H3:H6,I3:I6)*(1-LOOKUP(RANDARRAY(C6,,C7,C8,1),E3:F6)),2)). Thanks for the Tuesday formula fun :)) Thumbs up!!
@@excelisfun Thanks Mike! Was trying to figure out how to SPILL the NORM.INV function. The only thing I could come up with was CHOOSE again which can be set to SPILL the number of rows with SEQUENCE, but you are forced to put the NORM.INV function in every value argument of CHOOSE, as in: =CHOOSE(SEQUENCE(C6),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10)). The above SPILLS up to 10 results, but would require continuing to repeat the function for the option of many more results. So, not really practical unless the desired number of SPILLED results were limited. Still, fun to experiment :)) Go Team!!!
@@wayneedmondson1065 Yes, that would be hard for a big data set. But fun knows no limit and is not constrained by the spill-less Excel distribution functions!!!!!
Great formula Wayne!!, Great video Mike!! King of statistics!! 😉✌✌ One question, these formula are good for the case or not? =NORM.INV(RANDARRAY(12),44,10) (it spills), even =NORM.INV(RANDARRAY(12,3),44,10) spills.
Thanks Mike....That was great! The Bonus Formula however may require me to rewatch this video =RANDBETWEEN(2,20) more time to grasp your level of thinking. It was awesome!
From my Analytics class, here is a video about other types of variables and distributions that you can use in randomizing formulas: ruclips.net/video/7NNtUs5xAkw/видео.html
Very interesting Mike. Hope all is well with you. I created a lambda for generating random data (on the lambda page on Mr Excel), but this is more interesting and specific. I like it!
Great to hear from you, Rico S!!!! That is cool that you created a LAMBDA : ) there are so many ways to create random data. With the many distribution .INV functions so you can really spread out the data as you would like : )
omg I am so glad I found your channel. I have been trying to figure out a two way index match function for my data set analysis for 3 whole days and I failed. But after checking out one of your vids, I did it in 5 minutes! Thank you so so much!
Yes, but I have not found a good practical use, yet. I just like the F9 ability and the ability to change inputs without having to click refresh too much with worksheet formulas ; )
You the Man Mike! Here's a curve ball for you, do you know if there's a way to apply an absolute cell reference within a formula, via the use of another conditional formula? (e.g. absolute cell refence if true, else relative? Thanks for all the years of great content!
Does the unary + trick work on the distribution formulas for dynamic arrays? Great tutorial as always. In depth, bonus tricks, and interesting I’m working a RANDDATA LET/ LAMBDA where you provide the number of rows and columns to return from a master data table that would. This would be used to create random smaller datasets.
I have question for you. How would you be able to get the dates to randomize, but without repeating the same date in that data set? Would love to be to randomize an adjustable number items/row but randomize all items/rows without repeating. Thank you very much for your response.
Here are two formulas that might work: =INDEX(SORT(CHOOSE({1,2},RANDARRAY(COUNT(Date)),Date)),,2) or =DROP(SORT(HSTACK(RANDARRAY(COUNT(Date)),Date)),,1)
@@excelisfun Wow! Mr. Girvin - Thank you very much for the prompt response. I will give them a try and see what happens. Might have to watch some INDEX and CHOOSE videos first.
Great little exercise!! Wait,… what?! Those Stats functions don’t vectorise? Come on Microsoft, fix that glitch already!… :-) PS: shouldn’t you be using ‘your’ banker’s rounding there? ;-)
Yes, too bad about the stats functions not spilling. I guess if we were drinking from the stats functions, that no spill aspect would be great! lol Banker's rounding? Why? It is just random data.
Two: 1) Monte Carlo Simulations used in accounting, finance and analytics. 2) Teachers, book writers and bloggers that need data sets to help teach and have relevant data : )
This is really awesome, is there a way to expand this to say display inconsistent group count information from a database? Ex. I have 3 groups A,B,C (10,15,7). If I type A in a cell it counts the rows and gives the data for the 10 locations in a nice table? Almost like a pivot table but formula driven instead. Sorry anti-pivot person, they are clunky.
Hi Sir, How are you? Sir, I have office 2019. I want to randomise some texts and number but without repeatation in Cell "A2" to "E10" Excel 2019. Would you kindly teach us, how to do it?
@@excelisfun Thank you Sir for your reply. I've learnt a lot of things from you. I've been your loyal subscribers since 2013. You are doing absolutely greate work. May God bless you.
To spill the Bonus formula at the 06:53 minute mark, use:
=CHOOSE(RANDARRAY(C6,,1,3,1),INT(NORM.INV(RANDARRAY(C6),44,10)),RANDARRAY(C6,,1,5,1),RANDARRAY(C6,,C7,C8,1))
This is thanks to our Excel Teammate Exceλambda!!!! Go Team!!!!! P.S. This formula is in download workbook also : )
Awesome Mike! Great way to generate a list of random data from a few inputs. Nice bonus to generate different distribution logic. Just for fun, you can shoehorn everything into a single dynamic formula with the help of CHOOSE, as in: =CHOOSE({1,2,3,4},RANDARRAY(C6,,C4,C5,1),RANDARRAY(C6,,C7,C8,1),INDEX(H3:H6,RANDARRAY(C6,,1,ROWS(H3:H6),1)),ROUND(XLOOKUP(INDEX(H3:H6,RANDARRAY(C6,,1,ROWS(H3:H6),1)),H3:H6,I3:I6)*(1-LOOKUP(RANDARRAY(C6,,C7,C8,1),E3:F6)),2)). Thanks for the Tuesday formula fun :)) Thumbs up!!
That is some rad showhorning, O Master Formula Guy Wayne : ) : ) Thanks for sharing with the Team. I will add it to the download workbook. Go Team!!!
@@excelisfun Thanks Mike! Was trying to figure out how to SPILL the NORM.INV function. The only thing I could come up with was CHOOSE again which can be set to SPILL the number of rows with SEQUENCE, but you are forced to put the NORM.INV function in every value argument of CHOOSE, as in: =CHOOSE(SEQUENCE(C6),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10),NORM.INV(RAND(),44,10)). The above SPILLS up to 10 results, but would require continuing to repeat the function for the option of many more results. So, not really practical unless the desired number of SPILLED results were limited. Still, fun to experiment :)) Go Team!!!
@@wayneedmondson1065 Yes, that would be hard for a big data set. But fun knows no limit and is not constrained by the spill-less Excel distribution functions!!!!!
Great formula Wayne!!, Great video Mike!! King of statistics!! 😉✌✌ One question, these formula are good for the case or not?
=NORM.INV(RANDARRAY(12),44,10) (it spills), even =NORM.INV(RANDARRAY(12,3),44,10) spills.
@@Excelambda Awesome! Thanks for sharing those formulas. Go Team!!
Beautiful template Professor! Great reminder to not hard code numbers in formulas that may change. So flexible! 👍👍👍
Yes, Excel's Golden Rule is the best = as you say: so flexible!!!
Date Randomization,Price Look up This video useful in Stock prices Analysis as well.very useful video.
Just another great video from the XL-Entertrainer. I really enjoyed the lesson. 👍
Glad you enjoyed it, Roger!!!!!
Boom!Super Cool Tutorial With Some Awesome Formulas...Thank You Mike :)
You are welcome, darryl. Thanks for your awesome BOOM!
Mike, you are simply great! Hats off for this video! Learnt so many tricks in one go! Thanks a lot! 😊👍
You are welcome a lot, Vijay!!!
Thanks Mike. Nice Video for Breakfast this morning. :) :)
Yes!!! You are welcome for the breakfast, Formula Guy John : )
Thanks Mike....That was great! The Bonus Formula however may require me to rewatch this video =RANDBETWEEN(2,20) more time to grasp your level of thinking. It was awesome!
Nice way to generate a random number of videos lol. You are welcome, Matt!!!!
Lovely. Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher Syed MM : ) : )
Great example how to randomise data
Glad you like it, Vida : )
From my Analytics class, here is a video about other types of variables and distributions that you can use in randomizing formulas: ruclips.net/video/7NNtUs5xAkw/видео.html
Thank you Mike 🌺🌺
You are welcome, Yaser!!!
Very helpful information. Thank you for sharing.
You are welcome for the share, Paulo!!!
Great Teacher 🙏🙏🙏
Glad you like it all, RAJESH!!!!
Very interesting Mike. Hope all is well with you. I created a lambda for generating random data (on the lambda page on Mr Excel), but this is more interesting and specific. I like it!
Great to hear from you, Rico S!!!! That is cool that you created a LAMBDA : ) there are so many ways to create random data. With the many distribution .INV functions so you can really spread out the data as you would like : )
Liked and Thumbs Up👍 Sirji...
Thanks, Mahesh!!!!
This is awesome Mike, thanks
You are welcome, Chris : )
omg I am so glad I found your channel. I have been trying to figure out a two way index match function for my data set analysis for 3 whole days and I failed. But after checking out one of your vids, I did it in 5 minutes! Thank you so so much!
You are welcome so much, Adrian!!!
Great. Thank you!!
You are welcome, Santiago!!!
Always amazing..
Thanks, usman : )
@@excelisfun Welcome Mike
Perfect
Glad you like it, Hussein!!!
Have you ever used Power Query to create random data sets?
Yes, but I have not found a good practical use, yet. I just like the F9 ability and the ability to change inputs without having to click refresh too much with worksheet formulas ; )
Thanks sir🙏
You are welcome, Kamlesh!!!!
First comment, great as always
You get the first place trophy!!!!! : )
You the Man Mike! Here's a curve ball for you, do you know if there's a way to apply an absolute cell reference within a formula, via the use of another conditional formula? (e.g. absolute cell refence if true, else relative? Thanks for all the years of great content!
Does the unary + trick work on the distribution formulas for dynamic arrays?
Great tutorial as always. In depth, bonus tricks, and interesting
I’m working a RANDDATA LET/ LAMBDA where you provide the number of rows and columns to return from a master data table that would. This would be used to create random smaller datasets.
I do not understand what you are asking. Sorry.
I have question for you. How would you be able to get the dates to randomize, but without repeating the same date in that data set? Would love to be to randomize an adjustable number items/row but randomize all items/rows without repeating. Thank you very much for your response.
Here are two formulas that might work:
=INDEX(SORT(CHOOSE({1,2},RANDARRAY(COUNT(Date)),Date)),,2)
or
=DROP(SORT(HSTACK(RANDARRAY(COUNT(Date)),Date)),,1)
@@excelisfun Wow! Mr. Girvin - Thank you very much for the prompt response. I will give them a try and see what happens. Might have to watch some INDEX and CHOOSE videos first.
Great little exercise!!
Wait,… what?! Those Stats functions don’t vectorise?
Come on Microsoft, fix that glitch already!… :-)
PS: shouldn’t you be using ‘your’ banker’s rounding there? ;-)
Yes, too bad about the stats functions not spilling. I guess if we were drinking from the stats functions, that no spill aspect would be great! lol
Banker's rounding? Why? It is just random data.
@@excelisfun Because they're financial values?
(Note to self: add the Banker's rounding Excel function to LAMBDA.LET Library).
@@GeertDelmulle Cash register data uses the typical round, that is why I always use ROUND : ) Those darn bankers... lol
@@excelisfun LOL! :-)
Can anyone share where would they have applied techniques such as this? I think its brilliant but I'm challenged with finding applications for this.
Two: 1) Monte Carlo Simulations used in accounting, finance and analytics. 2) Teachers, book writers and bloggers that need data sets to help teach and have relevant data : )
This is really awesome, is there a way to expand this to say display inconsistent group count information from a database?
Ex. I have 3 groups A,B,C (10,15,7). If I type A in a cell it counts the rows and gives the data for the 10 locations in a nice table? Almost like a pivot table but formula driven instead. Sorry anti-pivot person, they are clunky.
Not sure. You can try this great Excel question site: mrexcel.com/board
Hi Sir, How are you? Sir, I have office 2019. I want to randomise some texts and number but without repeatation in Cell "A2" to "E10" Excel 2019. Would you kindly teach us, how to do it?
I am not sure how to do that. You can try this great Excel question site: mrexcel.com/board
@@excelisfun Thank you Sir for your reply. I've learnt a lot of things from you. I've been your loyal subscribers since 2013. You are doing absolutely greate work. May God bless you.
@@kartickchakraborty9135 I am glad to help when I can : )
U+2764