our article is time-based, that means is my article search more in a specific time. as you can see in under chart this article search more in specific period time.

enter image description here

if my dataset looks like this(it can be a json,csv,xlsx) :

enter image description here

How can I find the trend time of my article (time between the red line in image 1)

I need to know that period of time to be ready for next year.

(I need excel solution for that, but if you can solve this problem with other ways and programming language it's ok)

  • $\begingroup$ First you have to clearly define what's a trend in your data set. My suggestion would be, You can use timestamp as the feature in the sense, you can make bins for let's say months or weeks or whatever granularity you want(just don't take at day level since, in your dataset, it looks like time column in unique which isn't a good feature! that's why I suggested creating bins). Then use your impression as the output variable. Finally, you can fit a linear model using a linear regression algorithm and from there you see whether you find some of the trends and fan out in multiple directions! $\endgroup$ – Anu Feb 10 '19 at 23:13
  • 1
    $\begingroup$ Do you want to predict that period for the next year or find the period on your past data and make the assumption that it will be the same next year? $\endgroup$ – Tasos Feb 11 '19 at 8:05
  • $\begingroup$ @Tasos thanks for your reply, No I want to know where is trend time of chart $\endgroup$ – majid Feb 12 '19 at 12:57

Based on your comment and what I can understand from your question, you could identify the "trend" you are talking about as a series of Outliers. From Wikipedia, an outlier is:

In statistics, an outlier is an observation point that is distant from other observations. An outlier may be due to variability in the measurement or it may indicate experimental error; the latter are sometimes excluded from the data set.

How can you do this in excel? There is a nice tutorial in this link. In short, based on the data you added, you need to do

  1. Calculate the 1st and 3rd Quartile with the formula =QUARTILE(B2:B9,1) and =QUARTILE(B2:B9,3) respectively
  2. Then get the IQR which is the difference of the above two =F3-F1
  3. Calculate the L Bound as =G2-(1.5*G4), where G2 is the 1st Quartile and G4 the IQR
  4. Calculate the U Bound as =G2-(1.5*G4)
  5. Then next to the column with the data, add this =OR(B2<$G$5,B2>$G$6) which check if your data is lower or higher the L and U Bound. If it is, that row is marked as Outlier.

You need to play around with the multiplier in 3rd and 4th step to get you the right results in your test data. With 0.8, the last 3 rows are marked as outliers and you can say that the desired outlier or "trend" as you called it period is on 17 to 19th January 2018.

Excel Implementation

There are more sophisticated ways to do it in Python, but I am not aware of a similar one in Excel. You could look for Anomaly Detection in time series with Python.

| improve this answer | |

Your Answer

