# Posts by healthguy

• ## Quick help with an IF formula in excel not working on large dataset

Hey all,

I am working with a large dataset and want to use the IF formula in excel to reference a table of initial visit dates for clients for serial rows of following visits. Each row has the client ID and I want to use the IF formula to reference the client ID with the ID from the initial visit table, but it keeps returning FALSE when I know there is a initial visit for each client. Any suggestions?

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

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

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

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.

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

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.

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

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

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

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.

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

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.

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

Thanks for the heads up. I attached it.

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