Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Dynamic Value in a Table
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Dynamic Value in a Table
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Dynamic Value in a Table
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Dynamic Value in a Table
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Dynamic Value in a Table
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Dynamic Value in a Table
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Dynamic Value in a Table
I tired that and was having a problem. However, at the moment I don't
recall what the problem was with that approach. Let me play with it some more this weekend and I'll let you know what I come up with. Another approach that I was considering last night, and also could not get to work, was trying to limit the ROI portion of the formula to a subset of the ROI range. Basically, I would count the number of cells in the ROI range that contain data, and then limit the formula so that it considers only those values. So, in pseudo English, the formula would be something like: MIN(ROI,First_N_Values) Of course, that's not a valid command, but I think you get the idea. At one time I was an Excel guru, but that was with Excel 4, so it's been a few years! Anyway, I seem to remember that there was a way to take the first n values of an array and work with just those values. I just can't seem to pull out of my memory banks exactly how to do that. --Tom "T. Valko" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Dynamic Value in a Table
MIN(ROI,First_N_Values)
Assuming ROI is a one dimensional array. =MIN(INDEX(ROI,1):INDEX(ROI,n)) If ROI was the range A1:J1 =MIN(INDEX(ROI,1):INDEX(ROI,5)) Evaluates to: =MIN(A1:E1) -- Biff Microsoft Excel MVP "Thomas M." wrote in message ... I tired that and was having a problem. However, at the moment I don't recall what the problem was with that approach. Let me play with it some more this weekend and I'll let you know what I come up with. Another approach that I was considering last night, and also could not get to work, was trying to limit the ROI portion of the formula to a subset of the ROI range. Basically, I would count the number of cells in the ROI range that contain data, and then limit the formula so that it considers only those values. So, in pseudo English, the formula would be something like: MIN(ROI,First_N_Values) Of course, that's not a valid command, but I think you get the idea. At one time I was an Excel guru, but that was with Excel 4, so it's been a few years! Anyway, I seem to remember that there was a way to take the first n values of an array and work with just those values. I just can't seem to pull out of my memory banks exactly how to do that. --Tom "T. Valko" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Dynamic Value in a Table
Sorry that it has taken a couple of days for me to reply back on this. I
was out of the office in all day meetings that last two days. I'll have to experiment with that method. The way I got it working this weekend was as follows: In the ROI range I used: IF(D2="","",D7/D5) That blanks out the ROI value in each column that does not contain data. With that done, then MIN(ROI) picks up the minimum value in the range. I thought that I had tried that before and couldn't get it working, but then I have been sick for the last two weeks so maybe I just wasn't thinking straight. I got it working straight away this last weekend. I then made the formula a bit more complex by adding concatenation to match up the smallest ROI with the corresponding ticker symbol, as follows: CONCATENATE(INDEX(INDEX(Investments_Table,1,0),MAT CH(MIN(ROI),ROI,0))," - ")&TEXT(MIN(ROI),"0.00%;[Red](0.00%)") An example of the output of that formula would be: C - 10.15% However, my testing showed that negative values don't get displayed in red, but that the parenthesis do appear. So a negative value would look like this: C - (10.15%). I really wanted to put the negative values in red, but I couldn't do that in the concatenation formula. However, I did find another solution. I defined a name called Smallest_ROI and set the value to SMALL(ROI,{1}). I then created a conditional formatting rule that uses a red font when Smallest_ROI<0. --Tom "T. Valko" wrote in message ... MIN(ROI,First_N_Values) Assuming ROI is a one dimensional array. =MIN(INDEX(ROI,1):INDEX(ROI,n)) If ROI was the range A1:J1 =MIN(INDEX(ROI,1):INDEX(ROI,5)) Evaluates to: =MIN(A1:E1) -- Biff Microsoft Excel MVP "Thomas M." wrote in message ... I tired that and was having a problem. However, at the moment I don't recall what the problem was with that approach. Let me play with it some more this weekend and I'll let you know what I come up with. Another approach that I was considering last night, and also could not get to work, was trying to limit the ROI portion of the formula to a subset of the ROI range. Basically, I would count the number of cells in the ROI range that contain data, and then limit the formula so that it considers only those values. So, in pseudo English, the formula would be something like: MIN(ROI,First_N_Values) Of course, that's not a valid command, but I think you get the idea. At one time I was an Excel guru, but that was with Excel 4, so it's been a few years! Anyway, I seem to remember that there was a way to take the first n values of an array and work with just those values. I just can't seem to pull out of my memory banks exactly how to do that. --Tom "T. Valko" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup table w/ Dynamic Named List | Excel Worksheet Functions | |||
dynamic range with a table below the working table | Excel Worksheet Functions | |||
Dynamic Worksheet Lookup | Excel Worksheet Functions | |||
dynamic lookup | Excel Worksheet Functions | |||
using LOOKUP instead of IF on dynamic row | Excel Worksheet Functions |