+ Reply to Thread
Results 1 to 7 of 7

Spotting trends in large datasets

  1. #1
    Registered User
    Join Date
    05-22-2007
    Posts
    26

    Spotting trends in large datasets

    I manage a relatively large dataset:

    52 columns representing each week in a year
    267 rows representing 267 monitoring stations at different locations

    The spreadsheet is populated with data on air emissions.

    The spreadsheet is up-dated on a weekly basis, and every week I am required to examine the data for each monitoring station to see if emissions are increasing or decreasing. The goal is to spot trends.

    Example 1: If emissions at one monitoring station are increasing slowly over the course of 4 or more weeks, I may flag this monitoring station in my dataset and follow up with the facility that is generating the emissions.

    Example 2: If emissions at one (or more) monitoring station(s) are decreasing over the course of 12 weeks, I may decide to stop collecting data for this monitoring station (i.e. remove it from my dataset).

    Without having to plot/graph data for each monitoring station, is there a function (or other solution) I could use that would make it quick/easy to spot trends in my data?

    Thanks,
    tan

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Spotting trends in large datasets

    Perhaps conditional formatting could be your answer. In E1, for example, the formula could be =AND(E1>D1,D1>C1,C1>B1,B1>A1). You could then pick a format to apply if this formula returns true, which would highlight all cells where the emissions have increased for four straight weeks. You could use a similar formula for the decrease.

    If you need more help, could you post a dummy workbook with the same layout you'll be using?

  3. #3
    Registered User
    Join Date
    05-22-2007
    Posts
    26

    Re: Spotting trends in large datasets

    How do I post a dummy workbook?

  4. #4
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Spotting trends in large datasets

    Click Go Advanced, then the paper clip icon. Try to keep the workbook relatively small (some people have data plans and others have their downloads monitored at work).

  5. #5
    Registered User
    Join Date
    05-22-2007
    Posts
    26

    Re: Spotting trends in large datasets

    Here is a dummy spreadsheet (MUCH smaller than my actual spreadsheet). I have used your suggestion and added "TRENDING" columns.

    This is a good option, however it will require that I update the formula each time I insert new data into my spreadsheet (once per week) to reflect the 4 most recent columns of data. This is a step in the right direction but I am open to more suggestions!

    I added an ifnumber statement in my formula because I have blank columns to work with.

    example: =IF(ISNUMBER($B3), AND(K3>J3, J3>I3, I3>H3, H3>G3), "")
    Attached Files Attached Files

  6. #6
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Spotting trends in large datasets

    Not really what I meant. I mean, you could do it with a trending column like that and your formulae could be modified so you don't have to change them with each week, but I was talking about conditional formatting. Here's a version of yours with conditional formats applied from week 13 forward for trending down over 12 weeks (green if yes) and from week 5 forward for upward trending.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-22-2007
    Posts
    26

    Re: Spotting trends in large datasets

    Thanks! I will give this a try.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1