Forum Discussion
Is Excel capable of this operation?
Sorry for the delay, Patrick! I was creating some docs to help explain my problem and my target. Here they are:
https://1drv.ms/f/s!AosKAG2c8jifcvJgOxXyASyf-Rg?e=m9XQbM
I sincerely appreciate your help with this problem, in advance. I'm not an Excel wiz, so I'm not sure that this is even possible. I hope you're able to prove me wrong, and maybe show me an example.
- If you have any problems with OneDrive access, just let me know. I have other avenues of access.
Thank you for taking the time to upload the sample workbook and notes.
I have some questions. Beginning at Row 50, there's 37 consecutive data points where White is 6 and Orange is 0. I entered "Label?" in column C because this seems to meet the criteria of 24+ rows (or 6 minutes) of stability. Should these be marked with labels?
This might be simplifying things, but sometimes with a complex task it's best to start small and scale it to add nuance and complexity as needed. One way to measure stability is obtaining a rolling Standard Deviation. For the sake of this example, we'll say Standard Deviation must be 0 for there to be Stability.
This function obtains a rolling Standard Deviation:
=SCAN(, White, LAMBDA(a, v, LET(r, ROW(v) - 1, STDEV(TAKE(White, r)))))The same function can be used on the Orange array, too.
This snip illustrates the stability early on in the sample (0 and 0 for those last two columns):
The question I have is: when the stretch of 4-0 ends, would you like to discard the previous numbers for White and Orange and start over in the calculations?
Patrick2788 Wow! I am reeling from your insight! Amazing! "Label ?" question is best answered as "values for Column A are below threshold limit", so no analysis (or label) is required. Values don't meet criteria. Answer for 2nd question regarding 4-0 is YES, deviation should reset to new plateau values, so 5% deviation at plateaus around Column A data of 30, should be re-calc'd when new plateau is around Column A of 50. Rounding up value of deviation is alao acceptable, cuz we only use integers. I hope these answers help to clarify. I am travelling from late day 8Aug until eve of 11 Aug. My delayed responses are not due to lack of interest, only to connectivity and eyesight issues. Best regards
PS: Your skills and knowledge are impressive! Good for you!