Aug 05 2023 02:36 PM
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. Between plateaus, data is dynamic and may be higher or lower. I want to assess data in Column B to find when Column B data reaches a "plateau" between 2 and 20 rows (always) AFTER Column A. Absolute values and ranges in both Columns are different, but "plateau definition" is identical. A) How can Excel identify when "plateaus" occur, and B) How can Excel identify when Column B has a "plateau" within 2-20 rows, when Column B has a different data value/ range than Column A? Is this beyond Excel capabilities?
Aug 05 2023 04:04 PM
Aug 06 2023 10:42 AM
Aug 07 2023 09:56 AM
Sorry for the delay, Patrick! I was creating some docs to help explain my problem and my target. Here they are:
Disparate Data Tiers documents
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.
Aug 07 2023 09:58 AM
Aug 07 2023 11:57 AM
Aug 07 2023 03:05 PM
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?
Aug 07 2023 06:39 PM
@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!
Aug 07 2023 06:53 PM
Aug 07 2023 07:56 PM
Aug 08 2023 10:14 AM
The approach I'm taking is to identify the plateaus in the data where White and Orange are stable for at least 24 rows. Essentially, it's identifying the rows that are part of a 'streak'. This draft does not allow for any wiggle room. For White/Orange to be stable, there must be no change for 24+ rows.
=LET(
Change, LAMBDA(arr, N(DROP(arr, 1) = DROP(arr, -1))),
Stable, DELTA(Change(White), Change(Orange)),
Seq, SEQUENCE(ROWS(Stable)),
NonStable, 1 + FILTER(Seq, Stable = 0),
Streak, NonStable - VSTACK(0, DROP(NonStable, -1)),
Seq_2, SEQUENCE(ROWS(White), , 2),
Plateau, LAMBDA(e,
LET(
x, XLOOKUP(e, NonStable, Streak, , 1),
IFNA(IF(x >= 24, "Plateau", ""), "")
)
),
MAP(Seq_2, Plateau)
)
Aug 08 2023 10:17 AM
@mtarler FIRST, I like your humour! 2nd, you are correct in thinking your are missing MUCH by not having the explanatory graphic! How can I get it to you? Patrick opened it OK, and it was definitely uploaded as a PNG. I will shortly try to post as a PDF. Following the notice that the "Labels" are identified in the graphic using alpha characters, they serve only as landmarks, to identify a particular location on the graphic; their inclusion in the xls was to help connect data values to a location on the graphic. They have no connection to CASE occurences. Without the graphic, explanation is difficult. However, I can say that all values below 23 in Column A are exempt from analysis, and I can also say that Label location F (Column B) is a CASE for Label location E (Column A); Label O is a CASE for Label N; Label T is a CASE for Label R; and, Label W is a CASE for Label U. Until we can get you a copy of the graphic I worry that you are "blindly grabbing in a dark fridge", if you'll pardon the expression! Ha! I hope this helps to clarify matters. - Best regards
Aug 08 2023 10:23 AM
@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
Aug 08 2023 11:15 AM
Aug 09 2023 03:42 PM
@asezas OK so i had 'fun' and created 2 Lambda functions: wAvg and dThresh.
my wAvg will take an array and apply an N-pt moving average. It can accept an weighting array for custom weighted windows or just an N for # of pts and will size that window in the beginning and end to output an array length = to the input array.
dThresh will take an array and apply a dynamic threshold meaning the threshold value will drift based on the signal or the dPath you give it and return FALSE for values < dThresh.
I did this thinking these might be helpful to this problem, but when I came back to look at your data and sample sheet I don't think so and noticed:
a) the graph you sent doesn't match this set of data so those letters don't line up with the named sections on the spreadsheet (no big deal i guess) and
b) you have a column with your visual output which is what I assume you hope the formula to produce. BUT those sections don't make sense based on your "rules".
c) To me, it seems like you just want to find any local maximas that 'plateau' for at least x points on the 'response' data (and maybe the same on the 'Energy' data too).
would love to help on this but still trying to understand exactly what you need.
Aug 11 2023 09:11 AM
Aug 12 2023 12:04 PM
Aug 12 2023 12:05 PM
Aug 12 2023 12:24 PM
Aug 12 2023 12:39 PM