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