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....
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 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?
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?
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!