# Identify lowest/ highest 3 in range

#### sjcwhittle1

##### New Member
I am trying to find a method for determining the lowest & highest three values in a set of data whilst ignoring blank cells.

Column A will be text and columns C,E,F and G will be numbers (these numbers will be unique for the most part)

I need to be able to identify the lowest and then highest three by the identifer in Column A

The only formula I have found is to identify the lowest:
But this does not work if I am looking in mutiple rows.

=INDEX(A2:A42,MATCH(LARGE(IF(A2:A42<>0,B2:B42,0),1),B2:B42,0))
(Change the 1 to a 2 to get the second smallest number)

Does anyone have an idea?

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
If I understood you correctly, try:

For 3 smallest:

=INDEX(\$A\$1:\$A\$42,SUM(IF(ISNUMBER(SEARCH(SMALL(IF(\$C\$2:\$G\$42<>0,\$C\$2:\$G\$42),ROW(\$A1)),\$C\$2:\$G\$42)),ROW(\$A\$1:\$A\$42))))

For 3 largest:

=INDEX(\$A\$1:\$A\$42,SUM(IF(ISNUMBER(SEARCH(LARGE(IF(\$C\$2:\$G\$42<>0,\$C\$2:\$G\$42),ROW(\$A1)),\$C\$2:\$G\$42)),ROW(\$A\$1:\$A\$42))))

Both formulas are confirmed with CTRL+SHIFT+ENTER not just ENTER

Both formulas are copied down 3 rows.

sjcwhittle1

Welcome to the Mr Excel board!

See if this is what you want. It does rely on the smallest 3 and largest 3 being unique. (You will need to adjust the ranges as I have just used a samll range to demonstrate).

Formula in F2:
=INDEX(\$A\$1:\$A\$6,MIN(IF(\$B\$1:\$D\$6=SMALL(\$B\$1:\$D\$6,ROWS(F\$2:F2)),ROW(\$B\$1:\$D\$6))))
Confirmed with Ctrl+Shift +Enter and copied down.

G2:
=INDEX(\$A\$1:\$A\$6,MIN(IF(\$B\$1:\$D\$6=LARGE(\$B\$1:\$D\$6,ROWS(G\$2:G2)),ROW(\$B\$1:\$D\$6))))
Confirmed with Ctrl+Shift +Enter and copied down.
Mr Excel.xls
ABCDEFGH
1Fred5811Smallest 3Largest 3
2Tom6322KenTom
3Bill7419SamBill
4Sam21217TomTed
5Ken11315
6Ted91814
7
Smallest and Largest

Thanks for the help but this dosen't work for me?

an example of the data I have is: ("Name" = cell "A1")

Name | Date |Batting |Date |Batting| Date| Batting
Dave | Mar-06 |1 |Mar-07 |5| Mar-08| 1
Mike |Mar-06 |2 |Mar-07 |84
Tim |Mar-06 |13
Rob |Mar-06| 0.4| Mar-07 |0.4| Mar-08| 0.1
Ben |Mar-06| 5| Mar-07| 3| Mar-08| 5
Tom |Mar-06| 0.1|
Mark |Mar-06| 0.51| Mar-07| 1| Mar-08| 15

I am trying to find the two names with the highest batting and lowest batting score over the three years - where some cells may be blank.

In the above Tom would be lowest then Rob.
HIghest would be Mike then Mark.

In the above Tom would be lowest then Rob
Not sure I understand this as they both have a lowest of 0.1

Why is it Tom then Rob? ..rather than Rob then Tom? ..or just both equal?

See if this might be some help, it doesn't order the lowest 2 or highest 2 but does identify them.

H2: =MIN(IF((\$B\$1:\$G\$1="Batting")*(B2:G2<>""),B2:G2))
Confirmed with Ctrl+Shift+Enter and copied down.
I2: =MAX(IF((\$B\$1:\$G\$1="Batting")*(B2:G2<>""),B2:G2))
Confirmed with Ctrl+Shift+Enter and copied down.

Then
1. Select H2:H8
2. Format|Conditional Formatting...|Condition 1:|Formula is: =H2<=SMALL(\$H\$2:\$H\$8,2)|Format|Patterns|Select green|OK|OK
3. Select I2:I8
4. Format|Conditional Formatting...|Condition 1:|Formula is: =I2>=LARGE(\$I\$2:\$I\$8,2)|Format|Patterns|Select blue|OK|OK
Mr Excel.xls
ABCDEFGHIJ
1NameDateBattingDateBattingDateBattingLowHigh
2DaveMar-061Mar-075Mar-08115
3MikeMar-062Mar-0784284
4TimMar-06131313
5RobMar-060.4Mar-070.4Mar-080.10.10.4
6BenMar-065Mar-073Mar-08535
7TomMar-060.10.10.1
8MarkMar-060.51Mar-071Mar-08150.5115
9
Lowest and Highest

Hi Peter,
We are trying to find the two batsmen with the highet score - Mike (2+84), then Mark (15+1+.15)

Lowest is calculated a similar way.

I guess this is the problem, I don;t have a full set of data for each player. Is this possible to find the two highest. - conditional formatting would be perfect if it could only identify the two highest rather than just the top one.

1. Formula in H2 (copied down): =SUMIF(B\$1:G\$1,"Batting",B2:G2)
2. Select H2:H8
3. Format|Conditional Formatting...|Condition 1|Formula is: =H2<=SMALL(\$H\$2:\$H\$8,2)|Format|Patterns|choose green|OK|Add>>|Condition 2|Formula is: =H2>=LARGE(\$H\$2:\$H\$8,2)|Format|Patterns|choose blue|OK|OK
Mr Excel.xls
ABCDEFGHI
1NameDateBattingDateBattingDateBattingTotal
2DaveMar-061Mar-075Mar-0817
3MikeMar-062Mar-078486
4TimMar-061313
5RobMar-060.4Mar-070.4Mar-080.10.9
6BenMar-065Mar-073Mar-08513
7TomMar-060.10.1
8MarkMar-060.51Mar-071Mar-081516.51
9
Lowest and Highest

You can also try:
Book2
ABCDEFGHIJKLM
1NameDateBattingDateBattingDateBattingScore SummaryHighestLowest
2Dave6-Mar-0617-Mar51-Mar-0817MikeTom
3Mike6-Mar-0627-Mar8486MarkRob
4Tim6-Mar-061313
5Rob6-Mar-060.47-Mar0.41-Mar-080.10.9
6Ben6-Mar-0657-Mar31-Mar-08513
7Tom6-Mar-060.10.1
8Mark6-Mar-060.517-Mar11-Mar-081516.51
9
Sheet1

Formula in J2: =SUMPRODUCT((MOD(COLUMN(C2:G2)-COLUMN(C2),2)=0)*(C2:G2)) copied down

Formula in K2: =INDEX(\$A\$2:\$A\$8,MATCH(LARGE(\$J\$2:\$J\$8,ROW(A1)),\$J\$2:\$J\$8,0)) copied down

Formula in L2: =INDEX(\$A\$2:\$A\$8,MATCH(SMALL(\$J\$2:\$J\$8,ROW(A1)),\$J\$2:\$J\$8,0)) copied down

thanks - but there is a massive amt of data on the post - is this correct?

thanks - but there is a massive amt of data on the post - is this correct?
Can you explain this? Which post(s)? can you see the sample sheets posted by NBVC and me?

Replies
4
Views
302
Replies
1
Views
388
Replies
5
Views
245
Replies
5
Views
344
Replies
5
Views
1K

1,219,892
Messages
6,150,814
Members
450,985
Latest member
Andynair7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back