Count number of occurrences values increase by X within N days in absolute and % terms

[Barque]

I am trying to extract some information out of a data series. I am first trying to count the number of positive and negative streaks and the magnitude of each move. The 2nd step will be to count the frequency of moves by their size and their date.

I have tried looking at some nested IF/AND functions on other threads but am struggling with the best path. I am happy to use helper columns, as many as needed, to keep things simple and not hog too much system resources.

The box on the right is what a summary table might look like. I will be needing both regular and % changes. I work with Climate data.

Any suggestions are appreciated. I have become a big fan of Excel 365 new functions like Filter, Xlookup, etc but haven't reached a solution yet with anything.

Climate Data.xlsx
	A	B	C	D	E	F	G	H
1	Date	Temp	Change	% Change	+/-		Start Date	3/1/2021
2	4/16/2021	100	0	0.00%	0		End Date	4/16/2021
3	4/15/2021	99	-1	-1.00%	0		Lookback
4	4/14/2021	98	-1	-1.01%	0
5	4/13/2021	97	-1	-1.02%	0		# of Moves > 10%
6	4/12/2021	96	-1	-1.03%	0		# of Moves > 5%
7	4/9/2021	100	4	4.17%	1		# of Moves < 10%
8	4/8/2021	95	-5	-5.00%	0		# of Moves < 5%
9	4/7/2021	90	-5	-5.26%	0		Shortest Move > 10%
10	4/6/2021	85	-5	-5.56%	0
11	4/5/2021	80	-5	-5.88%	0
12	4/2/2021	100	20	25.00%	1
13	4/1/2021	90	-10	-10.00%	0
14	3/31/2021	80	-10	-11.11%	0
15	3/30/2021	70	-10	-12.50%	0
16	3/29/2021	60	-10	-14.29%	0
17	3/26/2021	100	40	66.67%	1
18	3/25/2021	85	-15	-15.00%	0
19	3/24/2021	70	-15	-17.65%	0
20	3/23/2021	55	-15	-21.43%	0
21	3/22/2021	40	-15	-27.27%	0
22	3/19/2021	100	60	150.00%	1
23	3/18/2021	80	-20	-20.00%	0
24	3/17/2021	60	-20	-25.00%	0
25	3/16/2021	40	-20	-33.33%	0
26	3/15/2021	20	-20	-50.00%	0
27	3/12/2021	30	10	50.00%	1
28	3/11/2021	40	10	33.33%	1
29	3/10/2021	50	10	25.00%	1
30	3/9/2021	60	10	20.00%	1
31	3/8/2021	70	10	16.67%	1
32	3/5/2021	90	20	28.57%	1
33	3/4/2021	100	10	11.11%	1
34	3/3/2021	50	-50	-50.00%	0
35	3/2/2021	100	50	100.00%	1
36	3/1/2021	100	0	0.00%	0
Sheet1

Cell Formulas
Range	Formula
E2:E36	E2	=IF(C2>0,"1","0")
H1	H1	=MIN(A2:A36)
H2	H2	=MAX(A2)
C3:C36	C3	=B3-B2
D3:D36	D3	=(B3-B2)/B2
B3:B6	B3	=B2-1
B8:B11	B8	=B7-5
B13:B16	B13	=B12-10
B18:B21	B18	=B17-15
B23:B26	B23	=B22-20
B27:B31	B27	=B26+10

 

[KRice]

You could do something like this to offer some flexibility in setting the criteria for the counts:

Book1
	G	H	I	J
1	Start Date	3/1/2021
2	End Date	4/16/2021
3	Lookback
4		Criteria	Threshold	Count
5	# of Moves	>	10%	11
6	# of Moves	>	20%	8
7	# of Moves	<	10%	24
8	# of Moves	<	1%	23
9	Shortest Move > 10%
Sheet6

Cell Formulas
Range	Formula
H1	H1	=MIN(A2:A36)
H2	H2	=MAX(A2)
J5:J8	J5	=COUNTIF($D$2:$D$36,H5&I5)

Two questions:
1) Could you clarify what is meant by, say, a move <10%? Does that mean you are looking for all positive changes less than 10% (which will include all negative changes regardless of their magnitude), or are you looking for changes of x, where -0.1<x<0.1?
2) For the Shortest Move >10%, how are you defining "shortest"? There are two parts to this answer: one involves whether you are looking for the absolute value of the change (related to question #1), and the other involves whether you are basing "shortest" on the percentage movement or the absolute magnitude movement (i.e., column C vs. column D).

 

[Barque]

I should have made the data Chronological. Below table is updated and should be easier to speak about examples with:

Climate Data.xlsx
	A	B	C	D	E
1	Date	Temp	Change	% Change	+/-
2	3/1/2021	100			0
3	3/2/2021	98	-2	-2.00%	0
4	3/3/2021	96	-2	-2.04%	0
5	3/4/2021	94	-2	-2.08%	0
6	3/5/2021	92	-2	-2.13%	0
7	3/8/2021	100	8	8.70%	1
8	3/9/2021	105	5	5.00%	1
9	3/10/2021	110	5	4.76%	1
10	3/11/2021	115	5	4.55%	1
11	3/12/2021	120	5	4.35%	1
12	3/15/2021	100	-20	-16.67%	0
13	3/16/2021	90	-10	-10.00%	0
14	3/17/2021	80	-10	-11.11%	0
15	3/18/2021	70	-10	-12.50%	0
16	3/19/2021	60	-10	-14.29%	0
17	3/22/2021	60	0	0.00%	0
18	3/23/2021	63	3	5.00%	1
19	3/24/2021	66	3	4.76%	1
20	3/25/2021	69	3	4.55%	1
21	3/26/2021	72	3	4.35%	1
22	3/29/2021	75	3	4.17%	1
23	3/30/2021	78	3	4.00%	1
24	3/31/2021	81	3	3.85%	1
25	4/1/2021	84	3	3.70%	1
26	4/2/2021	87	3	3.57%	1
27	4/5/2021	90	3	3.45%	1
28	4/6/2021	95	5	5.56%	1
29	4/7/2021	120	25	26.32%	1
30	4/8/2021	70	-50	-41.67%	0
31	4/9/2021	75	5	7.14%	1
32	4/12/2021	74	-1	-1.33%	0
33	4/13/2021	73	-1	-1.35%	0
34	4/14/2021	72	-1	-1.37%	0
35	4/15/2021	71	-1	-1.39%	0
36	4/16/2021	100	29	40.85%	1
Sheet1

Cell Formulas
Range	Formula
E2:E36	E2	=IF(C2>0,"1","0")
C3:C36	C3	=B3-B2
D3:D36	D3	=(B3-B2)/B2
B3:B6	B3	=B2-2
B8:B11	B8	=B7+5
B12	B12	=B11-20
B13:B16	B13	=B12-10
B18:B26	B18	=B17+3
B32:B35	B32	=B31-1

1.) Good question. I do wish to separate moves into positive and negative buckets first.
2.) Shortest move would be the least amount of days to yield a change of >10%, etc. I probably should have made the changes a bit smaller on the example sheet. I would eventually like to capture absolute magnitude, bucketed by positive or negative, and percentage movement.

One of the main goals is to count and quantify streaks by magnitude and amount of time. For example, A2 to A6, 3/1/2021 - 3/5/2021, was 5 full days. The Temperature change over that time period was 8 degrees, and also was an 8% decrease. So I would like to capture both of those data points somehow. If those happened to be the largest and quickest temperate changes in my dataset, that would be very valuable to know.

I imagine I might need to create helper columns to help loop through the data somehow. I have to answer questions like "What was the fastest temperature rise or fall over 5 days? and by percentage? "The quickest 3 day rise in temperature each Spring going back 5 years?" "What was the largest swing in temperature overnight when humidity was below 60%?"

I am having a tough time with "basing" or anchoring. I think the 1st step of the logic function is something like IF B3<B2, then start counting this as a negative streak. Find the last negative day, stop there, and calculate the absolute move, percentage move, and how long the move was.

Thanks for your response and I hope that clarifies things.

 

[KRice]

Some of your objectives are clearer...thank you. It sounds as if you plan to perform additional queries that were not initially described. For those that were, my earlier posting offers one way to perform the counts of time steps where sequential steps "exceed" some threshold. For example, if one step has a metric value of x, then exceeding that metric by more than 10% means the subsequent step is >1.1x, and exceeding that metric by more than -10% means the subsequent step is <0.9x. In other words, the step size of the measured value is larger than 10% of the original step's value in some direction. The "count" approach described earlier uses inputs of > and < and some percentage value.

I completely misinterpreted your initial description of a "shortest move > 10%". I assumed that meant which adjacent time steps yielded a movement of >10%, and of those, which was closest to 10%. Your clarification says that for each time step, look at the metric (apparently a temperature) and find the first date on which the metric has been exceeded by at least 10%. The working example below shows one way to do this with helper columns. Not all of the helper columns are necessary, and there are other ways to accomplish everything shown, especially with Excel 365 (which I do not have). Your input is the blue shaded cell, where a positive percentage means you are looking for the next time step where the current temperature is exceeded by that percentage. A negative percentage says to find the next time step where the temperature has decreased by at least that percentage. In this example, I used -10%, so I am looking for the date when the temperature has decreased 10% relative to my current date's temperature. Each time step potentially generates an answer to this question, so you then need to look through this list of dates, determine the time duration between those dates (I used a simple difference to indicate the number of intervals...if you want the end dates included, then 1 needs to be added), and then find the smallest duration. In this case, the helper columns establish T_threshold (the temperature threshold that must be exceeded based on the current date's temperature)(see column G), the date on which T_threshold is first exceeded (col H), the temperature on that date (col I), and the number of days elapsed before T_threshold was exceeded (col J). Since there may be multiple occasions when this occurs, I2:J3 determine the smallest duration and perform a count of the occurrences. In this case, we learn that the smallest duration is only 1 day and it occurs 4 different times. The formulas in cols L:M build this list of those 4 occasions. Again, there are more efficient ways to build that list with Excel 365's improved function set.

Please see father below some additional commentary about temperature.

MrExcel20210420.xlsx
	A	B	C	D	E		G	H	I	J	K	L	M
1
2							>+ or <-		count-->	4		List of date pairs
3							-10%		smallest d-->	1	-->	with shortest time
4	Date	Temp	Change	% Change	+/-		T_threshold	date exceeding threshold	Temp on date	days		From	To
5	3/1/2021	100			0		90	3/17/2021	80	16		3/16/2021	3/17/2021
6	3/2/2021	98	-2	-0.02	0		88.2	3/17/2021	80	15		3/17/2021	3/18/2021
7	3/3/2021	96	-2	-0.020408163	0		86.4	3/17/2021	80	14		3/18/2021	3/19/2021
8	3/4/2021	94	-2	-0.020833333	0		84.6	3/17/2021	80	13		4/7/2021	4/8/2021
9	3/5/2021	92	-2	-0.021276596	0		82.8	3/17/2021	80	12
10	3/8/2021	100	8	0.086956522	1		90	3/17/2021	80	9
11	3/9/2021	105	5	0.05	1		94.5	3/16/2021	90	7
12	3/10/2021	110	5	0.047619048	1		99	3/16/2021	90	6
13	3/11/2021	115	5	0.045454545	1		103.5	3/15/2021	100	4
14	3/12/2021	120	5	0.043478261	1		108	3/15/2021	100	3
15	3/15/2021	100	-20	-0.166666667	0		90	3/17/2021	80	2
16	3/16/2021	90	-10	-0.1	0		81	3/17/2021	80	1
17	3/17/2021	80	-10	-0.111111111	0		72	3/18/2021	70	1
18	3/18/2021	70	-10	-0.125	0		63	3/19/2021	60	1
19	3/19/2021	60	-10	-0.142857143	0		54
20	3/22/2021	60	0	0	0		54
21	3/23/2021	63	3	0.05	1		56.7
22	3/24/2021	66	3	0.047619048	1		59.4
23	3/25/2021	69	3	0.045454545	1		62.1
24	3/26/2021	72	3	0.043478261	1		64.8
25	3/29/2021	75	3	0.041666667	1		67.5
26	3/30/2021	78	3	0.04	1		70.2	4/8/2021	70	9
27	3/31/2021	81	3	0.038461538	1		72.9	4/8/2021	70	8
28	4/1/2021	84	3	0.037037037	1		75.6	4/8/2021	70	7
29	4/2/2021	87	3	0.035714286	1		78.3	4/8/2021	70	6
30	4/5/2021	90	3	0.034482759	1		81	4/8/2021	70	3
31	4/6/2021	95	5	0.055555556	1		85.5	4/8/2021	70	2
32	4/7/2021	120	25	0.263157895	1		108	4/8/2021	70	1
33	4/8/2021	70	-50	-0.416666667	0		63
34	4/9/2021	75	5	0.071428571	1		67.5
35	4/12/2021	74	-1	-0.013333333	0		66.6
36	4/13/2021	73	-1	-0.013513514	0		65.7
37	4/14/2021	72	-1	-0.01369863	0		64.8
38	4/15/2021	71	-1	-0.013888889	0		63.9
39	4/16/2021	100	29	0.408450704	1		90
Sheet1

Cell Formulas
Range	Formula
J2	J2	=COUNTIF($J$5:$J$39,J3)
J3	J3	=SMALL(J$5:J$39,1)
E5:E39	E5	=IF(C5>0,"1","0")
G5:G39	G5	=$B5*(1+G$3)
H5:H39	H5	=IFERROR(INDEX($A5:$A$39,AGGREGATE(15,6,(ROW($B5:$B$39)-ROW($B4))/(IF($G$3>0,$B5:$B$39>G5,$B5:$B$39<G5)),1)),"")
I5:I39	I5	=IFERROR(VLOOKUP(H5,$A$5:$B$39,2),"")
J5:J39	J5	=IFERROR(H5-$A5,"")
L5:L23	L5	=IFERROR(INDEX($A$5:$A$39,AGGREGATE(15,6,(ROW($J$5:$J$39)-ROW($J$4))/($J$5:$J$39=$J$3),IF(ROWS(L$5:L5)<=$J$2,ROWS(L$5:L5),""))),"")
M5:M23	M5	=IFERROR(L5+$J$3,"")
C6:C39	C6	=B6-B5
D6:D39	D6	=(B6-B5)/B5
B6:B9	B6	=B5-2
B11:B14	B11	=B10+5
B15	B15	=B14-20
B16:B19	B16	=B15-10
B21:B29	B21	=B20+3
B35:B38	B35	=B34-1
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

I will offer some additional commentary not directly related to the Excel questions. It appears that you are using temperatures as the metrics and have established some percentage of movement in those temperatures as being important. I would advise caution with that approach. The choice of temperature scale will likely change your answers. I don't know what your actual data look like, but if they are based on a non-thermodynamic temperature scale, then it would be prudent to investigate why the analysis should use that scale. Typically, a thermodynamic scale, such as the Kelvin or Rankine scales are used to avoid this issue. As an example, consider the notional example below with evenly spaced time steps and a steady increasing temperature of 2 degrees C per step. For time1, with a temperature of 16 C, we see that its temperature is exceeded at time2, whose temperature is 18 C. But if we do nothing more than convert temperatures from degrees C to Kelvin, then we see that time1's temperature of 289.15 K is exceeded at time13. From a thermodynamics perspective, the latter would be considered the correct answer.

MrExcel20210420.xlsx
	AA	AB	AC	AD	AE	AF	AG
1
2				>	>
3				8%	8%
4	Time	T(C)	T(K)	T(C)_th	T(K)_th	date_T(C)	date_T(K)
5	time1	16	289.15	17.28	312.282	time2	time13
6	time2	18	291.15	19.44	314.442	time3	time14
7	time3	20	293.15	21.6	316.602	time4	time15
8	time4	22	295.15	23.76	318.762	time5	time16
9	time5	24	297.15	25.92	320.922	time6
10	time6	26	299.15	28.08	323.082	time8
11	time7	28	301.15	30.24	325.242	time9
12	time8	30	303.15	32.4	327.402	time10
13	time9	32	305.15	34.56	329.562	time11
14	time10	34	307.15	36.72	331.722	time12
15	time11	36	309.15	38.88	333.882	time13
16	time12	38	311.15	41.04	336.042	time14
17	time13	40	313.15	43.2	338.202	time15
18	time14	42	315.15	45.36	340.362	time16
19	time15	44	317.15	47.52	342.522
20	time16	46	319.15	49.68	344.682
Sheet1

Cell Formulas
Range	Formula
AC5:AC20	AC5	=AB5+273.15
AD5:AD20	AD5	=$AB5*(1+AD$3)
AE5:AE20	AE5	=$AC5*(1+AE$3)
AF5:AG20	AF5	=IFERROR(INDEX($AA$5:$AA$20,MATCH(AD5,AB$5:AB$20,1)+1),"")