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.

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

"Josh G" wrote:

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.

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?

"Sean Timmons" wrote:

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

"Josh G" wrote:

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.

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.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Josh G" wrote:

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?

"Sean Timmons" wrote:

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

"Josh G" wrote:

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.

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.

"Shane Devenshire" wrote:

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.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Josh G" wrote:

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?

"Sean Timmons" wrote:

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

"Josh G" wrote:

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.

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

On Aug 31, 3:46*pm, Josh G wrote:
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.



"Shane Devenshire" wrote:
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.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Josh G" wrote:

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?

"Sean Timmons" wrote:

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

"Josh G" wrote:

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.- Hide quoted text -

- Show quoted text -