Finding the Count in # of Months between the next highest in the range of values.

[EVANWIT84]

Hi All,

I have found the Max Value in Col. P of my attached imaged. However, I also wanted to find out the # of months it takes until the range of values (Col. O) hits a new maximum. Any thoughts on how to best achieve this results? For example, from July 1956 until July 1958 it took 24 months to hit a new peak. From Aug 1954 to Nov 1954 it took 3 months. So I would like this unique count so I can then find the average # of months to hit a new peak.

Thanks

 

[Eric W]

I'm not entirely sure, but maybe:

Book1
	O	P	Q	R	S
6	Cumulative	MAX	Max True	New Max	# of months
7	1000000	1000000	1000000
8	1016146	1016146	1016146		1
9	981591	1016146
10	983273	1016146
11	1014220	1016146
12	1020635	1020635	1020635		4
13	1022758	1022758	1022758		1
14	1055023	1055023	1055023		1
15	1057522	1057522	1057522		1
16	1077882	1077882	1077882		1
17	1110374	1110374	1110374		1
18	1133556	1133556	1133556		1
19	1134874	1134874	1134874		1
20	1174683	1174683	1174683		1
21	1151106	1174683		5
22	1209800	1209800	1209800		2
23	1196634	1209800		6
24	1255124	1255124	1255124		2
25	1295239	1295239	1295239		1
26	1310411	1310411	1310411		1
27	1314262	1314262	1314262		1
28	1311544	1314262		7
29	1342402	1342402	1342402		2
30	1342455	1342455	1342455		1
31	1410852	1410852	1410852		1
32	1463594	1463594	1463594		1
33	1457987	1463594		8
34	1469462	1469462	1469462		2
35	1444033	1469462		9
36	1509415	1509415	1509415		2
Sheet4

Cell Formulas
Range	Formula
P7:P36	P7	=MAX(O$7:O7)
Q7:Q36	Q7	=IF(O7=P7,O7,"")
S8:S36	S8	=IF(Q8="","",ROW(Q8)-LOOKUP(P7,Q$7:Q7/(Q$7:Q7<>""),ROW(P$7:P7)))
R22:R36	R22	=IF(Q22="",MAX(R$7:R21)+1,"")

 

[EVANWIT84]

Didn't work exactly but you have me a good idea on how to solve this thru a Pivot Table.