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?
Bookmarks