Excel 2007

I have stock investment data in a simple spreadsheet. The spreadsheet is
laid out so that company ticker symbols run across the top row, and the
details associated with each investment (share purchased, buy and sell
prices, breakeven price, ROI, etc.) are listed down the column.

Just for fun I would like to create a formula that shows me the investment
with the greatest ROI. Toward that end, I added a row called ROI, added
formulas to calculate ROI on each investment, and then named the range. I
can use MAX(ROI) to return the highest ROI. I would like to enhance that
formula so that it pulls in the company name. I tried the following:

=HLOOKUP(MAX(ROI),Investment_Table,1)

That returns #N/A. I thought that since Investment_Table is an array that
maybe the formula needs to be entered as such, but that didn't make any
difference. So I did some reading on Excel's various lookup functions but I
just haven't had any luck. I know this is doable, in fact I think I did
something like this several years ago but I haven't been able to find that
file and I can't seem to recall how I solved the problem previously.

How can I create a formula that will pick up the maximum ROI and return the
ticker symbol in row 1 of the same column?

--Tom

Try this...

Assuming your named range, Investment_Table, includes the first row of
column headers.

=INDEX(INDEX(Investment_Table,1,0),MATCH(MAX(ROI), ROI,0))

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Excel 2007

I have stock investment data in a simple spreadsheet. The spreadsheet is
laid out so that company ticker symbols run across the top row, and the
details associated with each investment (share purchased, buy and sell
prices, breakeven price, ROI, etc.) are listed down the column.

Just for fun I would like to create a formula that shows me the investment
with the greatest ROI. Toward that end, I added a row called ROI, added
formulas to calculate ROI on each investment, and then named the range. I
can use MAX(ROI) to return the highest ROI. I would like to enhance that
formula so that it pulls in the company name. I tried the following:

=HLOOKUP(MAX(ROI),Investment_Table,1)

That returns #N/A. I thought that since Investment_Table is an array that
maybe the formula needs to be entered as such, but that didn't make any
difference. So I did some reading on Excel's various lookup functions but
I just haven't had any luck. I know this is doable, in fact I think I did
something like this several years ago but I haven't been able to find that
file and I can't seem to recall how I solved the problem previously.

How can I create a formula that will pick up the maximum ROI and return
the ticker symbol in row 1 of the same column?

--Tom

Sorry that I could not respond sooner. I've been out sick the last several
days and have not been online much in that time.

I tried your solution and it works fine. However, now I have another
question. What if I wanted to go the other way and have a cell that shows
the smallest ROI in the range?

I thought this would be a simple matter of replacing "MAX" in the formula
with "MIN." The problem is that the ROI range contains 20 or so columns,
but only a few have actual data at the moment, so most of the formulas on
the ROI line equate to zero. Therefore, simply using the MIN function
always returns a zero. I tried to get around this by counting the number of
values in the ROI range that are not equal to zero, and then using the SMALL
function to return the smallest ROI that is not equal to zero. But I
couldn't get that working, and anyway I think that the way I was trying to
do it would only work for values greater than zero, and not in cases where
the ROI is negative.

How would I go about returning the smallest non-zero value--positive or
negative--in the ROI range?

--Tom

"T. Valko" wrote in message
...
Try this...

Assuming your named range, Investment_Table, includes the first row of
column headers.

=INDEX(INDEX(Investment_Table,1,0),MATCH(MAX(ROI), ROI,0))

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Excel 2007

I have stock investment data in a simple spreadsheet. The spreadsheet is
laid out so that company ticker symbols run across the top row, and the
details associated with each investment (share purchased, buy and sell
prices, breakeven price, ROI, etc.) are listed down the column.

Just for fun I would like to create a formula that shows me the
investment with the greatest ROI. Toward that end, I added a row called
ROI, added formulas to calculate ROI on each investment, and then named
the range. I can use MAX(ROI) to return the highest ROI. I would like
to enhance that formula so that it pulls in the company name. I tried
the following:

=HLOOKUP(MAX(ROI),Investment_Table,1)

That returns #N/A. I thought that since Investment_Table is an array
that maybe the formula needs to be entered as such, but that didn't make
any difference. So I did some reading on Excel's various lookup
functions but I just haven't had any luck. I know this is doable, in
fact I think I did something like this several years ago but I haven't
been able to find that file and I can't seem to recall how I solved the
problem previously.

How can I create a formula that will pick up the maximum ROI and return
the ticker symbol in row 1 of the same column?

--Tom

Are you sure you want to ignore 0?

If you can have a positive or negative ROI then a 0 ROI is a real
possibility.

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Sorry that I could not respond sooner. I've been out sick the last
several days and have not been online much in that time.

I tried your solution and it works fine. However, now I have another
question. What if I wanted to go the other way and have a cell that shows
the smallest ROI in the range?

I thought this would be a simple matter of replacing "MAX" in the formula
with "MIN." The problem is that the ROI range contains 20 or so columns,
but only a few have actual data at the moment, so most of the formulas on
the ROI line equate to zero. Therefore, simply using the MIN function
always returns a zero. I tried to get around this by counting the number
of values in the ROI range that are not equal to zero, and then using the
SMALL function to return the smallest ROI that is not equal to zero. But
I couldn't get that working, and anyway I think that the way I was trying
to do it would only work for values greater than zero, and not in cases
where the ROI is negative.

How would I go about returning the smallest non-zero value--positive or
negative--in the ROI range?

--Tom

"T. Valko" wrote in message
...
Try this...

Assuming your named range, Investment_Table, includes the first row of
column headers.

=INDEX(INDEX(Investment_Table,1,0),MATCH(MAX(ROI), ROI,0))

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Excel 2007

I have stock investment data in a simple spreadsheet. The spreadsheet
is laid out so that company ticker symbols run across the top row, and
the details associated with each investment (share purchased, buy and
sell prices, breakeven price, ROI, etc.) are listed down the column.

Just for fun I would like to create a formula that shows me the
investment with the greatest ROI. Toward that end, I added a row called
ROI, added formulas to calculate ROI on each investment, and then named
the range. I can use MAX(ROI) to return the highest ROI. I would like
to enhance that formula so that it pulls in the company name. I tried
the following:

=HLOOKUP(MAX(ROI),Investment_Table,1)

That returns #N/A. I thought that since Investment_Table is an array
that maybe the formula needs to be entered as such, but that didn't make
any difference. So I did some reading on Excel's various lookup
functions but I just haven't had any luck. I know this is doable, in
fact I think I did something like this several years ago but I haven't
been able to find that file and I can't seem to recall how I solved the
problem previously.

How can I create a formula that will pick up the maximum ROI and return
the ticker symbol in row 1 of the same column?

--Tom

True. I suppose you could sell an investment for exactly what you paid and
have a zero ROI. What I really want to do is come up with a formula that
will ignore those columns in the range that contain no data. Building on
that thought, maybe the formula could key off the ticker symbol line and be
made to basically ignore columns containing no data. Otherwise, the only
way that I can think of to do this is to shrink the range down to exactly
the number of columns that contain data, and only expand it by inserting
columns when there is new data to enter.

--Tom

"T. Valko" wrote in message
...
Are you sure you want to ignore 0?

If you can have a positive or negative ROI then a 0 ROI is a real
possibility.

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Sorry that I could not respond sooner. I've been out sick the last
several days and have not been online much in that time.

I tried your solution and it works fine. However, now I have another
question. What if I wanted to go the other way and have a cell that
shows the smallest ROI in the range?

I thought this would be a simple matter of replacing "MAX" in the formula
with "MIN." The problem is that the ROI range contains 20 or so columns,
but only a few have actual data at the moment, so most of the formulas on
the ROI line equate to zero. Therefore, simply using the MIN function
always returns a zero. I tried to get around this by counting the number
of values in the ROI range that are not equal to zero, and then using the
SMALL function to return the smallest ROI that is not equal to zero. But
I couldn't get that working, and anyway I think that the way I was trying
to do it would only work for values greater than zero, and not in cases
where the ROI is negative.

How would I go about returning the smallest non-zero value--positive or
negative--in the ROI range?

--Tom

"T. Valko" wrote in message
...
Try this...

Assuming your named range, Investment_Table, includes the first row of
column headers.

=INDEX(INDEX(Investment_Table,1,0),MATCH(MAX(ROI), ROI,0))

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Excel 2007

I have stock investment data in a simple spreadsheet. The spreadsheet
is laid out so that company ticker symbols run across the top row, and
the details associated with each investment (share purchased, buy and
sell prices, breakeven price, ROI, etc.) are listed down the column.

Just for fun I would like to create a formula that shows me the
investment with the greatest ROI. Toward that end, I added a row
called ROI, added formulas to calculate ROI on each investment, and
then named the range. I can use MAX(ROI) to return the highest ROI. I
would like to enhance that formula so that it pulls in the company
name. I tried the following:

=HLOOKUP(MAX(ROI),Investment_Table,1)

That returns #N/A. I thought that since Investment_Table is an array
that maybe the formula needs to be entered as such, but that didn't
make any difference. So I did some reading on Excel's various lookup
functions but I just haven't had any luck. I know this is doable, in
fact I think I did something like this several years ago but I haven't
been able to find that file and I can't seem to recall how I solved the
problem previously.

How can I create a formula that will pick up the maximum ROI and return
the ticker symbol in row 1 of the same column?

--Tom

Can you redo your ROI formula to account for no data?

If there's data then the ROI formula returns a number, if no data then the
ROI formula returns a blank or some other TEXT value. Then, when searching
for the min that TEXT entry will be ignored.

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
True. I suppose you could sell an investment for exactly what you paid
and have a zero ROI. What I really want to do is come up with a formula
that will ignore those columns in the range that contain no data.
Building on that thought, maybe the formula could key off the ticker
symbol line and be made to basically ignore columns containing no data.
Otherwise, the only way that I can think of to do this is to shrink the
range down to exactly the number of columns that contain data, and only
expand it by inserting columns when there is new data to enter.

--Tom

"T. Valko" wrote in message
...
Are you sure you want to ignore 0?

If you can have a positive or negative ROI then a 0 ROI is a real
possibility.

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Sorry that I could not respond sooner. I've been out sick the last
several days and have not been online much in that time.

I tried your solution and it works fine. However, now I have another
question. What if I wanted to go the other way and have a cell that
shows the smallest ROI in the range?

I thought this would be a simple matter of replacing "MAX" in the
formula with "MIN." The problem is that the ROI range contains 20 or so
columns, but only a few have actual data at the moment, so most of the
formulas on the ROI line equate to zero. Therefore, simply using the
MIN function always returns a zero. I tried to get around this by
counting the number of values in the ROI range that are not equal to
zero, and then using the SMALL function to return the smallest ROI that
is not equal to zero. But I couldn't get that working, and anyway I
think that the way I was trying to do it would only work for values
greater than zero, and not in cases where the ROI is negative.

How would I go about returning the smallest non-zero value--positive or
negative--in the ROI range?

--Tom

"T. Valko" wrote in message
...
Try this...

Assuming your named range, Investment_Table, includes the first row of
column headers.

=INDEX(INDEX(Investment_Table,1,0),MATCH(MAX(ROI), ROI,0))

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Excel 2007

I have stock investment data in a simple spreadsheet. The spreadsheet
is laid out so that company ticker symbols run across the top row, and
the details associated with each investment (share purchased, buy and
sell prices, breakeven price, ROI, etc.) are listed down the column.

Just for fun I would like to create a formula that shows me the
investment with the greatest ROI. Toward that end, I added a row
called ROI, added formulas to calculate ROI on each investment, and
then named the range. I can use MAX(ROI) to return the highest ROI.
I would like to enhance that formula so that it pulls in the company
name. I tried the following:

=HLOOKUP(MAX(ROI),Investment_Table,1)

That returns #N/A. I thought that since Investment_Table is an array
that maybe the formula needs to be entered as such, but that didn't
make any difference. So I did some reading on Excel's various lookup
functions but I just haven't had any luck. I know this is doable, in
fact I think I did something like this several years ago but I haven't
been able to find that file and I can't seem to recall how I solved
the problem previously.

How can I create a formula that will pick up the maximum ROI and
return the ticker symbol in row 1 of the same column?

--Tom