Generating Random Sample Data in Numbers

If you are building a spreadsheet in Numbers it can be useful to have sample data to help construct your tables and get things right before you have real data to enter. You can use the RANDBETWEEN function to create random numbers. Then if you copy and paste correctly you can turn those formulas into static numbers that won't change.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. On today's episode let me show you how to quickly and easily create sample data in Numbers.
I'm working on building an online course right now that will teach people how to use Mac Numbers. In doing so I'm using a technique all the time that I want to share with you that could help you if you are developing a spreadsheet. That is the ability to create sample data very quickly.
For instance, say you're creating a spreadsheet and you want to keep track of inventory in stores. So you've got various products (apple, orange, pear, peach, banana) and you also have stores. So Store A, Store B, Store C, Store D, Store E. Let's say you wanted to create sample data for all of this. 
Now imagine if there were a hundred stores and there were fifteen different products. Entering in a number, you know just typing in something random, takes up a lot of time and your data really isn't truly random. Say you just want to have an inventory number between one hundred and two hundred. 
Well, you can put a formula in here and use RANDBETWEEN. So I started typing rand and I get some hints here and I'll hit RANDBETWEEN. It allows me to put a lower amount. So let's say I want a minimum of 100 and a maximum of 200. Then I can copy that and paste that through here and I get all these random numbers. So great I've got random data.
The only problem is it will change every time something else changes in the spreadsheet. So I enter a number here, 0, and you can see all those numbers change. 
So a way to keep this consistent so I can test out ideas and how this table works is I can select it all, Edit, and I'll Copy and immediately Paste Formula Results. When I do that now if I look here and I select a cell and it says the Actual value is that value. There is no more formulas in here. This stuff won't change anymore because these are numbers just as if I had entered them manually.
Let's say, for instance, if I wanted to create some Footer cells here and do some prices. Some let me put some Footer cells here, shrink the table up. In this one I'm going to put the price and let's say I want these values here to be between twenty cents and fifty cents. So what I can do is RANDBETWEEN but that's only for whole numbers. But that will work out fine because let's do cents. So then 20 and then 50 as the upper. Then I'll multiple the result by .01. There I go. I get that. I'm going to Copy and Paste this formula across and it will change anytime I change anything.
So I'll do the same thing. Select it all, Edit, Copy, and Paste Formula Results. Now these are static numbers here. So now I can do things like create a total, multiple by the price, and paste that across. Let's go, to be neat, and create these changes to currency. So now I've actually got, maybe, a spreadsheet that's kind of working for what I want. These numbers are static. I can change them to see how something in the spreadsheet changes and make sure I get things working right.
Then I can could clear out the data and enter in the real data later on now that I know my formulas work right, my table looks good, and everything is going to work great. This works especially well for charts. I'm going to Undo that here and say if I want to do a chart with this information.
I can do say a Scatter chart here for all these numbers and see whether or not this works for me and how I want to adjust this chart with sample data. Then later on when I've got real data I won't have to scramble to work on the chart. The chart would already be exactly like I want it even before I have my first real number.

Comments: 3 Responses to “Generating Random Sample Data in Numbers”

Joel

9 years ago

I often have to work also in Excel (please don't hit me), but I don't know if such a formula exists there. Do you?

Gary Rosenzweig

Joel: In Excel it is the same function (RANDBETWEEN). Then you Edit, Paste Special and choose to paste the values.

Thanks. I'll definitely have need for that.

Comments Closed.

Generating Random Sample Data in Numbers

Comments: 3 Responses to “Generating Random Sample Data in Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information