+ Reply to Thread
Results 1 to 7 of 7

Automating Graph Analysis

  1. #1
    Registered User
    Join Date
    12-08-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    7

    Automating Graph Analysis

    Greeting Forumites

    I have a bunch of data (it's AMPs from a machine as it runs on-load/off-load/off if you're interested). The graph shows the kind of data i have when it's sorted high-low in excel.

    I currently have to pick numbers to represent the higher cut-off (red dotted line), and lower cut-off (yellow dotted line), the calculation then looks after itself.

    What i'd like to do is automate that process, so the calculation figures out where the cut-offs should be itself. The bottom cut-off is easy (it can be a non-zero value high enough to eliminate 'noise- in the data. The upper cut-off should be 50% between the two plateaus on the chart. It's this that i'm a bit stumped with, i imagine you could pick the two highest occurring numbers (with a little bit of a tolerance) and split the difference, but not sure how to do that... I think mode would get the most off occurring number, but what about the second most often?.......

    Capture.JPG

    I've looked at sorting the data into bins using countifs, but that still requires user intervention, so gains nothing. Something about a moving gradient maybe. It's easy to do by eye, but i can't think of how to make an algorithm - Oh to be borg!

    Has anybody got any nice ideas?

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Automating Graph Analysis

    Is horizontal axis just ord.number of measurement? so you have nearly 90K of values?

    Smoothed (for instance with running median, not running average) gradient could be a good idea for first tries.

    Would you please attach a SMALL sample Excel workbook(s) with few sets of points? I know it's a lot of data, but in this case just 20-30 points sample we suggest usually will not work.

    To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    12-08-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    7

    Re: Automating Graph Analysis

    Hi Kaper,

    Thanks for your thoughts.

    Yes, the x-axis is just the number of values. The chart represents the data sorted from high-to-low to make it easy to visually gauge the data, and do some other unrelated things.

    I'll have a look at running medians and see what drops out of that

    I had an idea using the difference between the values, and averageifs to remove and tried that, but it gives a low value - which makes sense if you think about it.

    Basically it looks like i need to identify the median between the to plateau. So identifying the plateau values would then make things trivial.

    https://drive.google.com/file/d/1EGb...ew?usp=sharing

    Above is a link to a CSV with 1 days worth of data and some annotation to explain things better.

    Capture1.JPG


    Thanks

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Automating Graph Analysis

    See attached file.
    you have plenty of points, so smoothing can be quite generous. I first did running average of +/- 10 results each direction (so from 21 points). Could be less, but this allows for nice detection of second plateau start (see suplementary graph in a file)
    then used difference to asses local gradient, and finally removed some possible noise (in sample data it was smaller than I could expect) with median.
    I used double discrimination to find sloped and relatively flat parts. And scatter plot to biuld a horizontal line just from two points.
    See attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-08-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    7

    Re: Automating Graph Analysis

    That looks great - Lots to understand, so i'll have a look through that in more details later - Thanks though - Much appreciated

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Automating Graph Analysis

    The key to understanding is the formula in F18 (and copied down):
    =IF(ISEVEN(F17),IF(E18>0.02,F17+1,F17),IF(E18<0.002,F17+1,F17))

    flat sections are numbered with even, and sloped with odd numbers.
    so if previous point was in flat section (even) we shall note only if gradient is high enough to treat it as a beginning of sloped (odd) section.
    for points in odd section - we check if gradient is very small - indicating we are moving to flat section.

    Final comment - formally in D13 we shall put not =C12-C13 but =C13-C12 - we have negative trent all the time, but I liked working on positive number, and it better presents on the graph (to me) if one plots for instance column D or E. It's flat near zero in flat sections, and rised in sloped sections.

  7. #7
    Registered User
    Join Date
    12-08-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    7

    Thumbs up Re: Automating Graph Analysis

    That's perfect - I like your thinking here - KUDOS

    I'll digest this more later on, am up to my neck in it at the moment. The curve shape is not always as per the example data so the gradient could change numerous times although the two main plateau would still be evident, they just may have more gradient changes between them.

    Thanks for your input on this

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Automating Making More TIck Marks/Lables in a Graph for 2,000+ Files
    By wings1080 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2015, 10:51 PM
  2. Automating data analysis
    By RobMac14 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-25-2014, 02:58 AM
  3. Automating Data analysis across multiple worksheets in a folder.
    By briggsa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-15-2013, 08:22 AM
  4. Replies: 2
    Last Post: 07-14-2013, 03:13 PM
  5. Automating a pivot table for data analysis
    By LeapingLizard in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2013, 06:19 PM
  6. [SOLVED] Analysis Toolkit does not work when automating EXCEL workbook
    By Dave Gruzewski in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-15-2005, 11:06 AM
  7. Problem using analysis toolpack when automating EXCEL
    By Dave Gruzewski in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2005, 03:06 PM

Tags for this Thread

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