Formula issue with LARGE and SMALL

The problem with Small() or Large() or even Rank() is that they all count the same value as the same priority, and the next number is the total of all the same +1, so with 3 of the same number, they all rank as = 1 or 2 or 3, then the next number (lowest or highest) in the sequence is equal to rank 4

Possible Solutions for the first part of your question
For your 2 Large() funtions:
(2nd largest):

Formula:

=SUMPRODUCT(MAX(($A$1:$A$13<MAX($A$1:$A$13))*$A$1:$A$13))

or

Formula:

=LARGE($A$1:$A$13,COUNTIF($A$1:$A$13,MAX($A$1:$A$13))+1)

(3rd largest):

Formula:

=SUMPRODUCT(MAX(($A$1:$A$13<SUMPRODUCT(MAX(($A$1:$A$13<MAX($A$1:$A$13))*$A$1:$A$13)))*$A$1:$A$13))

or

Formula:

=SUMPRODUCT(MAX(($A$1:$A$13<LARGE($A$1:$A$13,COUNTIF($A$1:$A$13,MAX($A$1:$A$13))+1))*$A$1:$A$13))

EDIT-
or

Formula:

=LARGE($A$1:$A$13,COUNTIF($A$1:$A$13,">="&LARGE($A$1:$A$13,COUNTIF($A$1:$A$13,MAX($A$1:$A$13))+1))+1)

Still working on the second part

See Attached, all above solutions and the SMALL solution for CF as well
(Note - I copied your SMALL() CF values under the LARGE() values, so ranges will have to be adjusted accordingly)
Return 2nd smallest value from a range :

Formula:

=SMALL($A$18:$A$27,COUNTIF($A$18:$A$27,MIN($A$18:$A$27))+1)

Hope this helps

Hi

Please see the file

Is this what you looking for?

Hi dredwolf

thanks for the solution... i tried but in some instances, the result returns the same for either the 1st and 2nd value or the 2nd and 3rd values are the same. It happens to both the Large and Small formula.

Any help?

Add a helper column in B and use this, copied down...
=IF(COUNTIF($A$1:A1,A1)>1,"",A1)
then use this, copied down, to find the values you want...
=LARGE($B$1:$B$13,ROW(A2))

Well, seeing as how it's been a month since I posted the solution, with no feed back, I suggest you check out the other solutions...sorry