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.
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!
- If 6 is below threshold then why are rows 2-34 given Label A since those are only 4 and then 98 to 273 are given Label B also with White value of 6. And then Label E is rows 432 - 479 where White starts at 51 and max of 56 and then very next value is also 51 but not included. So it seems you can have a ramp (sliding threshold?) AND 51 to 56 may/could be 53+/-5% but then that would be 50.35 (round to 50) and 55.65 (round to 56) but then the 50 just before the first 51 isn't included either. and then Label F and G and L and Q and R and T and U are each 0 in column 1 (certainly below threshold). and then Label J does NOT include a 43 just before it starts but DOES include a 40 at the end right after a 43. label K is too short. Label P is also only 23 long but at least that is really close to 24.
So back to my points above. You mention an 'attached graphic' but I can't find it and that may help explain a lot of this. You mention thresholds, minimum deviations, averaging, etc... but I don't see those values anywhere except the 5% deviation you mention. Patrick suggested using StDev instead of a 'flat' deviation and you seem to like that idea? I suspect some of these labels maybe to point out things like areas not to count or plateaus for Orange and not White and hence I suggested the 2 separate columns.
I'd love to sink my teeth into this but feel like I'm grabbing blind from the refrigerator drawer and just as likely to grab an onion as a I am to grab an apple.
- 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.
