Solved: Functions LARGE and SMALL, avoiding duplicated values

[brunces]

Friends,

Hi, guys. I've searched for this here, but I really didn't find anything related. If there is some, I'm sorry.

I have this situation here...

A1=20
A2=25
A3=25
A4=30
A5=40
A6=40
A7=50

B1=LARGE(A1:A7,1) -> This returns 50. (OK!)
B2=LARGE(A1:A7,2) -> This returns 40. (OK!)
B3=LARGE(A1:A7,3) -> This returns 40. (40 again? It should be 30!)
B4=LARGE(A1:A7,4) -> This returns 30. (It should be 25!)
B5=LARGE(A1:A7,5) -> This returns 25. (It shound be 20!)

I know this things happen because of duplicated values. OK! So, please, do you guys know any way to avoid that kind of result? I mean, a formula which avoids duplicated values and returns the exact numbers?

Thank you for the attention, fellows. :)

Hugs,

Bruno

[Bob Phillips]

=LARGE(IF(MATCH($A$1:$A$7,$A$1:$A$7,0)=ROW($A$1:$A$7)-CELL("Row",$A$1:$A$7)+1,$A$1:$A$7),1)

=LARGE(IF(MATCH($A$1:$A$7,$A$1:$A$7,0)=ROW($A$1:$A$7)-CELL("Row",$A$1:$A$7)+1,$A$1:$A$7),2)

etc.


which are array formulae, it should be committed with Ctrl-Shift-Enter, not just Enter.

[Shazam]

See if this works for you.

=IF(ROW($B2)=2,MAX($A$2:$A$12),MAX(IF($A$2:$A$12<B1,$A$2:$A$12)))

Needs to be confirmed with:

Ctrl+Shift+Enter

[brunces]

Gee, you're the men! LOL :)

Thanks a lot, you both guys!

Hugs,

Bruno

[brunces]

Guys,

I'm here again to ask you one more thing. The solution you have provided me for the original problem worked very fine. But now, I have the same problem in a different way.

Instead of that simple range from A1 to A7, now I have it from A1 to E7. Then I have different values in each one of these cells and I want to use that LARGE function again, without returning duplicated values. In other words, now I have 5 columns, instead of that original single one.

What should I do now? Can you figure out any solution this time? Maybe creating a new funciont using VBA, I don't know...

I hope you can help me, guys.

Thank you very much in advance.

Hugs,

Bruno

[Shazam]

Try...

[brunces]

Shazam,

It worked pretty fine! Thank you very much for your attention! :)

Hugs,

Bruno

[Cyberdude]

Bruno, I rejoice that you have found a workaround, but it troubles me that it appears that the LARGE function isn't working correctly. I tested it and got similar results. It would seem that LARGE has some VERY bad bugs. Are these bugs published and acknowledged by Microsoft?? It's hard to believe that they would leave a function in production like that.

[Bob Phillips]

Bruno, I rejoice that you have found a workaround, but it troubles me that it appears that the LARGE function isn't working correctly. I tested it and got similar results. It would seem that LARGE has some VERY bad bugs. Are these bugs published and acknowledged by Microsoft?? It's hard to believe that they would leave a function in production like that.

There are no documented bugs in LARGE that I am aware of. To what are you referring?