Identify lowest/ highest 3 in range

sjcwhittle1

New Member
Joined
Oct 3, 2006
Messages
7
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?
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,780
Office Version
  1. 365
Platform
  1. Windows
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
 

sjcwhittle1

New Member
Joined
Oct 3, 2006
Messages
7
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,780
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

sjcwhittle1

New Member
Joined
Oct 3, 2006
Messages
7
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,780
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What about this then?

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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,780
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,538
Members
410,547
Latest member
htran4
Top