Search for 2 specific repeating Columns subtract and solve for MAX

[Vanth_2013]

I am doing data analysis and need to search numerous columns that contain a "MAX" and "START". My example is populated with 3 repeating columns of data that each have a MAX and START column. I want to be able to find the MAX then subtract the START, hold the value and repeat for the next set of MAX and START(s). Once all the variables are found and subtracted, I just want the MAX value. IE. My max value would be $R2-$T2, which equals 15.

I can do this all manually, but is not a very efficient use of my time. I'm sure there is a more elegant way to do my task.

Here is an example of my manual formula: (the below formula does not match my example as to column count)
-------------------------------------------------------------------------------------------------------------------
=MAX($FI4-$FK4,$FQ4-$FS4,$FY4-$GA4,$GG4-$GI4,$GO4-$GQ4,$GW4-$GY4,$HE4-$HG4,$HM4-$HO4,$HU4-$HW4,$IC4-$IE4,$IK4-$IM4,$IS4-$IU4)

The above formula will subtract the 12 pairs and result in the MAX of the 12 variables
This is my manual way to get the MAX I need. I do not want to look through all the columns and keep recreating the formula manually. My total columns are (DK:BLM), which is quite large.

Any help is appreciated!

Thanks You Kindly!

[Special-K]

Does this work (tested a little)?

Range (DK:BLM) ??????????

=MAX(DK4:BLM4-DM4:BLO4)
Array formula, use Ctrl-Shift-Enter

[Jonmo1]

Does this work (tested a little)?

Range (DK:BLM) ??????????

=MAX(DK4:BLM4-DM4:BLO4)
Array formula, use Ctrl-Shift-Enter

I think you'd have to add the criteria using the header ending with (MAX)

Assuming headers in row 1

Formula array entered with CTRL + SHIFT + ENTER
=MAX(IF(RIGHT($FI1:$IS1,5)="(MAX)",$FI4:$IS4-$FK4:$IU4))

[Vanth_2013]

Background:
I have temperature data that is recorded via thermocouples. Eight values are recorded for each specific thermocouple. There are 190 thermocouples repeated as below with varying number sequences.

Column Headers:
T_1_1_1_O_04 (MIN), T_1_1_1_O_04 (MAX), T_1_1_1_O_04 (RANGE), T_1_1_1_O_04 (START), T_1_1_1_O_04 (END), T_1_1_1_O_04 (DELTA), T_1_1_1_O_04 (AVG), T_1_1_1_O_04 (STD)

Here is my array solution:

{=MAX(IF(LEFT($DL$3:$BKA$3, 3)="T_1", IF(RIGHT($DL$3:$BKA$3, 5)="(MAX)",IF(MID($DL$3:$BKA$3, 5,1)<>"O",$DL4:$BKA4,0),0),0)-IF(LEFT($DL$3:$BKA$3, 3)="T_1", IF(RIGHT($DL$3:$BKA$3, 5)="(MAX)",IF(MID($DL$3:$BKA$3, 5,1)<>"O",OFFSET($DL4:$BKA4,0,2),0),0),0))}

To break it down:
I can use the formula along with modifications to seek out a specific thermocouple or pack of thermocouples looking for the MAX temperature rise of searched criteria.
The above formula finds all thermocouples starting with "T_1", has "(MAX)" at the end, does not contain a "O" as the 5th character from the left, then holds the value. Next it will find the same value again and populate it with the value two columns to the right (My START value) and subtract it from the first value. This gives me the MAX temperature rise. So this solves my initial question.

[Jonmo1]

You don't need to RE-Find the same criteria a 2nd time, then offset by 2 columns..

To add the 2 additional criteria (left 3 = T_1, and MID 5 1 <> O) to the formula I posted..
=MAX(IF(RIGHT($FI1:$IS1,5)="(MAX)",$FI4:$IS4-$FK4:$IU4))
changes to
=MAX(IF(RIGHT($FI1:$IS1,5)="(MAX)",IF(LEFT($FI1:$IS1,3)="T_1",IF(MID($FI1:$IS1,5,1)<>"O",$FI4:$IS4-$FK4:$IU4))))

Now just adjust the ranges to your actual need (it would help if the sample book you posted used the 'actual' range you need it to use)

=MAX(IF(RIGHT($DL$3:$BKA$3,5)="(MAX)",IF(LEFT($DL$3:$BKA$3,3)="T_1",IF(MID($DL$3:$BKA$3,5,1)<>"O",$DL$4:$BKA$4-$DN4:$BKC4))))

Still array entered with CTRL + SHIFT + ENTER

[Vanth_2013]

Thanks so much Jonmo1! This makes the statement more elegant and refined. Worked like a charm! It will decrease the processing power and time needed for all of my formulas that scan the 6,500 rows and approx. 1,524 columns. Now to apply this to my MIN and AVERAGE(s).

[Jonmo1]

You're welcome.