9:15 am

Using Trend Lines In Numbers

Trend lines can help you find meaning in what could seem like random data. One example looks at daily sales for a store and finds an upward trend over the course of a year. Another example looks at physics data and converts it to a mathematical formula.

Video Transcript (Click to Expand)
Hi, this is Gary with MacMost.com. On today's episode let's look at using trend lines in Numbers for Mac.

So let's look at simple example. I'm using Numbers 3.6.2 and we'll see how trend lines can be very useful for seeing a trend in a lot of data.

I've got this set of numbers here. It's a table with everyday of the year and the number of sales on the right. If I try to do a chart for this I'll hit Chart and I'll just do a simple chart right here. I can see here everyday has a different bar going all the way across. It's hard to see if there is a trend. I get a feeling that sales were stronger at the end of the year than at the beginning of the year. But, you know, there's peaks and valleys everywhere. So is there a trend from the start of the year to the end of the year. Are sales, indeed, going up.

So you can figure that out using a trend line. What I'm going to do is I've got the Format bar turned on here and I'm going to select, in the chart, this series. I'm going to click inside here so I'm selecting the data inside not the chart itself.

I'm going to go to Series and then there, sure enough, is the option for Trendline. I'm going to turn on Trendline and I'm going to use Linear because I'm looking to see if things are increasing. If there is a trend going on. So Linear will do it for me. It's going to hopefully show me a line moving upwards.

I'll select it and sure enough there it is. I can turn it off here and see that there is a line going across that is definitely trending up. Now if I want to see it a little bit better I can maybe select Series again, go to Style, change the Fill to a color fill, say a gray that kind of puts the data in the background a little bit and makes the trend line itself pop so I can see it. So I can see sales are gradually increasing throughout the year even though with 366 data points it's really hard to see that from the individual bits of data.

Now you have more options when it comes to trend lines. So I'm going to select this Series again here and I've got the trend line. I can name it here but I can also show the Equation and the R Value. Now if you don't know what these are then it's probably not something you need to worry about. The equation here is a simple linear equation and it can very useful if you are using this in a math project or something to be able to see the equation of that line and we're going to see how that's even more useful in the next example.

Here I've got a different set of data. I've got the data from a projectile being shot from horizontal position 0 to 0 with a velocity of 100 vertically, a hundred up, and 100 horizontally. So it's continuing to move horizontally. I'm not taking any kind of thing like wind or anything into account. Just 100 per second and being shot at a 45 degree angle. So 100 and 100 there. The vertical position starts at 0 and then it will continue to move up. But vertical velocity here, you can see in the formula, is being affected by gravity. That's that negative 9.8 meters per second there.

So basically it's physics. It's being shot up but gravity is slowly slowing it down as it's going up. Eventually gravity now is pulling it down and see that reflected here in the Vertical Position is rising, it gets to a peak point, then it starts falling.

I can easily show this visually by doing a chart. I don't want to show the vertical velocity. I want to just select column A and then Control click column C and select these two columns. Then I do Chart and a line chart and I get this chart here that really clearly shows the vertical position and the horizontal position and you can see it as the arc of say a projectile through the air.

So where do trend lines come in for this. Well, if I select the Series here, I'm going to click inside and select this series I can go and set a Trendline. Now linear doesn't make any sense because this isn't moving in a linear fashion. It's going to say over time it's going to continue to move down because I'm ending here kind of underground. It doesn't mean anything.

What you want here is something that's going to be a Polynomial because a polynomial represents this type of data and, in fact, if I show the equation I get a polynomial equation that perfectly represents the math underlying the acceleration of velocity here.

So you can actually use this formula to show the position of the cannonball at any point, x and y. So it's pretty useful here and it shows that you can use these different trend liness for different things. There are reasons like this to use polynomial, there's reasons to linear, and there will be different mathematical reasons you may have to use some of these other settings here.

But it's the basics of how you do it and how you use trend lines in Numbers to show you different trends and to bring out more information, like this formula here, from the data.