Count occurance of largest duplicate number in a single column ran

[catpro]

I need to count the occurance of only the largest duplicate number in a range
and return "TIE" in cells adjacent to those cells that have the largest
duplicate number.
Example
A B
1 ""
2 ""
5 TIE
3 ""
5 TIE
5 TIE

COUNTIF provides desired output but it's output provides "TIE" for all
matching cells without respect to the largest number.

Thanks,

 

[Ron Coderre]

Let's see if I understand what you want correctly.....

With
A1:A6 containing your posted data

You want to determine the largest value that is duplicated and flag cells
that match that value as "TIE"....even if another single occurring value is
larger.

If that is correct then
B1: =IF(A1=MAX(INDEX((COUNTIF($A$1:$A$6,$A$1:$A$6)>1)*$A$1:$A$6,0)),"TIE","")

Copy that formula down through B6

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[catpro]

The formula works as you configured it to but, I only wish to output "TIE"
for the duplicates of the largest number in the range.

Thanks for your help,

Catpro

 

[JMB]

So if the largest number has no duplicates, you just want "". You are not
looking for the largest duplicated value, just the largest number and, if it
happens to be duplicated, show "TIE"?

If your data is in A1:A12, one possibility:
=IF((COUNTIF(A$1:A$12,MAX(A$1:A$12))>1)*(A1=MAX(A$1:A$12)),"TIE","")

 

[T. Valko]

Ron's formula produces the results you posted in your example. Isn't that
what you wanted?

Or, is this what you want:

A B
1 ""
2 ""
5 ""
3 ""
5 TIE
5 TIE

In my "book" the first instance of a value isn't a duplicate. The duplicate
is the next instance (if it exists) and every instance after that.

Entered in B1 and copied down:

=IF(AND(A1=MAX(A$1:A$6),COUNTIF(A$1:A1,A1)>1),"Tie","")

Biff

 

[Teethless mama]

=IF(A1=MAX(($A$1:$A$6)*(COUNTIF($A$1:$A$6,$A$1:$A$6)>1)),"TIE","")