RANK variable blocks of cells between BLANK cells

[BakerBaker]

I would like to RANK individual blocks of cells within a column which will be a variable number between BLANK cells. In the (simple) example I wish to RANK A1 to A4 and then A6 to A11. A formula in B1 would be copied down to the end of the column, which could be several hundred rows.


A1 22 B1 Formula
A2 46
A3 10
A4 8
A5 BLANK
A6 22
A7 12
A8 43
A9 2
A10 19
A11 18
A12 BLANK

etc

 

[Peter_SSs]

With XL2BB, generally better to use Mini Sheet rather than Table Only. That way we can see the column & row references as well as any formulas etc.

What range(s) can we see in that table and what column is the formula in?

Also, what about including the expected results as requested in my previous post?

 

[BakerBaker]

With XL2BB, generally better to use Mini Sheet rather than Table Only. That way we can see the column & row references as well as any formulas etc.

What range(s) can we see in that table and what column is the formula in?

Also, what about including the expected results as requested in my previous post?

Thank you for looking at this. This is a horse racing handicapping workbook. In the example attached, Col. AD (BEST) is simply a sum of the cells across preceding rows. The number of rows in each "block" varies and there can be anything up to 500 rows with an empty row separating each "block". The current formula in Col. AE (RANK) ranks each number within each "block" in Col. AD from highest to lowest. There are often duplicate numbers as shown by the colours. If possible, I would like the current formula to return a UNIQUE ranking.
The current formula in COL. AE is: =IF(AD5="","",RANK(AD5,INDEX(AD$4:AD5,AGGREGATE(14,6,(ROW(AD$4:AD4)-ROW(AD$4)+1)/(AD$4:AD4=""),1)+1):INDEX(AD5:AD$993,MATCH(TRUE,INDEX(AD5:AD$993="",0),0)-1)))

HorrocksSystems_No.2_Place.xlsm
	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2
3										BEST	RANK	Qualify?
4
5	0	0	76	0	0		32			76	1	FALSE
6	0	0	0	56	0		9.4			56	10	FALSE
7	0	0	0	75	0		11.5			75	3	FALSE
8	0	72	0	0	0		210			72	5	FALSE
9	0	0	72	0	0		38			72	5	FALSE
10	0	0	0	0	71		27			71	7	FALSE
11	0	0	0	76	0		350			76	1	FALSE
12	0	0	0	0	0		2.22			0	11	FALSE
13	0	0	0	0	0		55			0	11	FALSE
14	0	0	0	70	0		16.5			70	8	FALSE
15	0	57	0	0	0		7			57	9	FALSE
16	0	0	0	0	75		14			75	3	FALSE
17
18	72	0	0	0	0		3.3			72	2	FALSE
19	0	0	0	0	0		4.5			0	5	FALSE
20	0	69	0	0	0		4.1			69	3	FALSE
21	0	74	0	0	0		8			74	1	FALSE
22	64	0	0	0	0		12			64	4	FALSE
23	0	0	0	0	0		23			0	5	FALSE
24
25	0	0	88	0	0		14.5			88	3	FALSE
26	0	0	0	87	0		26			87	5	FALSE
27	0	0	0	86	0		340			86	6	FALSE
28	0	0	0	0	85		1000			0	12	FALSE
29	0	78	0	0	0		14			78	9	FALSE
30	0	0	88	0	0		100			88	3	FALSE
31	0	0	82	0	0		10			82	7	FALSE
32	0	0	0	79	0		3.95			79	8	FALSE
33	0	0	76	0	0		240			76	10	FALSE
34	0	0	0	90	0		24			90	2	FALSE
35	0	91	0	0	0		34			91	1	FALSE
36	0	0	76	0	0		2.44			76	10	FALSE
37
Worksheet 2

Cell Formulas
Range	Formula
X5,X35:X37,X33,X28:X31,X15:X25,X12:X13,X8:X10	X5	=IF(D5="","",IF(N5=FALSE,"",IF(S5="NR",0,IF(F5=0,0,IF((U5+V5+W5)>0,0,IF(F5<8,P5,0))))))
AD5:AD37	AD5	=IF(D5="","",IF(N5=FALSE,"",IF(S5="NR",0,SUM(U5:Y5))))
AE5:AE16	AE5	=IF(AD5="","",RANK(AD5,INDEX(AD$4:AD5,AGGREGATE(14,6,(ROW(AD$4:AD4)-ROW(AD$4)+1)/(AD$4:AD4=""),1)+1):INDEX(AD5:AD$993,MATCH(TRUE,INDEX(AD5:AD$993="",0),0)-1)))
AF5:AF37	AF5	=IF(AE5="","",AND(AE5<3,AB5>=4))
AE17:AE37	AE17	=IF(D17="","",IF(AD17="","",RANK(AD17,INDEX(AD$4:AD17,AGGREGATE(14,6,(ROW(AD$4:AD16)-ROW(AD$4)+1)/(AD$4:AD16=""),1)+1):INDEX(AD17:AD$993,MATCH(TRUE,INDEX(AD17:AD$993="",0),0)-1))))
V36:V37,V30:V34,V16:V28,V9:V14,V5:V7	V5	=IF(D5="","",IF(N5=FALSE,"",IF(S5="NR",0,IF(H5=0,0,IF(U5>0,0,IF(H5<8,R5,0))))))
W37,W34:W35,W32,W26:W29,W10:W24,W6:W8	W6	=IF(D6="","",IF(N6=FALSE,"",IF(S6="NR",0,IF(G6=0,0,IF((U6+V6)>0,0,IF(G6<8,Q6,0))))))
Y29:Y37,Y17:Y27,Y11:Y15,Y5:Y9	Y5	=IF(D5="","",IF(N5=FALSE,"",IF(S5="NR",0,IF(F5=0,0,IF((U5+V5+W5+X5)>0,0,IF(F5<8,O5,0))))))
U5:U37	U5	=IF(D5="","",IF(N5=FALSE,"",IF(S5="NR",0,IF(I5=0,0,IF(I5<8,S5,0)))))
AA5:AA37	AA5	=IF(D5="","",IF(N5=FALSE,"",IFERROR(VLOOKUP(D5,Betfair!$A$1:$F$1000,6,FALSE),1000)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AF5:AF700	Cell Value	contains "FALSE"	text	NO
AF5:AF700	Cell Value	contains "TRUE"	text	NO
AA5:AA700	Cell Value	=1000	text	NO
AA5:AA700	Cell	contains a blank value	text	NO
AD3	Cell Value	between 1 and 3	text	NO
AB5:AB700,A5:J700,L5:M700,O5:S700,U5:Y700,AD5:AF700	Cell	contains a blank value	text	NO

 

[Peter_SSs]

Thanks for the XL2BB sample data. Does it include this?

what about including the expected results as requested in my previous post?

 

[BakerBaker]

Thanks for the XL2BB sample data. Does it include this?

The expected (required) result is simply the ranking of the numbers in column AE

 

[Peter_SSs]

23 07 23.xlsm
	AD	AE
1
2
3	BEST	RANK
4
5	76	1
6	56	10
7	75	3
8	72	5
9	72	5.01
10	71	7
11	76	1.01
12	0	11
13	0	11.01
14	70	8
15	57	9
16	75	3.01
17
18	72	2
19	0	5
20	69	3
21	74	1
22	64	4
23	0	5.01
24
25	88	2
26	87	6
27	86	7
28	0	12
29	78	9
30	88	2.01
31	88	2.02
32	79	8
33	88	2.03
34	78	9.01
35	91	1
36	76	11
37
Rank no ties

Cell Formulas
Range	Formula
AE5:AE37	AE5	=IF(AD5="","",RANK(AD5,INDEX(AD$4:AD5,AGGREGATE(14,6,(ROW(AD$4:AD4)-ROW(AD$4)+1)/(AD$4:AD4=""),1)+1):INDEX(AD5:AD$993,MATCH(TRUE,INDEX(AD5:AD$993="",0),0)-1))+(COUNTIF(INDEX(AD$4:AD5,AGGREGATE(14,6,(ROW(AD$4:AD4)-ROW(AD$4)+1)/(AD$4:AD4=""),1)+1):AD5,AD5)-1)/100)

 

[BakerBaker]

23 07 23.xlsm
	AD	AE
1
2
3	BEST	RANK
4
5	76	1
6	56	10
7	75	3
8	72	5
9	72	5.01
10	71	7
11	76	1.01
12	0	11
13	0	11.01
14	70	8
15	57	9
16	75	3.01
17
18	72	2
19	0	5
20	69	3
21	74	1
22	64	4
23	0	5.01
24
25	88	2
26	87	6
27	86	7
28	0	12
29	78	9
30	88	2.01
31	88	2.02
32	79	8
33	88	2.03
34	78	9.01
35	91	1
36	76	11
37
Rank no ties

Cell Formulas
Range	Formula
AE5:AE37	AE5	=IF(AD5="","",RANK(AD5,INDEX(AD$4:AD5,AGGREGATE(14,6,(ROW(AD$4:AD4)-ROW(AD$4)+1)/(AD$4:AD4=""),1)+1):INDEX(AD5:AD$993,MATCH(TRUE,INDEX(AD5:AD$993="",0),0)-1))+(COUNTIF(INDEX(AD$4:AD5,AGGREGATE(14,6,(ROW(AD$4:AD4)-ROW(AD$4)+1)/(AD$4:AD4=""),1)+1):AD5,AD5)-1)/100)

Thank you for taking the time to look at this. I'm able to RANK unique rather than duplicate.

 

[Peter_SSs]

You're welcome. If you wanted whole numbers ..

23 07 23.xlsm
	AD	AE
1
2
3	BEST	RANK
4
5	76	1
6	56	10
7	75	3
8	72	5
9	72	6
10	71	7
11	76	2
12	0	11
13	0	12
14	70	8
15	57	9
16	75	4
17
18	72	2
19	0	5
20	69	3
21	74	1
22	64	4
23	0	6
24
25	88	2
26	87	6
27	86	7
28	0	12
29	78	9
30	88	3
31	88	4
32	79	8
33	88	5
34	78	10
35	91	1
36	76	11
37
Rank no ties (2)

Cell Formulas
Range	Formula
AE5:AE37	AE5	=IF(AD5="","",COUNTIF(INDEX(AD$4:AD5,AGGREGATE(14,6,(ROW(AD$4:AD4)-ROW(AD$4)+1)/(AD$4:AD4=""),1)+1):INDEX(AD5:AD$993,MATCH(TRUE,INDEX(AD5:AD$993="",0),0)-1),">"&AD5) +(COUNTIF(INDEX(AD$4:AD5,AGGREGATE(14,6,(ROW(AD$4:AD4)-ROW(AD$4)+1)/(AD$4:AD4=""),1)+1):AD5,AD5)))

 

[BakerBaker]

You're welcome. If you wanted whole numbers ..

23 07 23.xlsm
	AD	AE
1
2
3	BEST	RANK
4
5	76	1
6	56	10
7	75	3
8	72	5
9	72	6
10	71	7
11	76	2
12	0	11
13	0	12
14	70	8
15	57	9
16	75	4
17
18	72	2
19	0	5
20	69	3
21	74	1
22	64	4
23	0	6
24
25	88	2
26	87	6
27	86	7
28	0	12
29	78	9
30	88	3
31	88	4
32	79	8
33	88	5
34	78	10
35	91	1
36	76	11
37
Rank no ties (2)

Cell Formulas
Range	Formula
AE5:AE37	AE5	=IF(AD5="","",COUNTIF(INDEX(AD$4:AD5,AGGREGATE(14,6,(ROW(AD$4:AD4)-ROW(AD$4)+1)/(AD$4:AD4=""),1)+1):INDEX(AD5:AD$993,MATCH(TRUE,INDEX(AD5:AD$993="",0),0)-1),">"&AD5) +(COUNTIF(INDEX(AD$4:AD5,AGGREGATE(14,6,(ROW(AD$4:AD4)-ROW(AD$4)+1)/(AD$4:AD4=""),1)+1):AD5,AD5)))

Even better. Thank you

 

[Peter_SSs]

Even better. Thank you

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.