Hi all,

I'm trying to figure out how to get Excel 2003 to report a value from a
separate column where the value reported by a MIN or MAX function
occurs. For example, say this is my setup:

Column A: time
Column B: value 1
Column C: value 2

I want to create a function using the MIN and MAX functions for cell
ranges in either column B or column C plus I want the value that
corresponds to the MIN or MAX value from column A. Basically, I have
data in columns B and C that occurs at times listed in column A. I not
only need to know the MIN and MAX values for certain ranges in columns B
and C but also the times at which these values occur (across hundreds
and hundreds of values with multiple MINs and MAXs, so this is not
something I want to do manually across multiple workbooks).

ummmm

Help!

TIA,

Ari

--
spammage trappage: remove the underscores to reply

I'm going to die rather sooner than I'd like. I tried to protect my
neighbours from crime, and became the victim of it. Complications in
hospital following this resulted in a serious illness. I now need a bone
marrow transplant. Many people around the world are waiting for a marrow
transplant, too. Please volunteer to be a marrow donor:
http://www.abmdr.org.au/
http://www.marrow.org/

spodosaurus wrote:
Hi all,

I'm trying to figure out how to get Excel 2003 to report a value from a
separate column where the value reported by a MIN or MAX function
occurs. For example, say this is my setup:

Column A: time
Column B: value 1
Column C: value 2

I want to create a function using the MIN and MAX functions for cell
ranges in either column B or column C plus I want the value that
corresponds to the MIN or MAX value from column A. Basically, I have
data in columns B and C that occurs at times listed in column A. I not
only need to know the MIN and MAX values for certain ranges in columns B
and C but also the times at which these values occur (across hundreds
and hundreds of values with multiple MINs and MAXs, so this is not
something I want to do manually across multiple workbooks).

ummmm

Help!

TIA,

Ari



Okay, I haven't even tried to apply this yet (I need sleep, it's 3:30am,
I'll continue in the morning), but would this be the sort of thing that
might possibly work:

=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1)

This would find the value in Column A that corresponds to the MIN value
for the range in Column B...right?

Now, If I wanted to do the same thing for a MAX in Column C I'd just
change the functions B's to C's...right?

okay...must sleep now...

Cheers,

Ari

--
spammage trappage: remove the underscores to reply

I'm going to die rather sooner than I'd like. I tried to protect my
neighbours from crime, and became the victim of it. Complications in
hospital following this resulted in a serious illness. I now need a bone
marrow transplant. Many people around the world are waiting for a marrow
transplant, too. Please volunteer to be a marrow donor:
http://www.abmdr.org.au/
http://www.marrow.org/

Yes, that formula would be appropriate and converted as stated.

--
Regards,
Tom Ogilvy

"spodosaurus" wrote in message
...
spodosaurus wrote:
Hi all,

I'm trying to figure out how to get Excel 2003 to report a value from a
separate column where the value reported by a MIN or MAX function
occurs. For example, say this is my setup:

Column A: time
Column B: value 1
Column C: value 2

I want to create a function using the MIN and MAX functions for cell
ranges in either column B or column C plus I want the value that
corresponds to the MIN or MAX value from column A. Basically, I have
data in columns B and C that occurs at times listed in column A. I not
only need to know the MIN and MAX values for certain ranges in columns B
and C but also the times at which these values occur (across hundreds
and hundreds of values with multiple MINs and MAXs, so this is not
something I want to do manually across multiple workbooks).

ummmm

Help!

TIA,

Ari



Okay, I haven't even tried to apply this yet (I need sleep, it's 3:30am,
I'll continue in the morning), but would this be the sort of thing that
might possibly work:

=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1)

This would find the value in Column A that corresponds to the MIN value
for the range in Column B...right?

Now, If I wanted to do the same thing for a MAX in Column C I'd just
change the functions B's to C's...right?

okay...must sleep now...

Cheers,

Ari

--
spammage trappage: remove the underscores to reply

I'm going to die rather sooner than I'd like. I tried to protect my
neighbours from crime, and became the victim of it. Complications in
hospital following this resulted in a serious illness. I now need a bone
marrow transplant. Many people around the world are waiting for a marrow
transplant, too. Please volunteer to be a marrow donor:
http://www.abmdr.org.au/
http://www.marrow.org/

Hi!

would this be the sort of thing that might possibly work:

=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B $100,FALSE),1)

This would find the value in Column A that corresponds to the MIN value for
the range in Column B...right?

Correct.

Since you are indexing a single column range you can omit the column_number
argument. It defaults to 1 when not included.

Also, the match_type argument can be one of three choices: -1, 0, 1. Since
you're using FALSE this will evaluate to 0 for an exact match and will still
work properly. So:

=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,0))

And for the MAX based on column C:

=INDEX($A$2:$A$100,MATCH(MAX($C$2:$C$100),$C$2:$C$ 100,0))

Note that if there are duplicate entries for either max or min the above
formulas will only return the corresponding value for the first instance.

Biff

"spodosaurus" wrote in message
...
spodosaurus wrote:
Hi all,

I'm trying to figure out how to get Excel 2003 to report a value from a
separate column where the value reported by a MIN or MAX function occurs.
For example, say this is my setup:

Column A: time
Column B: value 1
Column C: value 2

I want to create a function using the MIN and MAX functions for cell
ranges in either column B or column C plus I want the value that
corresponds to the MIN or MAX value from column A. Basically, I have data
in columns B and C that occurs at times listed in column A. I not only
need to know the MIN and MAX values for certain ranges in columns B and C
but also the times at which these values occur (across hundreds and
hundreds of values with multiple MINs and MAXs, so this is not something
I want to do manually across multiple workbooks).

ummmm

Help!

TIA,

Ari



Okay, I haven't even tried to apply this yet (I need sleep, it's 3:30am,
I'll continue in the morning), but would this be the sort of thing that
might possibly work:

=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1)

This would find the value in Column A that corresponds to the MIN value
for the range in Column B...right?

Now, If I wanted to do the same thing for a MAX in Column C I'd just
change the functions B's to C's...right?

okay...must sleep now...

Cheers,

Ari

--
spammage trappage: remove the underscores to reply

I'm going to die rather sooner than I'd like. I tried to protect my
neighbours from crime, and became the victim of it. Complications in
hospital following this resulted in a serious illness. I now need a bone
marrow transplant. Many people around the world are waiting for a marrow
transplant, too. Please volunteer to be a marrow donor:
http://www.abmdr.org.au/
http://www.marrow.org/

Biff wrote:
Hi!


would this be the sort of thing that might possibly work:


=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$ B$100,FALSE),1)


This would find the value in Column A that corresponds to the MIN value for
the range in Column B...right?


Correct.

Since you are indexing a single column range you can omit the column_number
argument. It defaults to 1 when not included.

Also, the match_type argument can be one of three choices: -1, 0, 1. Since
you're using FALSE this will evaluate to 0 for an exact match and will still
work properly. So:

=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,0))

And for the MAX based on column C:

=INDEX($A$2:$A$100,MATCH(MAX($C$2:$C$100),$C$2:$C$ 100,0))

Note that if there are duplicate entries for either max or min the above
formulas will only return the corresponding value for the first instance.

Biff

Now, if I wanted the values returned by these equations to appear in a
separate workbook (I have 18 separate workbooks that I want to take
values from and put into one workbook) would I have to add a
'workbook1.xls'! to the beginning of each of the three functions in the
equations? Such as:

=INDEX('workbook1.xls'!$A$2:$A$100,MATCH('workbook 1.xls'!MAX('workbook1.xls'!$C$2:$C$100),$C$2:$C$10 0,0))


"spodosaurus" wrote in message
...

spodosaurus wrote:

Hi all,

I'm trying to figure out how to get Excel 2003 to report a value from a
separate column where the value reported by a MIN or MAX function occurs.
For example, say this is my setup:

Column A: time
Column B: value 1
Column C: value 2

I want to create a function using the MIN and MAX functions for cell
ranges in either column B or column C plus I want the value that
corresponds to the MIN or MAX value from column A. Basically, I have data
in columns B and C that occurs at times listed in column A. I not only
need to know the MIN and MAX values for certain ranges in columns B and C
but also the times at which these values occur (across hundreds and
hundreds of values with multiple MINs and MAXs, so this is not something
I want to do manually across multiple workbooks).

ummmm

Help!

TIA,

Ari



Okay, I haven't even tried to apply this yet (I need sleep, it's 3:30am,
I'll continue in the morning), but would this be the sort of thing that
might possibly work:

=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$ B$100,FALSE),1)

This would find the value in Column A that corresponds to the MIN value
for the range in Column B...right?

Now, If I wanted to do the same thing for a MAX in Column C I'd just
change the functions B's to C's...right?

okay...must sleep now...

Cheers,

Ari


--
spammage trappage: remove the underscores to reply

I'm going to die rather sooner than I'd like. I tried to protect my
neighbours from crime, and became the victim of it. Complications in
hospital following this resulted in a serious illness. I now need a bone
marrow transplant. Many people around the world are waiting for a marrow
transplant, too. Please volunteer to be a marrow donor:
http://www.abmdr.org.au/
http://www.marrow.org/

Hi

With your workbooks, you need to put the workbook name inside [ ]
square brackets, then refer to the sheet name before the cell reference.

[Workbook1.xls]Sheet1!$A$2:$A$100

If they are all in the same subdirectory (or folder) as you are working
with your summary, the above will suffice. If not then you will need
[C:\Excel\My work\Workbook1.xls]Sheet1!$A$2:$A$100

replacing the C:\Excel\My work\Workbook1.xls with your relevant path and
filename.

Regards

Roger Govier



spodosaurus wrote:

Biff wrote:

Hi!


would this be the sort of thing that might possibly work:



=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1)



This would find the value in Column A that corresponds to the MIN
value for the range in Column B...right?



Correct.

Since you are indexing a single column range you can omit the
column_number argument. It defaults to 1 when not included.

Also, the match_type argument can be one of three choices: -1, 0, 1.
Since you're using FALSE this will evaluate to 0 for an exact match
and will still work properly. So:

=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,0))

And for the MAX based on column C:

=INDEX($A$2:$A$100,MATCH(MAX($C$2:$C$100),$C$2:$C$ 100,0))

Note that if there are duplicate entries for either max or min the
above formulas will only return the corresponding value for the first
instance.

Biff


Now, if I wanted the values returned by these equations to appear in a
separate workbook (I have 18 separate workbooks that I want to take
values from and put into one workbook) would I have to add a
'workbook1.xls'! to the beginning of each of the three functions in
the equations? Such as:

=INDEX('workbook1.xls'!$A$2:$A$100,MATCH('workbook 1.xls'!MAX('workbook1.xls'!$C$2:$C$100),$C$2:$C$10 0,0))



"spodosaurus" wrote in message
...

spodosaurus wrote:

Hi all,

I'm trying to figure out how to get Excel 2003 to report a value
from a separate column where the value reported by a MIN or MAX
function occurs. For example, say this is my setup:

Column A: time
Column B: value 1
Column C: value 2

I want to create a function using the MIN and MAX functions for
cell ranges in either column B or column C plus I want the value
that corresponds to the MIN or MAX value from column A. Basically,
I have data in columns B and C that occurs at times listed in
column A. I not only need to know the MIN and MAX values for
certain ranges in columns B and C but also the times at which these
values occur (across hundreds and hundreds of values with multiple
MINs and MAXs, so this is not something I want to do manually
across multiple workbooks).

ummmm

Help!

TIA,

Ari



Okay, I haven't even tried to apply this yet (I need sleep, it's
3:30am, I'll continue in the morning), but would this be the sort of
thing that might possibly work:

=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1)

This would find the value in Column A that corresponds to the MIN
value for the range in Column B...right?

Now, If I wanted to do the same thing for a MAX in Column C I'd just
change the functions B's to C's...right?

okay...must sleep now...

Cheers,

Ari

Roger Govier wrote:
Hi

With your workbooks, you need to put the workbook name inside [ ]
square brackets, then refer to the sheet name before the cell reference.

[Workbook1.xls]Sheet1!$A$2:$A$100

If they are all in the same subdirectory (or folder) as you are working
with your summary, the above will suffice. If not then you will need
[C:\Excel\My work\Workbook1.xls]Sheet1!$A$2:$A$100

replacing the C:\Excel\My work\Workbook1.xls with your relevant path and
filename.

Regards

Roger Govier



It almost works! Excel gives me an error with the second workbook
reference in this formula:

=INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0))

The first and thrid references to Workbook1.xls seem to be okay. I've
even tried adding extra parentheses around the second reference, like this:

=INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(([Workbook1.xls]'sheet1'!MAX([Workbook1.xls]'sheet1'!$C$2:$C$100)),$C$2:$C$100,0))

again, without success :-(



spodosaurus wrote:

Biff wrote:

Hi!


would this be the sort of thing that might possibly work:




=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1)




This would find the value in Column A that corresponds to the MIN
value for the range in Column B...right?




Correct.

Since you are indexing a single column range you can omit the
column_number argument. It defaults to 1 when not included.

Also, the match_type argument can be one of three choices: -1, 0, 1.
Since you're using FALSE this will evaluate to 0 for an exact match
and will still work properly. So:

=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,0))

And for the MAX based on column C:

=INDEX($A$2:$A$100,MATCH(MAX($C$2:$C$100),$C$2:$C$ 100,0))

Note that if there are duplicate entries for either max or min the
above formulas will only return the corresponding value for the first
instance.

Biff



Now, if I wanted the values returned by these equations to appear in a
separate workbook (I have 18 separate workbooks that I want to take
values from and put into one workbook) would I have to add a
'workbook1.xls'! to the beginning of each of the three functions in
the equations? Such as:

=INDEX('workbook1.xls'!$A$2:$A$100,MATCH('workbook 1.xls'!MAX('workbook1.xls'!$C$2:$C$100),$C$2:$C$10 0,0))



"spodosaurus" wrote in message
...

spodosaurus wrote:

Hi all,

I'm trying to figure out how to get Excel 2003 to report a value
from a separate column where the value reported by a MIN or MAX
function occurs. For example, say this is my setup:

Column A: time
Column B: value 1
Column C: value 2

I want to create a function using the MIN and MAX functions for
cell ranges in either column B or column C plus I want the value
that corresponds to the MIN or MAX value from column A. Basically,
I have data in columns B and C that occurs at times listed in
column A. I not only need to know the MIN and MAX values for
certain ranges in columns B and C but also the times at which these
values occur (across hundreds and hundreds of values with multiple
MINs and MAXs, so this is not something I want to do manually
across multiple workbooks).

ummmm

Help!

TIA,

Ari



Okay, I haven't even tried to apply this yet (I need sleep, it's
3:30am, I'll continue in the morning), but would this be the sort of
thing that might possibly work:

=INDEX($A$2:$A$100,MATCH(MIN($B$2:$B$100),$B$2:$B$ 100,FALSE),1)

This would find the value in Column A that corresponds to the MIN
value for the range in Column B...right?

Now, If I wanted to do the same thing for a MAX in Column C I'd just
change the functions B's to C's...right?

okay...must sleep now...

Cheers,

Ari




--
spammage trappage: remove the underscores to reply

I'm going to die rather sooner than I'd like. I tried to protect my
neighbours from crime, and became the victim of it. Complications in
hospital following this resulted in a serious illness. I now need a bone
marrow transplant. Many people around the world are waiting for a marrow
transplant, too. Please volunteer to be a marrow donor:
http://www.abmdr.org.au/
http://www.marrow.org/