Identify lowest/ highest 3 in range

[sjcwhittle1]

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?

 

[NBVC]

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]

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
	A	B	C	D	E	F	G	H
1	Fred	5	8	11		Smallest 3	Largest 3
2	Tom	6	3	22		Ken	Tom
3	Bill	7	4	19		Sam	Bill
4	Sam	2	12	17		Tom	Ted
5	Ken	1	13	15
6	Ted	9	18	14
7
Smallest and Largest

 

[sjcwhittle1]

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]

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
	A	B	C	D	E	F	G	H	I	J
1	Name	Date	Batting	Date	Batting	Date	Batting	Low	High
2	Dave	Mar-06	1	Mar-07	5	Mar-08	1	1	5
3	Mike	Mar-06	2	Mar-07	84			2	84
4	Tim	Mar-06	13					13	13
5	Rob	Mar-06	0.4	Mar-07	0.4	Mar-08	0.1	0.1	0.4
6	Ben	Mar-06	5	Mar-07	3	Mar-08	5	3	5
7	Tom	Mar-06	0.1					0.1	0.1
8	Mark	Mar-06	0.51	Mar-07	1	Mar-08	15	0.51	15
9
Lowest and Highest

 

[sjcwhittle1]

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]

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
	A	B	C	D	E	F	G	H	I
1	Name	Date	Batting	Date	Batting	Date	Batting	Total
2	Dave	Mar-06	1	Mar-07	5	Mar-08	1	7
3	Mike	Mar-06	2	Mar-07	84			86
4	Tim	Mar-06	13					13
5	Rob	Mar-06	0.4	Mar-07	0.4	Mar-08	0.1	0.9
6	Ben	Mar-06	5	Mar-07	3	Mar-08	5	13
7	Tom	Mar-06	0.1					0.1
8	Mark	Mar-06	0.51	Mar-07	1	Mar-08	15	16.51
9
Lowest and Highest

 

[NBVC]

You can also try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Date	Batting	Date	Batting	Date	Batting			Score Summary	Highest	Lowest
2	Dave	6-Mar-06	1	7-Mar	5	1-Mar-08	1			7	Mike	Tom
3	Mike	6-Mar-06	2	7-Mar	84					86	Mark	Rob
4	Tim	6-Mar-06	13							13
5	Rob	6-Mar-06	0.4	7-Mar	0.4	1-Mar-08	0.1			0.9
6	Ben	6-Mar-06	5	7-Mar	3	1-Mar-08	5			13
7	Tom	6-Mar-06	0.1							0.1
8	Mark	6-Mar-06	0.51	7-Mar	1	1-Mar-08	15			16.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

 

[sjcwhittle1]

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

 

[Peter_SSs]

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?