Problem w/ vertical array formula
 

I have a vertical array which worked fine in another worksheet but does not
work now. It looks at a set of cells and sorts them from high to low,
currently it will find the highest number, but when you auto fill the cells
below it, it shows duplicate entries all the way down. Please let me know
what I'm doing wrong with the following formula:
=INDEX(A$2:A$76,MATCH(LARGE($B$2:$B$76-ROW($B$2:$B$76)/75^75,ROWS($A$2:A2)),$B$2:$B$76-ROW($B$2:$B$76)/75^75,0))

column 'a' contains names, column 'b' contains the number. There are 75
entries.

The formula is fine.

As its an array, you have to enter the formular ctrl=shift-enter

"AZExcelNewbie" wrote:

I have a vertical array which worked fine in another worksheet but does not
work now. It looks at a set of cells and sorts them from high to low,
currently it will find the highest number, but when you auto fill the cells
below it, it shows duplicate entries all the way down. Please let me know
what I'm doing wrong with the following formula:
=INDEX(A$2:A$76,MATCH(LARGE($B$2:$B$76-ROW($B$2:$B$76)/75^75,ROWS($A$2:A2)),$B$2:$B$76-ROW($B$2:$B$76)/75^75,0))

column 'a' contains names, column 'b' contains the number. There are 75
entries.

I did

"Anthony Slater" wrote:

The formula is fine.

As its an array, you have to enter the formular ctrl=shift-enter

"AZExcelNewbie" wrote:

I have a vertical array which worked fine in another worksheet but does not
work now. It looks at a set of cells and sorts them from high to low,
currently it will find the highest number, but when you auto fill the cells
below it, it shows duplicate entries all the way down. Please let me know
what I'm doing wrong with the following formula:
=INDEX(A$2:A$76,MATCH(LARGE($B$2:$B$76-ROW($B$2:$B$76)/75^75,ROWS($A$2:A2)),$B$2:$B$76-ROW($B$2:$B$76)/75^75,0))

column 'a' contains names, column 'b' contains the number. There are 75
entries.

AZExcelNewbie wrote:
....
I have a vertical array which worked fine in another worksheet but
does not work now. It looks at a set of cells and sorts them from
high to low, currently it will find the highest number, but when
you auto fill the cells below it, it shows duplicate entries all
the way down. Please let me know what I'm doing wrong with the
following formula:
=INDEX(A$2:A$76,MATCH(LARGE($B$2:$B$76-ROW($B$2:$B$76)/75^75,
ROWS($A$2:A2)),$B$2:$B$76-ROW($B$2:$B$76)/75^75,0))
....

In the other workbook in which a similar formula worked, I'd bet you
weren't using ROW(..)/75^75. The result of this expression will be on
the order of 10^-140. If you try to add such numbers to othre numbers
that are on the order of 1 or higher, there won't be sufficient
precision to store these 'tie-breaker' values, so the 1st argument to
LARGE will be truncated to just the values from $B$2:$B$76.

You need to change your tie-breaker values. Either replace the 75^75
with a likely constant value, like 1E4, or make it sensitive to your
data, e.g., (MIN(ABS($B$2:$B$76))/10)^2. Note that this won't work if
your data range, $B$2:$B$76, contains zero and negative values, since
the MIN(ABS(..)) expression will return zero. In that case, you'd need
to use a suitable constant.