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 PostTest score
    pt12 1.5
    pt121 2
    pt134 3
    pt135 2
    pt161 5
    pt1100 6
    pt1121 2
    pt1122 7
    pt1134 8
    pt1156 1.4
    pt1200 5.3
    pt1455 3.4
    pt11133 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 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 ().

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

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