Formula issue with LARGE and SMALL

[Pearlyn]

Hi all

I have the following 3 problems on hand.

1st problem:

My formulas are as follows to return the 2nd highest and 3rd highest value from the following set of data. I need the result to show me the 2nd and 3rd highest value but ignoring duplicate values.
= LARGE (A1:A13,2). Result is 370. I want the result to be 339
= LARGE (A1:A13,3). Result is 370. I want the result to be 300

300
300
339
370
250
200
138
138
250
370
370
125
150



2nd problem:

I need to do conditional formatting for the 1st lowest and 2nd lowest value for the following set of data. My formula is:
=MIN(C1:C10). I want the result to show that all the number "1" is highlighted. This formula is currently working correctly.
=SMALL(C1:C10,2). I want the result to show that all the number "3" is highlighted as the 2nd lowest value. This formula is not working correctly and does not conditional format if there is more than one "1" in the data.

11
3
5
4
11
1
1
3
5
1

thanks all in adv for your help!

[dredwolf]

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):

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

(3rd largest):

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

EDIT-
or

Please Login or Register  to view this content.

Still working on the second part

[dredwolf]

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 :

Please Login or Register  to view this content.

Hope this helps

[micope21]

Hi

Please see the file

Is this what you looking for?

[Pearlyn]

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?

[FDibbins]

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))

[Jarko28]

Hi Pearlyn,

Here is another alternate solution to the ones already provided. I have attached a sample workbook for you with the conditional formatting for your second problem.

All formulas to be entered using Control+Shift+Enter:

Second Largest Value:
=LARGE(IF(COUNTIF(A$1:A$13,ROW(INDIRECT(MIN(A$1:A$13)&":"&MAX(A$1:A$13)))),ROW(INDIRECT(MIN(A$1:A$13)&":"&MAX(A$1:A$13)))),2)

Third Largest Value:
=LARGE(IF(COUNTIF(A$1:A$13,ROW(INDIRECT(MIN(A$1:A$13)&":"&MAX(A$1:A$13)))),ROW(INDIRECT(MIN(A$1:A$13)&":"&MAX(A$1:A$13)))),3)

Smallest Value:
=SMALL(IF(COUNTIF(C$1:C$10,ROW(INDIRECT(MIN(C$1:C$10)&":"&MAX(C$1:C$10)))),ROW(INDIRECT(MIN(C$1:C$10)&":"&MAX(C$1:C$10)))),1)

Second Smallest Value:
=SMALL(IF(COUNTIF(C$1:C$10,ROW(INDIRECT(MIN(C$1:C$10)&":"&MAX(C$1:C$10)))),ROW(INDIRECT(MIN(C$1:C$10)&":"&MAX(C$1:C$10)))),2)

Let me know if this works for you.

Thanks.

[dredwolf]

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

[Pearlyn]

Hi dredwolf

I checked my formula and there was sth wrong. so your sol does work!

thanks so much!