How to create an excel formula that will average the 2 highest values that are separated by a 3 week difference in testing date

[healthguy]

Hey all,

I am looking for some help creating a formula to analyze some health data that I am looking at. I have a range of testing dates for a preventative screening and I am looking to calculate the average of the 2 highest values separated by 21 days. I will attach a sample spreadsheet of an example of what it looks like, but I want to be able to replicate this over multiple samples (around 200 or so different groupings of testings). I have played around using a few different functions, but have failed to put it all together. I appreciate help in any way possible. Thank you!

	Days Post	Test score
pt1	2​	1.5​
pt1	21​	2​
pt1	34​	3​
pt1	35​	2​
pt1	61​	5​
pt1	100​	6​
pt1	121​	2​
pt1	122​	7​
pt1	134​	8​
pt1	156​	1.4​
pt1	200​	5.3​
pt1	455​	3.4​
pt1	1133​	2​

 

[KRice]

Could you please clarify your question...
Does "Days Post" refer to the number of days elapsed since a singular event, so that 2, 21, 34... days have a common zero time reference point?
And is the function to return the average of the two largest values separated by at least 21 days, or exactly 21 days? In your example, only two data points are separated by 21 days (100 and 121 days).

 

[healthguy]

Yes, sorry for the confusion. The is a 0 time point, which I should have included. That 0 point is the beginning and only sets the reference for the following points. The number of days then refer to the days since that 0 time point. The function should return the average of the 2 highest that are separated by at least 21 days. The overall goal is to find the person's peak function from the test, which is the average of the 2 largest values separated by at least 3 weeks (21 days). So the first step is finding the largest value from the test (8). Then from here, you need to find the second largest value separated by 21 or more days. The next highest value is 7, but since it is within 21 days, we have to exclude that. The next highest value is 6, which is more than 21 days - so we use this as the second value. The average between 6 and 8 give us the 7 we are looking for. That is how I would find it manually.

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1		Days Post	Test score
2	pt1	2	1.5		7
3	pt1	21	2
4	pt1	34	3
5	pt1	35	2
6	pt1	61	5
7	pt1	100	6
8	pt1	121	2
9	pt1	122	7
10	pt1	134	8
11	pt1	156	1.4
12	pt1	200	5.3
13	pt1	455	3.4
14	pt1	1133	2
Sheet3

Cell Formulas
Range	Formula
E2	E2	=AVERAGE(MAX(C2:C14),AGGREGATE(14,6,C2:C14/(ABS(B2:B14-LOOKUP(2,1/(C2:C14=MAX(C2:C14)),B2:B14))>=21),1))

 

[KRice]

Well done, Eric. I struggled with finding a solution when there are two or more max values...yours handles that well. Thanks for sharing.