MIN/MAX functions and reporting from a different column

[spodosaurus]

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]

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/

 

[Tom Ogilvy]

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

 

[Biff]

Hi!

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

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]

Hi!




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('workbook1.xls'!MAX('workbook1.xls'!$C$2:$C$100),$C$2:$C$100,0))

--
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/

 

[Roger Govier]

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]

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 :-(



--
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/

 

[Roger Govier]

Hi

I think your second Workbook reference is superfluous.
Try
=INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0))


Regards

Roger Govier

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]

Hi

I think your second Workbook reference is superfluous.
Try
=INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0))

Gives me a circular error, so it appears I might need it :-/

I'm wondering if I have the workbook references on the correct sides of
all the parentheses...?

Regards

Roger Govier

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 :-(

--
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]

Hi

I think your second Workbook reference is superfluous.
Try
=INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0))

Gives me a circular error, so it appears I might need it :-/

I'm wondering if I have the workbook references on the correct sides of
all the parentheses...?

Okay, starting from here:

=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))

I think the MATCH afunction might actually need extra references to the
workbook for its second argument:

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

This tells it to look in Workbook1.xls for the first argument, but then
perhaps it's looking to the workbook that it's in (Workbook19.xls) for
the $C$2:$C$100 value? I'm posting this from a separate computer because
the one that I'm working on is not networked at present, so bare with me
while I speculate then travel back and forth to test things.

Regards

Roger Govier

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 :-(

--
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/

 

[Roger Govier]

Hi

Sorry, I think its me being rather sleepy on a Sunday afternoon!!

You are right, you do need the second Workbook reference, PLUS a fourth
one before the final cell range, otherwise it will be using cells
C2:C1000 of your current workbook.

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


Regards

Roger Govier

Hi

I think your second Workbook reference is superfluous.
Try
=INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(MAX([Workbook1.xls]'sheet1'!$C$2:$C$100),$C$2:$C$100,0))

Gives me a circular error, so it appears I might need it :-/

I'm wondering if I have the workbook references on the correct sides
of all the parentheses...?

Okay, starting from here:

=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))


I think the MATCH afunction might actually need extra references to
the workbook for its second argument:

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


This tells it to look in Workbook1.xls for the first argument, but
then perhaps it's looking to the workbook that it's in
(Workbook19.xls) for the $C$2:$C$100 value? I'm posting this from a
separate computer because the one that I'm working on is not networked
at present, so bare with me while I speculate then travel back and
forth to test things.

Regards

Roger Govier



spodosaurus wrote:

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 :-(

 

[Ragdyer]

Why not let XL create the links (paths) for you?

Open all the WBs and start the formula from scratch.

=INDEX(

Now, navigate to the WB in question, click in the starting cell, drag to the
ending cell, then enter a comma in the formula *in the formula bar*.

(You'll see that XL has inserted the actual path for you.)

Now, continue typing in the formula bar:

MATCH(MAX(

And continue on ... navigating to the WBs and cells in question, and then
typing in the punctuation and functions.
When finished, hit <Enter>, and you should have your properly configured
formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

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

Gives me a circular error, so it appears I might need it :-/

I'm wondering if I have the workbook references on the correct sides of
all the parentheses...?

Okay, starting from here:

=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))

I think the MATCH afunction might actually need extra references to the
workbook for its second argument:

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

This tells it to look in Workbook1.xls for the first argument, but then
perhaps it's looking to the workbook that it's in (Workbook19.xls) for
the $C$2:$C$100 value? I'm posting this from a separate computer because
the one that I'm working on is not networked at present, so bare with me
while I speculate then travel back and forth to test things.

Regards

Roger Govier



spodosaurus wrote:

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)) =INDEX([Workbook1.xls]'sheet1'!$A$2:$A$100,MATCH(([Workbook1.xls]'sheet1'!MA
X([Workbook1.xls]'sheet1'!$C$2:$C$100)),$C$2:$C$100,0))

--
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/