Lookup columns (varying qty), then find MIN

[NRath]

I need to find the min value in each row for all columns with a heading of a
specific format (TEMP 1, TEMP 2, TEMP 3, ...). It can't include other
formats like ATEMP 1, ATEMP 2, ZTEMP 1, etc. The qty of TEMP 1, TEMP 2 will
vary up to say TEMP 20, so I need to look for all that have
TEMP[space]NUMBER. The return would be in some column on the right side.
Below was created manually as an example.

Time TEMP 1 TEMP 2 TEMP 3 ATEMP 1 ATEMP 2 Min Temp
9:00:00 32.4 40.8 32.2 29.60 29.62 32.2
9:03:00 32.5 40.6 32.3 29.66 29.69 32.3
9:06:00 32.1 40.5 32.7 29.67 29.74 32.1
9:09:00 32.9 31.1 32.2 29.64 29.68 31.1

I've tried a couple things, but no luck.

Thanks in advance.

 

[Charabeuh]

Hello,

For your example:

I suppose your data are in range A1 to F5

Put in cell G2 the array formula:
=MIN(IF(LEFT($B$1:$F$1,1)="T",B2:F2,""))
(Instead of Enter, press Ctrl+Shift+Enter to validate your formula)

copy the formula from G2 to G3 to G5

Hope this will help you

i apologize for my english.

 

[Shane Devenshire]

Hi,

The basic idea is an array formula of the form:

=MIN(IF(LEFT(B$1:G$1,5)="Temp ",B2:G2,""))

This assumes that titles are on row 1 and data can run to column G, you
would change the column G reference to reflect the last data column.

This is an array formula so you enter it by pressing Shift+Ctrl+Enter

 

[Charabeuh]

To prevent that one or more blank (=empty) values in columns of type TEMP N
made the formula of my last post return 0,
one could use the followin array formula:

=MIN(IF(LEFT($B$1:$F$1,1)="T",IF(B2:F2<>"",B2:F2,"")))

(nb: if all values in columns of type TEMP N are empty, then this formula
will nevertheless return 0)

Hello,

For your example:

I suppose your data are in range A1 to F5

Put in cell G2 the array formula:
=MIN(IF(LEFT($B$1:$F$1,1)="T",B2:F2,""))
(Instead of Enter, press Ctrl+Shift+Enter to validate your formula)

copy the formula from G2 to G3 to G5

Hope this will help you

i apologize for my english.




"NRath" <[email protected]> a écrit dans le message de
groupe de discussion :
(e-mail address removed)...

I need to find the min value in each row for all columns with a heading
of a
specific format (TEMP 1, TEMP 2, TEMP 3, ...). It can't include other
formats like ATEMP 1, ATEMP 2, ZTEMP 1, etc. The qty of TEMP 1, TEMP 2
will
vary up to say TEMP 20, so I need to look for all that have
TEMP[space]NUMBER. The return would be in some column on the right side.
Below was created manually as an example.

Time TEMP 1 TEMP 2 TEMP 3 ATEMP 1 ATEMP 2 Min Temp
9:00:00 32.4 40.8 32.2 29.60 29.62 32.2
9:03:00 32.5 40.6 32.3 29.66 29.69 32.3
9:06:00 32.1 40.5 32.7 29.67 29.74 32.1
9:09:00 32.9 31.1 32.2 29.64 29.68 31.1

I've tried a couple things, but no luck.

Thanks in advance.

 

[NRath]

I'll have to go with Shane's response, but looking at each response and the
differences has taught me, so Thank You for each of them!

That seemed too easy. Now I need to use the same table and find the average
for the same formatted text- all columns with TEMP 1, TEMP 2, TEMP 3... and
not including any of the others. Remebering that it has to look up the
header to find which columns to use in the average, I can't just use
=AVERAGE(B22) and there could be 60 or 70 "TEMP" columns.

Hi,

The basic idea is an array formula of the form:

=MIN(IF(LEFT(B$1:G$1,5)="Temp ",B2:G2,""))

This assumes that titles are on row 1 and data can run to column G, you
would change the column G reference to reflect the last data column.

This is an array formula so you enter it by pressing Shift+Ctrl+Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I need to find the min value in each row for all columns with a heading of a
specific format (TEMP 1, TEMP 2, TEMP 3, ...). It can't include other
formats like ATEMP 1, ATEMP 2, ZTEMP 1, etc. The qty of TEMP 1, TEMP 2 will
vary up to say TEMP 20, so I need to look for all that have
TEMP[space]NUMBER. The return would be in some column on the right side.
Below was created manually as an example.

Time TEMP 1 TEMP 2 TEMP 3 ATEMP 1 ATEMP 2 Min Temp
9:00:00 32.4 40.8 32.2 29.60 29.62 32.2
9:03:00 32.5 40.6 32.3 29.66 29.69 32.3
9:06:00 32.1 40.5 32.7 29.67 29.74 32.1
9:09:00 32.9 31.1 32.2 29.64 29.68 31.1

I've tried a couple things, but no luck.

Thanks in advance.

 

[Charabeuh]

Hello,

To find the average for columns with TEMP 1, TEMP 2,...
we can add the corresponding values of the row and
divide the sum by the number of cells in the row that
are not blank.

we can use the function sumproduct.

To find the sum, one could use :
=SUMPRODUCT( (LEFT(B$1:F$1;4,4)="TEMP" ) * ( B2:F2) )

To find the number of non blank cells, one could use :
=SUMPRODUCT( (LEFT(B$1:F$1;4,4)="TEMP" ) * ( B2:F2<>"") )

The function to find the average is :
=SUMPRODUCT( (LEFT(B$1:F$1;4,4)="TEMP" ) * ( B2:F2) ) /
SUMPRODUCT( (LEFT(B$1:F$1;4,4)="TEMP" ) * ( B2:F2<>"") )
(to enter this formula, press just the key 'Enter')

Titles are assumed to be on row 1 from column 2.
Column "F" will be replaced by the reference of the
last column of your data.

Does this help you ?

I'll have to go with Shane's response, but looking at each response and
the
differences has taught me, so Thank You for each of them!

That seemed too easy. Now I need to use the same table and find the
average
for the same formatted text- all columns with TEMP 1, TEMP 2, TEMP 3...
and
not including any of the others. Remebering that it has to look up the
header to find which columns to use in the average, I can't just use
=AVERAGE(B22) and there could be 60 or 70 "TEMP" columns.

Hi,

The basic idea is an array formula of the form:

=MIN(IF(LEFT(B$1:G$1,5)="Temp ",B2:G2,""))

This assumes that titles are on row 1 and data can run to column G, you
would change the column G reference to reflect the last data column.

This is an array formula so you enter it by pressing Shift+Ctrl+Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I need to find the min value in each row for all columns with a heading
of a
specific format (TEMP 1, TEMP 2, TEMP 3, ...). It can't include other
formats like ATEMP 1, ATEMP 2, ZTEMP 1, etc. The qty of TEMP 1, TEMP 2
will
vary up to say TEMP 20, so I need to look for all that have
TEMP[space]NUMBER. The return would be in some column on the right
side.
Below was created manually as an example.

Time TEMP 1 TEMP 2 TEMP 3 ATEMP 1 ATEMP 2 Min Temp
9:00:00 32.4 40.8 32.2 29.60 29.62 32.2
9:03:00 32.5 40.6 32.3 29.66 29.69 32.3
9:06:00 32.1 40.5 32.7 29.67 29.74 32.1
9:09:00 32.9 31.1 32.2 29.64 29.68 31.1

I've tried a couple things, but no luck.

Thanks in advance.

 

[Charabeuh]

Hello,

To find the average for columns with TEMP 1, TEMP 2,...
we can add the corresponding values of the row and
divide the sum by the number of cells in the row that
are not blank.

we can use the function sumproduct.

To find the sum, one could use :
=SUMPRODUCT( (LEFT(B$1:F$1;4,4)="TEMP" ) * ( B2:F2) )

To find the number of non blank cells, one could use :
=SUMPRODUCT( (LEFT(B$1:F$1;4,4)="TEMP" ) * ( B2:F2<>"") )

The function to find the average is :
=SUMPRODUCT( (LEFT(B$1:F$1;4,4)="TEMP" ) * ( B2:F2) ) /
SUMPRODUCT( (LEFT(B$1:F$1;4,4)="TEMP" ) * ( B2:F2<>"") )
(to enter this formula, press just the key 'Enter')

Titles are assumed to be on row 1 from column 2.
Column "F" will be replaced by the reference of the
last column of your data.

Does this help you ?

I'll have to go with Shane's response, but looking at each response and the
differences has taught me, so Thank You for each of them!

That seemed too easy. Now I need to use the same table and find the average
for the same formatted text- all columns with TEMP 1, TEMP 2, TEMP 3... and
not including any of the others. Remebering that it has to look up the
header to find which columns to use in the average, I can't just use
=AVERAGE(B22) and there could be 60 or 70 "TEMP" columns.

Hi,

The basic idea is an array formula of the form:

=MIN(IF(LEFT(B$1:G$1,5)="Temp ",B2:G2,""))

This assumes that titles are on row 1 and data can run to column G, you
would change the column G reference to reflect the last data column.

This is an array formula so you enter it by pressing Shift+Ctrl+Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I need to find the min value in each row for all columns with a heading of a
specific format (TEMP 1, TEMP 2, TEMP 3, ...). It can't include other
formats like ATEMP 1, ATEMP 2, ZTEMP 1, etc. The qty of TEMP 1, TEMP 2 will
vary up to say TEMP 20, so I need to look for all that have
TEMP[space]NUMBER. The return would be in some column on the right side.
Below was created manually as an example.

Time TEMP 1 TEMP 2 TEMP 3 ATEMP 1 ATEMP 2 Min Temp
9:00:00 32.4 40.8 32.2 29.60 29.62 32.2
9:03:00 32.5 40.6 32.3 29.66 29.69 32.3
9:06:00 32.1 40.5 32.7 29.67 29.74 32.1
9:09:00 32.9 31.1 32.2 29.64 29.68 31.1

I've tried a couple things, but no luck.

Thanks in advance.