0

I have a table organized by user and month. I'd like to figure out what the average 'trend' is between consecutive months. However, each user's history starts and stops at a different month. So, in order to get an accurate picture I need to look at only the consecutive, non-zero months for each user. What's the simplest way to go about doing this?

As a quick example, the table looks something like this:

0 0 0 0 0 1 2 5 3 5 4 1 1 0 0 
0 4 0 4 0 3 4 4 3 0 0 0 0 0 0 
0 3 3 3 1 1 1 3 0 0 0 0 0 0 0 
0 0 0 0 0 0 0 0 0 0 0 3 3 4 5 
0 2 2 2 0 0 0 4 5 4 4 6 5 3 0 

The output is a bit trickier because I'm ultimately looking for something that shows how much higher or lower the value is in recent months compared to previous ones. So, for example it would be something like:

Avg(Last Half of Set = {1 2 5 3}) / Avg(First Half of Set = {5 4 1 1}) for each set. 

In some cases like the second row there would be multiple sets, some of which would be singletons {4} since the user was dormant between months 2 and 4.

  • 1
    Some diagrams or screen shots of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. Upload any image(s) to Imgur and you will get link(s) you can share. Edit your question to include the link(s) and someone with sufficient reputation will inline the image(s) for you. – DavidPostill Dec 28 '16 at 21:52
  • 2
    What would the expected output look like? Do you just want to know how many months did not have 0 with the previous month also being 0? – BruceWayne Dec 28 '16 at 22:12
  • 1
    Usually trends are part of a STATISTICAL analysis over time. I'd look into statistics with Excel on a Google search. – user676368 Dec 29 '16 at 21:29

Your Answer

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

Browse other questions tagged or ask your own question.