Unknown Reference for Offset Function

[nicolehalliday]

Hi everyone!
I currently have a long list of stock tickers in column N and their daily percent price change in column U. I wanted to find out which stock had the biggest gain so (without having to scroll through the 500 rows) so I used =LARGE(U6:U505,1). This works great and returns the percentage value of the biggest gain. However, I also want to return the stock ticker of the corresponding biggest gain. I tried =IF(E8=(U6:U505),OFFSET(E8,,7)) but it didn't work.

Basically I need to identify the largest percentage in column U and then return the cell value of the same row in column N. The daily percentage gains are changing constantly so that's why it cannot be hardcoded, and the offset reference is 'unknown'.

I'd really appreciate some help with this one, thanks!!!

 

[Aladin Akyurek]

Hi everyone!
I currently have a long list of stock tickers in column N and their daily percent price change in column U. I wanted to find out which stock had the biggest gain so (without having to scroll through the 500 rows) so I used =LARGE(U6:U505,1). This works great and returns the percentage value of the biggest gain. However, I also want to return the stock ticker of the corresponding biggest gain. I tried =IF(E8=(U6:U505),OFFSET(E8,,7)) but it didn't work.

Basically I need to identify the largest percentage in column U and then return the cell value of the same row in column N. The daily percentage gains are changing constantly so that's why it cannot be hardcoded, and the offset reference is 'unknown'.

I'd really appreciate some help with this one, thanks!!!

Let Sheet1 (adjust to suit) house the data of interest.

Define VSize as referring to (by means of Insert|Name|Define or Formulas|Name Manager)...

=MATCH(9.99999999999999E+307,Sheet1!$U:$U)-ROW(Sheet1!$U$6)+1

Define TICKERS as referring to:

=OFFSET(Sheet1!$N$9,0,0,VSize)

and GAINS as referring to:

=OFFSET(Sheet1!$U$6,0,0,VSize)

Then consider the following set up, which yields the stock ticker(s) having the largest gain...

Sheet1

A1:

=MAX(GAINS)

A2:

=COUNTIF(GAINS,A1)

A3: Ticker

A4, control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)<=$A$2,INDEX(TICKERS,SMALL(IF(GAINS=$A$1,
    ROW(GAINS)-ROW(INDEX(GAINS,1,1)+1),ROWS($A$4:A4))),"")