From the course: Complete Guide to Excel Statistics with Copilot

Copilot: Basic assistance

From the course: Complete Guide to Excel Statistics with Copilot

Start my 1-month free trial Buy for my team

Copilot: Basic assistance

“

- [Instructor] All right, so let's begin our exploration of Copilot. And what you'll see here is that we have some very simple spreadsheets, some very simple sets of data. And what we're going to do is we're going to see what Copilot can do to help us add things up and multiply and maybe even generate some random numbers. So let's begin here. When you go to the home menu, you'll see Copilot is available to us in here. So we're going to click on Copilot, and you'll notice that along the side we have a place where we can ask it to help us. Now, very important thing, in most cases, what we're going to want to do is we're going to want to take our data, which is just in a number of cells right here, and we're going to want to turn it into a table. So you'll go up to home, format as a table, you can pick out whichever formatting you prefer. We say yes, we want tables to have headers, and then we say, okay, so we're all set. Now, Copilot tends to work much better when you put your data in tables. Now it creates some other issues sometimes when you're working with personal data sets, but for now, let's continue using this right here where we're living inside the table. The other thing, important thing that you'll want to consider is, as you ask Copilot questions about your table, you're going to want to make sure that you are living inside the table. So you don't want to be sitting out here because it sometimes thinks that you're asking it questions about what's happening in this area. By being in the table, it now knows that you are asking questions about that. So first thing we want to do is let's say that we want to figure out what's going to be amount of tax associated with each sale. So let's go ahead and do that. And you'll notice, I just asked it a question in a very simple format, "What is the amount of tax for each sale amount?" Let's see what happens here. All right, so it believes, it has figured it out, and it wants to allow us to add this in as an additional column. So let's go ahead and do that. And you'll notice we came up with some very simple numbers. So the tax rate here for $10, if it's a 5% tax rate, should be 50 cents. A tax rate for a 5% for $20 is $1. So it did actually get it done for us pretty easily. You'll also notice it gave us another option. If instead of it adding the column for us, the other thing that could have done is it could have actually given us the formula and we could have input the formula and then copied down. So that would've been another option. So sometimes it gives us one option, sometimes it gives us multiple, sometimes the option it gives you isn't very good, but in this case it seemed to work pretty well. Let's see if it can do something as simple as, and then you'll notice here, I've already formatted our data into a table. So what we're going to do is, again, let's be, make sure that we're inside the table and then let's ask it to, all right, we're going to add up the total amount of potatoes and see what it does here. And so in this case, it's giving us, again, two different options. One is it can give us a formula. So let's go do that first. I'm going to copy, you'll notice I copied it to the clipboard, and I can put it right here if I want. So let's say I want to say this is the total amount of potatoes, and I'm going to put it right here in this. So I'm going to copy this and then paste it. And you'll notice it did add them all up. And we can even test here by adding in 20 pounds. And that changes. Now the other option it was giving us is inserting a row. And you'll notice here, it allows us to do that as well. And in both cases, if we decide to change that from 10 pounds to 20 pounds, both numbers should go up. So again, work pretty well for something simple like this. Let's go and create some random numbers. Every once in a while we're going to want to create some random numbers to test some of our statistics. So the first thing we want to do, it's pretty simple. What we want to do is just generate a random integer. And so what you can see here is it didn't really know what type of integer we wanted. So it took a guess. And it said, look, if you're looking to generate a integer between 1 and 100, I have a formula for you. And what you can do is you can copy it and you can paste it over here. And we just did that. That's great. Now suppose you say, wait, I didn't want a random number between 1 and 100. I actually wanted a random number between 10 and 20. And there we got it. So again, very nice, very simple. If we wanted to generate multiple random numbers, we could just copy this formula down. How about if you don't want an integer? Instead you wanted a decimal. So let's go ahead and ask it to. And so now it gives us a new set of possibilities. So we can again, do it multiple ways. This one's pretty simple. This is actually a decimal a number with a decimal, it's not exactly what we wanted. If we wanted a random number between, let's try this one here, and it doesn't know what we're saying there. So in this case, it didn't quite understand what we wanted. Let's see if we can ask it again. Oh, actually I should have read a little bit more closely because it's saying to us, you can use the rand function to generate a random decimal between 0 and 1. So it's actually telling us right now, if we just want to use this, that would've worked. If you want to generate a random decimal between two specific values, you can use the following formula. So in some cases, when it doesn't know exactly what you want, it'll give you some options. And then if you read the prompt, it will tell you more specifically which numbers you may want to be placing into your formula. So in this case here, it said, well, we can do a random decimal, a random number with a decimal, integer with a decimal or just a random integer. Let's do one other really interesting thing that is helpful, especially for a course in statistics. Let's generate a random set of numbers in a normal distribution. So let's ask. All right, so it's given us a number of different options here, okay? So again, if you read through this, it'll give you some options. And it says here, you can use the NORM.INV function along with your RAND function to generate a set of numbers from a normal distribution. Here's how you can do it. Determine the mean and the standard deviation for the normal distribution. Use the following formula to generate a random number from a normal distribution. So let's copy this. Let's put it right here. All right, so now what it's waiting for is it's waiting for us to finish this off. So let's say that we want our random distribution to include the average of our standard distribution will be 30. And let's say that it has a standard deviation of 5.5 and let's see what happens. And there you go. Now this is only one number. So what we can do is we can drag that down and what you'll notice is it's given us a number of values. It looks like it gave us 20 different values, and these would be pulled from a normal distribution. So when you think about a normal curve, more of these numbers are going to be near our mean of 30 and the farther away we get from 30, the fewer numbers we're going to have in our distribution, or that's what we would expect. All right, so you've seen a number of different ways that you can use Copilot to do some very simple things. Everything from adding to finding tax rates, to also generating an assortment of random numbers.

Contents