Perform function by groups

[mikesal57]

Hi ALL...

How can I do a function (RANK) within groups separated by blank rows?

Date	Post	Track	Racenum	Prg	Horse	ML	cFT	cF4	cHF	cRtg	PSR	CPw		Points	RANK
3/23/19	10:30	AQU	1	1	Blue Pigeon	15	94	24	90	67	78	72		39.67			5.64	-1.2	-3.6	8.71	26.52	3.6
3/23/19	10:30	AQU	1	2	Borsa Vento	4	100	37	101	72	95	97		46.62			6	-1.85	-4.04	9.36	32.3	4.85
3/23/19	10:30	AQU	1	3	Domain	8	101	45	110	74	92	96		45.11			6.06	-2.25	-4.4	9.62	31.28	4.8
3/23/19	10:30	AQU	1	4	Kenyan	15	88	1	94	63	80	85		41.11			5.28	-0.05	-3.76	8.19	27.2	4.25
3/23/19	10:30	AQU	1	5	Rogue Nation	0.6	115	136	110	86	104	108		47.64			6.9	-6.8	-4.4	11.18	35.36	5.4
3/23/19	10:30	AQU	1	6	Orpheus (ARG)	3.5	93	1	98	69	87	90		44.66			5.58	-0.05	-3.92	8.97	29.58	4.5
3/23/19	11:03	AQU	2	1	Claire's Kitty	1.4	81	1	92	62	65	65		34.54			4.86	-0.05	-3.68	8.06	22.1	3.25
3/23/19	11:03	AQU	2	2	Midnight Crossing	10	77	1	95	60	59	61		31.68			4.62	-0.05	-3.8	7.8	20.06	3.05
3/23/19	11:03	AQU	2	3	Midnight Sky	3.5	76	1	93	60	62	62		32.77			4.56	-0.05	-3.72	7.8	21.08	3.1
3/23/19	11:03	AQU	2	4	Third Card Down	8	73	1	89	57	54	53		29.19			4.38	-0.05	-3.56	7.41	18.36	2.65
3/23/19	11:03	AQU	2	5	Dazzling Speed	2.5	71	1	92	61	67	59		34.19			4.26	-0.05	-3.68	7.93	22.78	2.95
3/23/19	11:03	AQU	2	6	Diamond Jen Brady	6	80	1	98	61	62	60		32.84			4.8	-0.05	-3.92	7.93	21.08	3
3/23/19	11:40	AQU	3	1	Judge Stanton	3	66	1	77	55	60	50		30.88			3.96	-0.05	-3.08	7.15	20.4	2.5
3/23/19	11:40	AQU	3	2	Invest	2.5	70	1	76	58	67	76		35.23			4.2	-0.05	-3.04	7.54	22.78	3.8
3/23/19	11:40	AQU	3	3	Golconda	10	74	1	83	60	62	63		33.1			4.44	-0.05	-3.32	7.8	21.08	3.15
3/23/19	11:40	AQU	3	4	Givetheman a Cigar	4.5	86	1	103	67	77	75		39.63			5.16	-0.05	-4.12	8.71	26.18	3.75
3/23/19	11:40	AQU	3	5	Bad Guy	8	85	1	90	68	75	83		39.94			5.1	-0.05	-3.6	8.84	25.5	4.15
3/23/19	11:40	AQU	3	6	Lorcan	6	74	1	83	59	55	47		29.79			4.44	-0.05	-3.32	7.67	18.7	2.35
3/23/19	11:40	AQU	3	7	Turbulence	5	90	27	100	63	64	78		33.9			5.4	-1.35	-4	8.19	21.76	3.9
3/23/19	11:40	AQU	3	8	Scrutinize	20	72	1	84	58	60	62		31.95			4.32	-0.05	-3.36	7.54	20.4	3.1

<colgroup><col span="5"><col><col span="17"></colgroup><tbody>
</tbody>

thank you
mike

 

[James006]

Hi,

In cell P2, you can test the following Array Formula

=IF(O2="","",RANK(O2,INDIRECT("O"&MAX(IF($O$1:O2="",ROW($O$1:O2)))+1):INDIRECT("O"&MATCH(TRUE,ISBLANK(A3:$A$100),0)+ROW()-1)))

Hope this will help

 

[mikesal57]

comes up as #n/a

 

[mikesal57]

Date	Post	Track	Racenum	Prg	Horse	ML	cFT	cF4	cHF	cRtg	PSR	CPw		Points	RANK
3/23/19	10:30	AQU	1	1	Blue Pigeon	15	94	24	90	67	78	72		39.67	#N/A		5.64	-1.2	-3.6	8.71	26.52	3.6
3/23/19	10:30	AQU	1	2	Borsa Vento	4	100	37	101	72	95	97		46.62	#N/A		6	-1.85	-4.04	9.36	32.3	4.85
3/23/19	10:30	AQU	1	3	Domain	8	101	45	110	74	92	96		45.11	#N/A		6.06	-2.25	-4.4	9.62	31.28	4.8
3/23/19	10:30	AQU	1	4	Kenyan	15	88	1	94	63	80	85		41.11	#N/A		5.28	-0.05	-3.76	8.19	27.2	4.25
3/23/19	10:30	AQU	1	5	Rogue Nation	0.6	115	136	110	86	104	108		47.64	#N/A		6.9	-6.8	-4.4	11.18	35.36	5.4
3/23/19	10:30	AQU	1	6	Orpheus (ARG)	3.5	93	1	98	69	87	90		44.66	#N/A		5.58	-0.05	-3.92	8.97	29.58	4.5
3/23/19	11:03	AQU	2	1	Claire's Kitty	1.4	81	1	92	62	65	65		34.54	#N/A		4.86	-0.05	-3.68	8.06	22.1	3.25
3/23/19	11:03	AQU	2	2	Midnight Crossing	10	77	1	95	60	59	61		31.68	#N/A		4.62	-0.05	-3.8	7.8	20.06	3.05
3/23/19	11:03	AQU	2	3	Midnight Sky	3.5	76	1	93	60	62	62		32.77	#N/A		4.56	-0.05	-3.72	7.8	21.08	3.1
3/23/19	11:03	AQU	2	4	Third Card Down	8	73	1	89	57	54	53		29.19	#N/A		4.38	-0.05	-3.56	7.41	18.36	2.65
3/23/19	11:03	AQU	2	5	Dazzling Speed	2.5	71	1	92	61	67	59		34.19	#N/A		4.26	-0.05	-3.68	7.93	22.78	2.95
3/23/19	11:03	AQU	2	6	Diamond Jen Brady	6	80	1	98	61	62	60		32.84	#N/A		4.8	-0.05	-3.92	7.93	21.08	3

<tbody>
</tbody>

 

[mikesal57]

any help here...

 

[James006]

For an Array Formula ...

Instead of the Enter key ...

You need to use simultaneously the three keys : Control Shift Enter

Hope this clarifies

 

[mikesal57]

JAMES006 ...thank you

 

[James006]

You are welcome