For a project, I'm trying to find the longest period where a number is higher than 10.000.000. I was looking to find two things

1. number of continuous periods with a number higher than 10mil (e.g. 9 in this example)
2. the minimum value closest to 10 million in that biggest continuous period (e.g. in this example 9 periods)

I did the first one via the following formule =MAX(FREQUENCY(IF(A28:AA28>=$A$1;COLUMN(A28:AA28));IF(A28:AA28<$A$1;COLUMN(A8:AA28)))) => this returned 9 as the longest continuous period with values higher than 10 million

I cannot find a way to extract the minimum value from the longest continuous period, specifically how to get the range from the longest continuous period with values higher than 10mil.

In this example I would have the find the value 11.000.000 as this is the min value from the longest continuous frequency above 10mil.

Does anyone have an idea how to solve this?

Much appreciated!

CodePudding user response：

Assuming B1 contains the result from your current formula, e.g. 9, for Office 365:

=MIN(INDEX(A28:AA28,SEQUENCE(B1,,FIND(REPT(1,B1),CONCAT(N(A28:AA28>=A1))))))

CodePudding user response：

@Jos Woolley's answer is perfect, but I wondered for my own satisfaction if I could get the minimum using the frequency array? The answer is 'yes', but the formula is much longer and less elegant. I believe the number of cells in the range a28:aa28 preceding the longest run can be calculated by counting the number of elements in the frequency array before the maximum and adding the sum of those elements so I ended up with this:

=LET(range,A28:AA28,
seq,SEQUENCE(1,COLUMNS(range)),
freq,FREQUENCY(IF(range>=$A$1,seq),IF(range<$A$1,seq)),
matchPos,MATCH(B1,freq,0),
start,IF(matchPos=1,1,matchPos SUM(INDEX(freq,SEQUENCE(matchPos-1)))),
end,start B1-1,
MIN(INDEX(range,start):INDEX(range,end)))