Hi
I'm looking for a formula that will find the max value (in col C) in a range, and then the value that is 5 rows below the max value, and then place both of these values in separate columns (H and I). There are many subjects (~100) and these are coded numerically in col A (1,2,3 etc), and so I need the formula to apply this range for each subject before moving on to the next one.
The next stage is to also display the data from col D and E which appear in the same rows as the max and max + 5 values of Col C (in col J and K for value 2, L and M for value 3 etc. Note this are not necesarily the max values in their own column, rather they are just the corresponding values that occur in the same row as the max value in col C.
There may be problems arising if the max value occurs with less then 5 rows remaining in the range. This will not occur often as the max should usually be towards the top of the range, but in some cases where it might, is it possible for the formula to return the last value in the range for the subject and not the value for the next subject (which may technically be the 5th row after the max)
I have attached a sample worksheet. Please tell me if you require any additional information
I understand that this is a reasonably complex problem and so I thank you in advance for your assistance
T85K
Bookmarks