ExcelBanter - Lookup columns (varying qty), then find MIN

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Lookup columns (varying qty), then find MIN (https://www.excelbanter.com/excel-worksheet-functions/255511-lookup-columns-varying-qty-then-find-min.html)

Lookup columns (varying qty), then find MIN

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.

Lookup columns (varying qty), then find MIN

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

"NRath" wrote:

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.

Lookup columns (varying qty), then find MIN

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)

"Charabeuh" a écrit dans le message de groupe de
discussion : ...
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" a écrit dans le message de
groupe de discussion :
...
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.

Lookup columns (varying qty), then find MIN

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(B2:D2) and there could be 60 or 70 "TEMP" columns.

"Shane Devenshire" wrote:

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

"NRath" wrote:

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.

Lookup columns (varying qty), then find MIN

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 ?

"NRath" wrote:

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(B2:D2) and there could be 60 or 70 "TEMP" columns.

"Shane Devenshire" wrote:

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

"NRath" wrote:

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.