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....
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.
=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.
So if you open the sheet in desktop after you type my lambda formula name you will see the arguments. In this photo I show that at the bottom but also show my help info about each (just text I wrote down to help me remember in the future)
The brackets [ ] mean they are optional and I say what the default value will be in the text above.
This function also uses the wAvg function I wrote for that weighted window averaging. I am attaching my workbook that I developed them in originally with that helpful text.
You should be able to copy these functions in the Name Manager. Better yet if you have the Excel Labs installed and use the Advanced formula environment I find it even easier.
- Wow! I am very impressed. I could not have developed your LAMBDA function in a million years! Wow! But the devil is in the details... I see that the Column B values around data points 920-929, and data points 987-998 didn't get identified by the function as "cases", and I'm assuming that this is because they were too short a streak. I think my original spec 20 or 24 data points. How can I edit the formula/function to change the "length of the streak", or the "deviation of the Column B value", to see what values work best to identify what "my eyes" determine to be a "case"? I'd like to try to paste the function into other data sheets to see what works best, but I am currently totally ignorant of how to use functions properly. I'm probably a light-year or two behind you folks! Maybe even some parsecs!
- Sorry for delay. Been dealing with some medical issues that require travel, but back now for a few days. I am also working on a hardware issue regarding capturing data intended for an RS-232C port ("Com1:"), but now having to be read by a Windows 10 USB port using a QB64 program. (Want to burn lots of cycles? Try this!) .
So, tonight or tomorrow I get back into the "poke" problem, and experiment with both LAMDA and StDev. (If you are old enough, you may remember when a "poke" was used to put data into a memory location in (at least) the original Apple II OS, called Apple DOS! - So my fishing pole analogy is a variation on when you have a hammer everything looks like a nail. and questioning if you are seeing the forest through the trees. I totally get the input output concept and looking at trying to extract thresholds and time delays. On a side note about thresholds I remember an awesome article about the use of adding chaotic or white noise to increase event detection. In concept it was so backwards it stuck with me. Basically it was seemingly random events but by introducing the additional noise it was able to make the periodic stimuli result in reaching threshold more often and then the frequency of that underlying stimuli was revealed. In any case I digress.
My main question here is why are you looking for plateaus and not just peak features/occurences? Is there something special about a plateau?
On another note, did you look at the sample file and play with the LAMBDA function I created to detect those plateaus?