2
$\begingroup$

I know my title seems to go against what an outlier is, but I don't know any other way of phrasing it.

Let's say I have a spreadsheet, and a column with the following values:

[From newest-to-oldest] 100, 9, 7, 100, 7, 7, 100, 9, 9, 100, 7, 9

If we were to disregard the 100's for a second, the average would seem to be 8; however, every third entry, the value seems to (reliably) jump up by 92+ the average. What I'm trying to figure out is how to predict what the next value will be given what we've seen before. So, if the last few values where:

[From newest-to-oldest] 9, 100, 7, 9, 100, 7, ect.

It should say "the next value should be 8 ± 1." If the values where instead:

[From newest-to-oldest] 9, 7, 100, 7, 7, 100, 9, ect.

It should say "the next value should be 100 ± 0." How would I go about doing this? This is all in excel (Google Spreadsheets to be more precise). I originally tried to find the outliers and throw the two sets of data into separate columns. Then, depending on the interval, choose the average from one of the two columns. However, since the 100's aren't really outliers (being that they're so common), using standard methods of finding them didn't work.

Improve this question
$\endgroup$
6
  • 1
    $\begingroup$ Do the 100s arrive at regular intervals? Or do they arrive irregularly? (In your example, every 3rd item is 100 and a remarkably simple model to forecast $y_t$ would be $\hat{y}_t = y_{t-2}$.) $\endgroup$
    – Matthew Gunn
    Commented Jan 26, 2017 at 2:59
  • $\begingroup$ They arrive at regular intervals. Also, the upper value isn't always 100 (I just used 100 as an example. It could be 101, 99, 98, 102, but they hang steadily around their own upper mean). I know I could hardcode ŷ = yt−2 but I don't know what the interval is going to be; this is just an example column. I have dozens of columns and each have their own interval with their own upper and lower means, but the intervals are steady. I know I could go through each column and find it out by hand, but I want the spreadsheet to be dynamic. $\endgroup$
    – David Perez
    Commented Jan 26, 2017 at 3:15
  • 3
    $\begingroup$ The first thing I would try are some basic ARMA models. The basic AR(p) model can be estimated with linear regression of the form $y_t = a + b_1y_{t-1} + b_2 y_{t-2} + \ldots + b_p y_{t-p} + \epsilon_t$ where $p$ is the number of lags. This type of model can capture all kinds of cyclic behavior. $\endgroup$
    – Matthew Gunn
    Commented Jan 26, 2017 at 3:19
  • $\begingroup$ If you identify a regular periodicity, I'd suggest looking at seasonal differences. FOr your above data, consider looking at $y_t-y_{t-3}$; If you have a specific set of data can you show a plot of the data in time order? $\endgroup$
    – Glen_b
    Commented Jan 26, 2017 at 8:34
  • 2
    $\begingroup$ To me an outlier is whatever causes surprise in relation to the majority of the data, and in saying that I stand on the shoulders and sit at the feet of two excellent statisticians. For more about that definition, and much else, see stats.stackexchange.com/questions/78063/… The ideas that outliers exist objectively and can be identified unproblematically cause more problems than they solve in my experience. $\endgroup$
    – Nick Cox
    Commented Jan 27, 2017 at 11:56

1 Answer 1

Reset to default
0
$\begingroup$

Thank you Matthew Gunn and Glen-b! Your responses led me to a YouTube video by Jalayer Academy titled "Time Series Forecasting with Google Sheets" which, to some extent, is a solution to my problem. It's not a complete solution, however, since it requires me to know what the interval will be, but I guess I can hardcode three-to-five of the most common intervals I've seen and have Excel brute force its way through the calculations, finding which of the five gives me the line of best fit.

It's not a perfect solution, but it's a solution and appreciate you guys for leading me there.

Improve this answer
$\endgroup$
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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