Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to analyze this data (97 sr2 or 2003)

    A user asked me how to analyze some data for him. I don't have analytical experience, but I told him I have people to help - aka YOU. :-D

    We track total dissolved gas (TDG) in our river system 24-hours a day, every day of the year. We need to track how often TDG exceeds a certain percentage (110%). The user has extracted the dates/times that TDG exceeded 110% from all the data collected for 1999-2003.

    Now he wants to create a chart or something that shows how often this exceedence occurs and when it occurred.

    Questions he needs to answer are:
    1. What are the most common dates and time that TDG exceeds? For instance, does it usually occur mid-month in the middle of the night?
    2. How often does TDG exceed at any given time (I'm guessing something lika Pivot table so he can plug in a time and see how often it occurred at that time or in that time range).


    The data is set up like this:

    Start Time End Time Total Hrs Exceeded
    8/14/1999 0:00 8/14/1999 13:00 13:00
    8/14/1999 17:00 8/15/1999 0:00 7:00
    8/15/1999 0:00 8/15/1999 10:00 10:00
    8/15/1999 12:00 8/15/1999 13:00 1:00
    8/15/1999 14:00 8/15/1999 16:00 2:00

    Is he asking for the impossible?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to analyze this data (97 sr2 or 2003)

    I assume your table represents 3 columns
    a start date/time
    end date/time
    the elapsed time between them

    1) Plot the the elapsed time (Y) vs starttime (or endtime) of the excursions on an XY to look for trends (sine curves, etc)
    Add columns to extract the time of day:
    =MOD(A2,1)

    Then you could create a table [eg in 3 hrs increments (0:00, 3:00, 6:00, ... 18:00, 21:00)] and use countif to determine the number of times the particular times had excursions (COlumn Chart)

    Or create a column chart using a sumif with your "3hr increments" vs (time column) and the sum of the elapsed times

    SImilarly you could create a column of day of the month:
    =day(A2)

    and then put 1-31 in a table and use countif (the "day") or sumif (day, elapsed time) to plot the # or times of excursions vs day of the month

    2) not sure how 2 is different than 1?

    If you need further details on any of the suggestions, please post back.

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to analyze this data (97 sr2 or 2003)

    Another thought:
    You might consider collecting all the data over time and use some "statistical process control" methodology [google under something like: "statistical process control" tutorial for some hits on the basics]

    It would be good to see if the "process" was "in control" before you started doing a bunch of work on the process.
    Once it is "in control" you can work on decreasing and elimininating the "out of control"

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to analyze this data (97 sr2 or 2003)

    Thanks. I'll work with what you've given me and will let you know how it turns out. Thanks for the suggestions! I started playing with using a histogram for his question about how often it is exceeded and at what times (for instance, at 1:00, there are 4 instances of exceedance). I think I like the sound of what you're talking about, so I'll try that too.

    Thanks.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to analyze this data (97 sr2 or 2003)

    Melanie, I don't how it is done in Excel but from a purely analytical perspective - you could get a quick visual answer to your questions by using a 3-D chart as follows:

    X axis = dates (all dates)
    Y axis = Time (24)
    Z axis = TDG Value for the time on the Y axis; arrange such that any TDG > 110 is red, TDG between 100-110 is yellow, below 100 is green... or whatever colors you choose.

    Looking at your chart should show any patterns - both high and low. I hope this is clearly explained. Maybe one of the Excel wizards can further explain how to create this type of chart in Excel... I would like to know also.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to analyze this data (97 sr2 or 2003)

    The one problem with XLs XYZ chart is that Z is the only "value" the x and Y are only categories, so you sould have to arrange the data properly

    You have dates in col A
    In row 1 you would have hours times 0-23
    In the intersection of each date and time, you need the values for the Z

    You could do it with a Vlookup if you have all the values of time every hour for each day

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to analyze this data (97 sr2 or 2003)

    You're absolutely correct. That is exactly what I had envisioned - I just didn't do a very good job expressing it.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to analyze this data (97 sr2 or 2003)

    I thought you did a fine job of expressing what you envisioned. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    If you hadn't I wouldn't have been able to explain the setup. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to analyze this data (97 sr2 or 2003)

    Thank you both for your 'visions'. You've set me on the right path!

Posting Permissions

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