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!

Thanks for the heads up. I attached it.

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.

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.

Quote from Carim

Hello Cedric,

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

Attached is your User Defined Function

Hope this will help

Display More

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

Quote from bosco_yip

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.

Quote from Carim

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 a standard function ... only by adjusting the reference range ...

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

Hope this clarifies

Display More

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.

Quote from Carim

Re,

Below is the formula correction you requested :

Code

=(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 ...

Display More

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