Location of a the second biggest number in a range

[Josh G]

I'm trying to find the cell location of the second biggest and third biggest
values in a range. I can find the values themselves with the LARGE formula,
but I haven't been able to figure out how to get the location. Any help
would be appreciated.

 

[Sean Timmons]

=MATCH(LARGE(A1:Z1,2),A1:Z1)

 

[Josh G]

Thanks Sean, that helps some. I'm still having a problem when there are two
equal values in the range, i.e. the second and third largest values are the
same. It is only returning one of the locations. Is there a good way to fix
that problem?

 

[Shane Devenshire]

Hi,

This is a logic problem - suppose the number 15 is the second largest number
and appears once. And suppose the number 14 is the 3rd largest number but it
appears 1000 times. Which one of the 1000 14's do you want to return the
address for? Or do you want to return all 1000 addresses?

I hope this clarifies the problem.

 

[barry houdini]

Let's say you have numbers in A1:Z1 then you can list these from th
largest to the smallest with this formula in A£ copied down

=LARGE(A$1:Z$1,ROWS(A$3:A3))

Now to show the position of each in the range you can use this formul
in B3

=SMALL(IF(A$1:Z$1=A3,COLUMN(A$1:Z$1)-COLUMN(A$1)+1),COUNTIF(A$3:A3,A3))

confirmed with CTRL+SHIFT+ENTER and copied dow

 

[Josh G]

Honestly, I'm not too picky as to which one of the 1000 14s is picked. My
primary concern is that if the 2nd and 3rd largest values are both 14, i get
two different values back.

 

[Josh G]

Thanks Barry, that fixed the duplicate number issue for me. I appreciate it.

 

[Ken]

Josh

A lot of times you can get around the tie issue by inserting an extra
column, and adding the row number divided by 100000000 (or an even
large number if you like) to the values in question. You do your
largest (or smallest) value determiniations based on that new column.
It won't work all the time, but, it is always accurate enough for golf
handicap calculation formulas.

Ken