How to change the 'REF' of a RANK formula based on a value in a cell

[mattadams84]

I have a long list of data in rows on one sheet. What i want to do is create a rank within those rows on blocks of data, depending on a unique identifier in column A:

So, i set up the following formula (as an example):

=RANK.EQ(e2,$e$2:$e$20,0)

This will indeed rank what ever is in between e2 and e20. However i want to be able to change the 'REF' array part of the rank formula to be dynamic. In column A there is unique identifier for each block of data, so for example in rows 2-20 there is a numerical identifier, lets say 0001 and then from rows 21-25 its 0002 and so on. For info the identifiers arent incremental they are random, sometimes it can pass from 4520 to 6020 for example, The number of rows in the blocks of data always changes, and there are roughly 900 rows.

Is it possible within my formula to make the reference of where it is trying to rank dynamic, based on the cell value of what is in column A? So i guess it would need to find on which row the unique identifier in column A starts and the row it finishes.

Any help is appreciated

 

[BSALV]

you can work with offset in your formula to change your ref, but i don't understand how it works now.
You point to a cell in column A and then excel has to understand that 4520 means range ("E...:E...").
Can you describe the logic/relation between that cell and the range ?

 

[mattadams84]

Here is an example sheet of my data:

id	data 1	data 2	data 3	rank data 1	rank data 2	rank data 3
6019	68	56	31
6019	445	38	18
6019	443	39	22
6019	442	53	33
6019	480	49	27
6019	440	36	24
6019	57	38	23
6019	441	37	19
6019	56	40	26
6019	449	42	23
6019	435	41	24
6019	491	35	20
6019	451	31	18
6019	444	31	15
6019	490	27	14
6019	486	25	12
6019	434	28	17
6019	448	22	11
6019	438	25	15
6019	433	38	20
6135	93	68	40
6135	151	71	37
6135	152	53	27
6135	59	41	22
6135	149	45	21
6135	153	46	25
6135	92	40	23
6135	223	24	10
6135	146	34	18
6135	143	39	22
6135	158	37	24
6135	108	40	22
6135	209	26	10
6135	157	30	20
6135	218	30	14
6135	222	29	15
6135	144	28	18
2250	145	22	10
2250	155	27	14
2250	159	16	9
6192	38	76	38
6192	33	64	39
6192	40	64	33
6192	49	48	27
6192	46	51	35
6192	42	40	21
6192	541	32	17
6192	41	36	20
6192	34	35	24
6192	44	37	14
6192	540	27	19
6192	43	27	14
6192	39	34	21
6192	50	27	17
6192	543	22	12
6192	47	26	17
6192	51	31	22
6192	544	23	12
6198	461	54	22
6198	470	60	35
6198	74	49	23
6198	90	42	18
4222	464	50	24
4222	113	46	20
4222	463	57	32
3000	471	46	31
3000	473	53	30
3000	469	49	19

 

[mattadams84]

Basically the logic is (using the example above) i would like to rank, for example, data 1, but rank the data (between themselves) on everything that has the ID 6019 (col A) so the range to rank would be all the rows that have 6019 in column A. If i then wanted to rank everything that has the ID 6198 it would rank (between themselves) everything that has the ID 6198.

In reality i have about 900 rows, and the ID's change at random points, they are always grouped, as in they are in successive rows because i sort by ID, so the range would always be a block

 

[BSALV]

every block is unique, means that you can repeat 6019 later then row 21 a 2nd time !!!
with conditional format : green is a new value, red is a new but not unique value

Map1
	A	B	C	D	E	F	G
1	d	data 1	data 2	data 3	rank data 1	rank data 2	rank data 3
2	6019	68	56	31	18	1	2
3	6019	445	38	18	8	8	13
4	6019	443	39	22	10	7	9
5	6019	442	53	33	11	2	1
6	6019	480	49	27	4	3	3
7	6019	440	36	24	13	12	5
8	6019	57	38	23	19	8	7
9	6019	441	37	19	12	11	12
10	6019	56	40	26	20	6	4
11	6019	449	42	23	6	4	7
12	6019	435	41	24	15	5	5
13	6019	491	35	20	1	13	10
14	6019	451	31	18	5	14	13
15	6019	444	31	15	9	14	16
16	6019	490	27	14	2	17	18
17	6019	486	25	12	3	18	19
18	6019	434	28	17	16	16	15
19	6019	448	22	11	7	20	20
20	6019	438	25	15	14	18	16
21	6019	433	38	20	17	8	10
22	6135	93	68	40	15	2	1
23	6135	151	71	37	9	1	2
24	6135	152	53	27	8	3	3
25	6135	59	41	22	17	6	7
26	6135	149	45	21	10	5	10
27	6135	153	46	25	7	4	4
28	6135	92	40	23	16	7	6
29	6135	223	24	10	1	17	16
30	6135	146	34	18	11	11	12
31	6135	143	39	22	13	9	7
32	6135	158	37	24	5	10	5
33	6135	108	40	22	14	7	7
34	6135	209	26	10	4	16	16
35	6135	157	30	20	6	12	11
36	6135	218	30	14	3	12	15
37	6135	222	29	15	2	14	14
38	6135	144	28	18	12	15	12
39	2250	145	22	10	3	2	2
40	2250	155	27	14	2	1	1
41	2250	159	16	9	1	3	3
42	6192	38	76	38	16	1	2
43	6192	33	64	39	18	2	1
44	6192	40	64	33	14	2	4
45	6192	49	48	27	7	5	5
46	6192	46	51	35	9	4	3
47	6192	42	40	21	12	6	8
48	6192	541	32	17	3	11	12
49	6192	41	36	20	13	8	10
50	6192	34	35	24	17	9	6
51	6192	44	37	14	10	7	15
52	6192	540	27	19	4	13	11
53	6192	43	27	14	11	13	15
54	6192	39	34	21	15	10	8
55	6192	50	27	17	6	13	12
56	6192	543	22	12	2	18	17
57	6192	47	26	17	8	16	12
58	6192	51	31	22	5	12	7
59	6192	544	23	12	1	17	17
60	6198	461	54	22	2	2	3
61	6198	470	60	35	1	1	1
62	6198	74	49	23	4	3	2
63	6198	90	42	18	3	4	4
64	4222	464	50	24	1	2	2
65	4222	113	46	20	3	3	3
66	4222	463	57	32	2	1	1
67	3000	471	46	31	2	3	1
68	3000	473	53	30	1	1	2
69	3000	469	49	19	3	2	3
Blad2

Cell Formulas
Range	Formula
E2:E69	E2	=RANK.EQ(B2,OFFSET(B$1,MATCH($A2,$A$1:$A2,0)-1,,COUNTIF($A$2:$A$1000,$A2)),0)
F2:G69	F2	=RANK(C2,OFFSET(C$1,MATCH($A2,$A$1:$A2,0)-1,,COUNTIF($A$2:$A$69,$A2)),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A100	Expression	=EN($A2<>A1;AANTAL.ALS($A$1:$A2;$A2)>1)	text	NO
A2:A100	Expression	=EN($A2<>A1)	text	NO

 

[mattadams84]

Perfect !

Thanks !

 

[BSALV]

i see now that above columns E is "Rank.Eq" and F:G is "Rank", easy to adapt yourself