First I would like to say that I am not a statistician nor am I good in the field.

I have been collecting data for over a period of e.g 100 days and each day has a varying amount of data that I can collect. So for example yesterday I collected 10 and tomorrow I might collect 20 or 2 etc.

I collected all this data and made a graph in excel which basically shows the flow.

Now, I wanted to see how based on all the data I have up till now, what can I expect tomorrow?

Excel provides some (click and produce) trend options like exponential, linear regression, etc. So, although I don't fully understand how they work, I used the linear regression trend. In my report draft, my teacher insist on knowing why linear regression but I don't know a right defense.

Was using linear regression right and if so, how may I defend the usage in my scenario ?

Please use the image below for reference.

enter image description here

  • $\begingroup$ You'll have to give us a little more information on what you're trying to show. Linear regression is used when you want to relate one variable to another. For example, you want to know if someone's height can be correlated to their IQ. A linear regression will show some relationship (or no relationship) between height on the x axis and IQ on the y-axis. If you're trying to show a relationship like that, then linear regression may be appropriate. Your description sounds a bit more like a time series analysis, but it's hard to say. Tell us what you're trying to see and we can help more! $\endgroup$
    – Duncan
    Jun 3, 2014 at 18:26
  • $\begingroup$ @Duncan how about data on the x-axis and the time on the y-axis ? Is it not sufficient ? I am trying to display received information(messages) over time and what is expected in the closest future. $\endgroup$ Jun 3, 2014 at 18:29
  • $\begingroup$ It sounds like your data may very well fit a time series better, but it's hard to say. Can you edit your question to include an example of the data that you're trying to analyze? Don't use the real data - you want to protect it - but come up with some dummy data that mimics the structure of what you're collecting and it'll help us get you to the right answer. $\endgroup$
    – Duncan
    Jun 3, 2014 at 18:40
  • $\begingroup$ @Duncan thanks for your reply . I just added an image of sample data. Thanks $\endgroup$ Jun 3, 2014 at 18:41
  • $\begingroup$ @Duncan The first graph is actually about growth. While the record is about instability. Those are the linear trends $\endgroup$ Jun 3, 2014 at 19:32

2 Answers 2


The image definitely helps. Using linear regression in a situation like this is generally appropriate if one of two things is true:

1) You have a reasonable belief that the relationship between time and your data is linear. You can eyeball this, or do the regression line. If you have a lot of points that are far away from your line, or if the data points look like they would fit a curve better or are more random, then don't use linear regression. Once you do the regression line, you can also look at "R squared", the coefficient of determination, which will help tell you if the line explains the information that you have. The closer R squared is to 1, the better.

Here are instructions on running regression and getting the R squared value in Excel:


2) If the time values are somehow independent of each other. This is usually not true. For example, you are counting the number of clouds in the sky. Obviously if you collect data at 9am and 9:05am, the numbers will be very similar - likely much closer than data collected at 9am and 9pm.

If linear regression does not match one of the above two criteria, then I'd suggest doing time series analysis instead. Time series deal with information that has a specific temporal ordering, as yours seems to. Part of why this is important is that the performance from minute-to-minute (or day-to-day or whatever your time period is) may not be independent. In the image above, a really good week may be more likely to lead to a not-so-good week. Also, due to general shopping trends, weeks that are closer together may have more similar values than weeks that are farther apart (think of it like this - lots of people shop retail in the November-December timeframe due to holiday sales and gift-giving holidays, whereas fewer people will shop in January-February since they just spent a lot of money).

Time series graphing is pretty straightforward in Excel (http://www.mrexcel.com/articles/excel-time-series-chart.php), but that will give you limited predictive ability as it's just a graph. Time series analysis will allow you to predict tomorrow's value, but is much more complicated in Excel. Here's a site that looks good based on a quick google search:


You can also try a google search for "Time Series Analysis in Excel" for other resources - there's a 10 minute youtube video that may help, and a couple other academic/government resources that show up in the first 10-20 results.

  • $\begingroup$ Very nice, I never knew you could do time series in Excel $\endgroup$
    – Marc
    Jun 3, 2014 at 19:08
  • $\begingroup$ The chart is easy, the analysis is not. The site I linked has a downloadable Excel file with a bunch of VBA macros that allow it to do analysis, but it's not a natural function for Excel. If you're serious about time series analysis, I'd suggest R, SAS, or some other real statistical program/language. $\endgroup$
    – Duncan
    Jun 3, 2014 at 19:09
  • $\begingroup$ @Duncan i really appreciate your help.It's very informative. I just added a plot of my real data. Y axis is the data and X is the time(days). And that's my Rsquared value. Any further comments ? $\endgroup$ Jun 3, 2014 at 19:26
  • $\begingroup$ That's a very high R^2 and the data definitely looks linear so it passes both the "eyeball" and the R squared test. I'd say linear regression is a good choice here and will give you reasonable predictions. $\endgroup$
    – Duncan
    Jun 3, 2014 at 19:34
  • 1
    $\begingroup$ A very low R^2 means that the regression line doesn't explain the data very well. Different people have slightly different interpretations of what R^2 means, but in general if the R^2 is that low then the linear regression is not very useful. Also, the data bounces around a LOT, so it fails the "eyeball" test also. Try changing the time scale. It's possible a linear trend may emerge if you look at a weekly score rather than a daily one. If that doesn't work, try doing a time series analysis. $\endgroup$
    – Duncan
    Jun 3, 2014 at 19:54

This is a question that is always difficult. If you want to do it completely correct you should determine by your knowledge of the underlying process what sort of relation you believe there to be between your $y$ and $x$ variable and then use this relation in your regression (without looking at the data). However, this most often doesn't happen, so (skipping some philosophical discussions and mathematical options) you should just look at your data and determine by its 'shape' what relation best describes the $y$'s in terms of the $x$'s. However, always keep in mind what you already know about the proces and include this knowledge in your choice.

  • $\begingroup$ Thanks for your reply. I added a picture which best describes the scenario. $\endgroup$ Jun 3, 2014 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.