• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Top 3 Value only if repeating twice or more

yogpat

New Member
Hi Friends,

How to retrieve Top 3 Max value if it’s that value repeating 2 times or more in excel column.
Function must return max value only & not blank.

Thanks
Eg.

Value
0
50
2
8
2
5
4
7
7
5
7
150
Ans
Top 1 -7
Top 2 -5
Top 3 -3
 

Peter Bartholomew

Well-Known Member
Excel 365 is a very different program, so
Code:
= LET(
  multiplicity, COUNTIFS(numbers, numbers),
  distinct, UNIQUE(FILTER(numbers, multiplicity>1)),
  INDEX(SORT(distinct,,-1), k) )
 

Attachments

yogpat

New Member
Try,
[F2] =LARGE((COUNTIFS($A$2:$A$14,$A$2:$A$14)>1)*($A$2:$A$14<(IF(F1>0,F1,99^99)))*($A$2:$A$14),E2)

E2:E4 = 1,2,3
Thanks for your support.

I am using Microsoft Excel 2007. whenever i change value in column my excel get hang & re-open.

Please suggest.
 

yogpat

New Member
Thanks for your support.

I am using Microsoft Excel 2007. whenever i change value in column my excel get hang & re-open.

Please suggest.
Thanks for your quick support.

I have change 99^99 to 1^3. Its working.

Thanks a lot.
Yog
 

GraH - Guido

Well-Known Member
Thanks for your support.

I am using Microsoft Excel 2007. whenever i change value in column my excel get hang & re-open.

Please suggest.
Thank for letting us know your version. Would not know a reason why it hangs.
You can replace 99^99, it's just a big number, by max of range +1.

Also strange it's working with 1^3 as that results in 1. Or I don't understand my own formula.
 
Top