Count how many days to drop from maximum value into 0

[uqls]

Hi
Please can you help me .
My spreadsheet is like this (please see the attached):

1. Column A : Dates (Every day's Date):
2. Column B : Data numbers (daily value)
3. Column E-F: Is where I want the formula to be: Determine the number of days from when maximum value drop to zero

For example, on Dec 5 2026, the maximum value is 23.11, and then it starts to getting smaller until 0 on Dec 8 2026. By this i have roughly 3 days in 2026. It is similar to 2027 that i have 3 days to drop from maximum value into 0


The formula must look at every in-Column A and the corresponding data in column B and generate the outputs as indicated from column E-F.

Many thanks

[6StringJazzer]

This formula works. There may be a more efficient or shorter one.

=LET(Pos,MATCH(E2&MAXIFS(B:B,A:A,">="&DATE(E2,1,1),A:A,"<="&DATE(E2,12,31)),YEAR(A:A)&B:B,0),MATCH(0,INDEX(B:B,Pos+2):INDEX(B:B,Pos+365),0))

[uqls]

Many Thanks

[6StringJazzer]

It just occurred to me that this formula assumes that that the number must be at least 1. Here is a revision that works if the very next number after the max is 0. Also this formula adds absolute addressing so it is moveable.

=LET(Pos,MATCH($E2&MAXIFS($B:$B,$A:$A,">="&DATE(E2,1,1),$A:$A,"<="&DATE($E2,12,31)),YEAR($A:$A)&$B:$B,0),MATCH(0,INDEX($B:$B,Pos+1):INDEX($B:$B,Pos+365),0)-1)

[uqls]

Thanks for clarification. I was wondering if i want to count how many days to drop from maximum value into certain threshold. For example, 5. if there anyway i can modify your suggested formular?

[6StringJazzer]

A bit harder, because looking for 0 is an exact match. I'll see if I can look into it.

[HansDouwe]

i want to count how many days to drop from maximum value into certain threshold. For example, 5

I'm not sure I understand you correctly. Can you give an example of this in a sample sheet?

[6StringJazzer]

This adjustment will look for the number of values following the maximum until it reaches 5 or less. Changes from the original formula are highlighted. The 5 can be replaced with a cell reference if desired.

=LET(Pos,MATCH($E2&MAXIFS($B:$B,$A:$A,">="&DATE(E2,1,1),$A:$A,"<="&DATE($E2,12,31)),YEAR($A:$A)&$B:$B,0),MATCH(5,INDEX($B:$B,Pos+1):INDEX($B:$B,Pos+365),-1))