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

• 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
• Hi and Welcome to the Forum

To make things a lot easier for everybody ... please attach a sample file ... with an illustration of your expected result ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...

Edited once, last by Carim ().

• Thanks for the heads up. I attached it.

Files

• Thanks for the Test file

However what are the expected results you are looking for ...

Even if you add them manually ... they would help visualize your objective ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...

• The expected results will be used to calculate the threshold maximum of the screening test, which will then be used to calculate additional measurements. From the test file, I expect the number to come out to be 7. I could it manually, but there are a large number of patient profiles.

• Quote

From the test file, I expect the number to come out to be 7

What are all the underlying steps which generate ( and explain...) this result ...???

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...

• 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 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.

• Hello Cedric,

Thanks a lot for ... a more than welcome explanation ...

Attached is your User Defined Function

Hope this will help

Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...

Edited once, last by Carim ().

• Or try this formula solution for average of the 2 largest values separated by 3 weeks (21 days) or over.

=(MAX(C2:C14)+AGGREGATE(14,6,C2:C14/((INDEX(B2:B14,MATCH(MAX(C2:C14),C2:C14,0))-B2:B14)>=21),1))/2

Regards

• Thank you for your help on this! Will I be able to use this formula in another sheet of only on this sheet?

• You are welcome

Since your function is a UDF ... you will need to copy the Function which is located in module 1 ... to your other workbook ...

The advantage of such a function is that you can use it like a standard function ... only by adjusting the reference range ...

Question : Do you actually need a stand-alone formula ...?

Hope this clarifies

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...

Edited once, last by Carim ().

• Or try this formula solution for average of the 2 largest values separated by 3 weeks (21 days) or over.

=(MAX(C2:C14)+AGGREGATE(14,6,C2:C14/((INDEX(B2:B14,MATCH(MAX(C2:C14),C2:C14,0))-B2:B14)>=21),1))/2

Regards

Thanks for the formula. The seems to be an issue when I play around using different numbers - it reports out incorrectly. When I put a "10" in C6, the reported value is incorrect. Any suggestions on a fix? I appreciate the assistance.

• As I already said ... a stand-alone formula will require to manually adjust all the ranges ... for each single case ...

But you prefer a stand-alone formula ... can look into it ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...

• Re,

Below is the formula correction you requested :

Code
1. =(MAX(C2:C14)+AGGREGATE(14,6,C2:C14/(ABS(INDEX(B2:B14,MATCH(MAX(C2:C14),C2:C14,0))-B2:B14)>=21),1))/2

Hope this will help you pick the most flexible solution for you ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...

• I'm still learning about macros, but I guess this will be a great hands on way to learn more about them. I was looking for which ever way will be faster to run a lot of the files, and this method will probably work the best. I am trying to figure out if a standalone formula would be possible for a program I am planning on storing the data, but that will be further down the road.

• That was fast - thank you. I will check it out now.

• Re,

Attached is your test file with the two solutions next to each other ...

Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...

• Once you have tested both solutions ... feel free to share your comments

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...

• Quote

FORUM ETIQUETTE

4a.
4b.
4c. Please take the time to thank those who took their time to help you..

Why? Because every single person that helps out at Ozgrid does so on their own free time. This is a free to post forum so show some gratitude. Besides, its nice to be nice and you will find that people will help you, if you help them by being a good responsible forum citizen.

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...