Forum Discussion
Is Excel capable of this operation?
THIS QUERY IS NOT FOR THE FAINT OF HEART! Speadsheet has Column A and Column B. Intermittently, Column A has a "plateau", where data is "stable" ( within 3%) for at least 4, and as many as 20, rows....
If you have any problems with OneDrive access, just let me know. I have other avenues of access.
this sounds interesting but not following some of the logic and such. You mention examples but all I see is an excel sheet with no graphics/plots. In that data sheet you label sections in groups but there are cases/runs of data that seem to be plateau that aren't labelled. I am also unclear what the label means as I would expect step 1 to be more a column to note Plateau on A and another for Plateau on B and try and find relationship between those 2. I don't follow what the labels you have are indicating. you also mention a plateau is within a certain percentage, is that known? and what to do about overlapping plateaus (e.g. slow ramps). for example we will say 10% and 5 samples and you have 9,10,9,10,10,11,10,11 and you will have a plateau 9,10,9,10,10 and another plateau 10,10,11,10,11 and the middle 10,10 overlap and are required by both to satisfy the 5 sample min and if they weren't required for the min which side would they go on?
mtarler How about trying this PDF version of the graphic that uses a different colour bkgnd, and darker characters for location Labels? ... PLEASE ADVISE JOY/NOJOY
- OK so I finished creating a LAMBDA to 'find plateaus' and added my LAMBDAs to your example file. I inserted my formula:
=Plateaus(B2:B1100,25,15,5,5)
which is looking at the array in column B (Orange) and uses a threshold for the average plateau value of 25, a minimum plateau width of 15, and allows for a maximum difference of 5 on the plateau and the final 5 is that it applies a 5pt moving average to the data. There is also a graph showing the White and Orange data and this new column/output. The amplitude is just progressive for each plateau (1, 2, 3, etc...) just so that if 2 'plateaus' are adjacent it is clear that they are recognized as 2 and not just 1 long one.
It isn't perfect but hope it is a starting point toward your goal and maybe help us understand your goal better.- Perhaps this is a better analogy: Consider a "heavy sleeper". We want to know how hard we have to "poke" them (Column A), to get a response (Column B). The response might be anything between a murmer and a loud moan, and the response always comes AFTER the poke. A big poke would get a faster and louder response, but a gentle poke might get a slower and quieter response. We just want to know (1) if we poke them above a certain level, do they feel it? We want to be able to make sure that the person who is being poked isn't trying to "fake" it, by just responding with responses of random intensities and random intervals. We want to be able to draw a dependable correlation between the "poke" and the "Mm!" It seems easy to SUBJECTIVELY spot correlations visually, but I hope that somehow Excel can perform this act as an OBJECTIVE analyzer.
asezas OK so i had 'fun' and created 2 Lambda functions: wAvg and dThresh.
my wAvg will take an array and apply an N-pt moving average. It can accept an weighting array for custom weighted windows or just an N for # of pts and will size that window in the beginning and end to output an array length = to the input array.
dThresh will take an array and apply a dynamic threshold meaning the threshold value will drift based on the signal or the dPath you give it and return FALSE for values < dThresh.
I did this thinking these might be helpful to this problem, but when I came back to look at your data and sample sheet I don't think so and noticed:
a) the graph you sent doesn't match this set of data so those letters don't line up with the named sections on the spreadsheet (no big deal i guess) and
b) you have a column with your visual output which is what I assume you hope the formula to produce. BUT those sections don't make sense based on your "rules".
c) To me, it seems like you just want to find any local maximas that 'plateau' for at least x points on the 'response' data (and maybe the same on the 'Energy' data too).
would love to help on this but still trying to understand exactly what you need.
- yes I can see that (btw where is that posted before? in that sharepoint folder? so weird I don't see it in that sharepoint folder).
so let me take a step back here and ask if you are looking for help to specifically do what you want or is there are bigger picture that your trying to get to and hoping this approach gets you there. The analogy being you want to catch lots of fish and you have a fishing pole so you want a boat that can hold tons of rods but didn't realize you could use a net instead. I ask because it appears you are looking for feature detection not plateau detection. for example plateaus B,C,D,E are basically all part of a feature that results in the feature topped by F. Also, that threshold barely meets that final valley and I suspect was selected after the fact specifically to catch that valley. Instead you might be better with a dynamic threshold that is basically a heavily weighted moving average but then will 'drift' up during the active region. I would probably also have the 2 traces independently processed and then correlated for common features. But I don't want to jump down either rabbit hole without checking with you. You could have very good reason why you specifically want to look at plateaus and for that specific threshold. Another question is if maybe you are really only interested in 'peak' plateaus (i.e not features C, D, H which are sub-peak plateaus) and maybe that min length isn't key as it sure looks like (and you say) F is a CASE for E even though F is only 18 long.- Trying to grok the fishing pole analogy, what I seek thru my query is "all of", or "the big" picture. Please let me take a step back from the granularity and scratchiness, and try to explain it this way: Column A is an "event". Let's call it a sound, like a bang or a clap of thunder, or a snap of fingers. The louder the sound, the higher the value, and the longer the sound duration, the more data points get lit up. Column B, is a response to that sound event, by persons. The response (of course) always happens after the event, but sometimes the frequency of the event is too high or too low and is therefore inaudible for a person, and sometimes the response is small because the perceived volume of the sound was small. Sometimes, the response is long because event was long. In the end, one thing I'd like to determine is where the threshold of detection is for the person giving the response ... but right now I'm happy just MANUALLY plugging in a number for that threshold value. Similarly, I'm also happy manually plugging in a number that can be used to determine how many sounds the person heard that were longer than x data points. Visually, it's a relatively simple task to look at the graphic of the plotted data, and tell whether there appears to be a correlation between the sound intensity and the response. It's less easy to determine how long it takes to get a response for a sound of a certain volume, or if sounds of higher volume require less duration to get a response. But for now, if I can plug a value into a formula for the Threshold value (nothing below that needs to be analyzed), and/or plug in a value that says that there has to be a specific number or correct/ positive responses to N number of sounds, I will be a happy camper. I offer this reply to all of you following this problem.
- I appreciate your response. Hope this reply clears some issues. 1st, Cyan letters are for ID/landmarking purposes only. 2nd, values below Coulmn A Threshold (23?) are not required to be analysed, because they are below a value that can produce a meaningful value in Column B. "RAMPS" are not "plateaus", so they are not counted, but I do see your questions about when does a "ramp" become a plateau
A pure square-wave data set in Column A without scratchiness would certainly eliminate that, but I have no hard answer -only that de-bouncing or flattening the values in both columns (averaging, or means, or rounding to a value of 5's?) might be a solution. I am still trying to bend my brain over that solution. Your comments would help. Thanks for your kind efforts and input..PS: Expect delayed replies from late 08 Aug to late 11 Aug..Travelling, limited conne tivity and vision.
