UDF Help: Find Maximum within multiple ranges

[strokebow]

Hoping someone can help me. I can currently perform the task I want using the built in max() function and alot of 'manual' work. However, I would be really thankful if someone could help me to create a UDF that will automate this process.

General Problem:
The data contains readings of intensity/magnitude at various times. Within certain time ranges I want to be able to find the maximum for each set of data. The time ranges and the number of them will be defined in the code.

Please find attached the example data set.

From the test data...
I want to find the maximum intensity for each data set (X1, X2, ..., etc), within a given time interval.

e.g.)
Time interval 1 (T1) = 72.5 < t1 < 73.5
Time interval 2 (T2) = 75.5 < t1 < 76.5
Time interval 3 (T3) = 81.5 < t1 < 82.5

In each of these regions I want to find the corresponding maximum values in the data sets. Such that I might have an output file like the attached.

---------------
Input:
m rows x n columns of data.

m and n can vary but the following is always true:
- The first column of data relates to time.
- The subsequent columns of data are magnitudes and represent different data sets.

---------------
Output Desired:
a rows x n columns of data
Here 'n' is the same value as given by the input.
For simplicity in the initial UDF, let a = 3, where a represents the number of time intervals (as should be defined in the code).

---------------
Method:
- Select m rows x n columns of data
- 1st column refers to time. Subsequent columns refer to different tests (data sets) and give magnitudes.
- We want to do this for a different time ranges. Let a = 3, for simplicity.
1. For first time range... Between time range (lower bound < t < upper bound)
2. Find maximum magnitude in the 2nd column within the rows corresponding to this range
3. Go back to #2 and do the same for all the other columns: 3rd, 4th, ..., nth.
4. Go back to #1 and do the same for the next time range... until have completed for the ath.
5. Output results in form of a table.
- Table should have 'a' rows and m columns. The first row should refer to the first time range giving the maximum in each column. (Like in the attached desired output).


Any help is greatly appreciated.

thanks

[StephenR]

Not sure you need VBA for this - you could use array formulae or database function DMAX. Does it have to be VBA?

[Sean Thomas]

you could use this in say row T1 column X28

=MAX(AC18:AC50)
or
if AE3 is the cell which you can change from T1 to T2 etc

=IF(AE3="T1",MAX(IF(AE3="T1",AC18:AC50)),IF(AE3="T2",MAX(IF(AE3="T2",AC114:AC146)),IF(AE3="T3",MAX(IF(AE3="T3",AC306:AC338)))))

[strokebow]

Thanks for your responses. I am still looking in to and trying to get to grips with your suggestions.

I would prefer using VBA to make a UDF since I believe it would be easier. Because I could easily open the code and change either:
1. the time range bounds (upper or lower)
2. increase the number of time ranges (a) I want to look at

I think it would just be easier to use and provide more flexibility without having to be worried about tripping up over some pretty long and complex formula.

[StephenR]

Here is one approach - results go in sheet2:

Please Login or Register  to view this content.