Great post! I wanted to begin my video blogging with a post about creating random data but you beat me to the punch. I learned some good pointers. I was using vlookups against a table that had a index number as the first column and the value as the second column. My first parameter in the vlookup was the randbetween function and the upper bound of the randbetween was determine by the size of the table that had the possible random values. The index function is more succinct then the method I was using. Also thanks for the tip of how you can select multiple rows using relative referencing. I enjoyed the video!
Wonderful video! What was the source you went to (assuming you did not create these yourself) for those initial company names, street names,and first & last names? Did you get those online?
Cool :) Any idea how to create a random set of number within a given range but to exclude a certain numbers: RANDBETWEEN(1,10), BUT NOT 5 and 7 (for example)
Michi Karpfen It's good for smaller datasets, but I'd like to have a dynamic formula that can be of use for this or any other datasets. It's gonna be tricky :) Thanks anyway, maybe Mike (ExcellIsFun) can think of some robust formula ;)
Laza Lazarevic Guess if you exchange in randbetween to something like 1:counta(column where values are) and for the values create a list that is entered instead of hardcoded values, it will do the job. No time to test it, but it's worth a try ;)
One way would be to used conditional formatting to highlight any duplicates. If there are none, you're good. If there are any, it's likely to be just a few that you can manually change.
Great random data tricks!
Great post! I wanted to begin my video blogging with a post about creating random data but you beat me to the punch. I learned some good pointers. I was using vlookups against a table that had a index number as the first column and the value as the second column. My first parameter in the vlookup was the randbetween function and the upper bound of the randbetween was determine by the size of the table that had the possible random values. The index function is more succinct then the method I was using.
Also thanks for the tip of how you can select multiple rows using relative referencing. I enjoyed the video!
Wonderful video! What was the source you went to (assuming you did not create these yourself) for those initial company names, street names,and first & last names? Did you get those online?
Cool :)
Any idea how to create a random set of number within a given range but to exclude a certain numbers:
RANDBETWEEN(1,10), BUT NOT 5 and 7 (for example)
how about a combination of randbetween and choose. something like =choose(randbetween(1;8);1;2;3;4;6;8;9;10)
Michi Karpfen
It's good for smaller datasets, but I'd like to have a dynamic formula that can be of use for this or any other datasets. It's gonna be tricky :)
Thanks anyway, maybe Mike (ExcellIsFun) can think of some robust formula ;)
Laza Lazarevic Guess if you exchange in randbetween to something like 1:counta(column where values are) and for the values create a list that is entered instead of hardcoded values, it will do the job. No time to test it, but it's worth a try ;)
Michi Karpfen
Sounds goog
Thanks
Cool. How do we make sure there are no duplicates?
One way would be to used conditional formatting to highlight any duplicates. If there are none, you're good. If there are any, it's likely to be just a few that you can manually change.
You gave me an idea for formula:
=INDEX($K$2:$K$11;RANDBETWEEN(1;COUNTA($K$2:$K$11)))
XL 40 trick