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

 

[louisH]

Hi,

I'm not sure i understood your problem. (what do you mean by "rank")

Try this in B1 & copy down :

=COUNTBLANK($A$1:$A1)

 

[BakerBaker]

RANK as in sort to order.
A B
1 22 =RANK(A1,$A$1:$A$4,0) (Result is 2)
2 46 =RANK(A2,$A$1:$A$4,0) (Result is 1)
3 10 =RANK(A3,$A$1:$A$4,0) (Result is 3)
4 8 =RANK(A4,$A$1:$A$4,0) (Result is 4)
5 BLANK
6 22 =RANK(A6,$A$6:$A$11,0) (Result is 2)
7 12 =RANK(A7,$A$6:$A$11,0) (Result is 5)
8 43 =RANK(A8,$A$6:$A$11,0) (Result is 1)
9 2 =RANK(A9,$A$6:$A$11,0) (Result is 6)
10 19 =RANK(A10,$A$6:$A$11,0) (Result is 3)
11 18 =RANK(A11,$A$6:$A$11,0) (Result is 4)
12 BLANK

I do not want to have to physically change the formula after each blank row. I have seen a formula online that can be copied down the column that will do this - unfortunately cannot find it again.

 

[Peter_SSs]

Leave a blank cell above the first value, then use this formula, copied down.

Excel Workbook
	A	B
1
2	22	2
3	46	1
4	10	3
5	8	4
6
7	22	2
8	12	5
9	43	1
10	2	6
11	19	3
12	18	4
13
14	5	2
15	6	1
16
Rank

 

[BakerBaker]

Absolutely excellent! Thanks for the time spent looking into this. Will save me a LOT of time

 

[Peter_SSs]

Absolutely excellent! Thanks for the time spent looking into this. Will save me a LOT of time

Cheers, glad to help. Thanks for letting us know.

 

[Dryver14]

Peter, that's brilliant It's gone into my formulas folder.

I assume its the latter part of the formula that determines the range to work on.

I tried it with min and max it works (and |I know you didn't design it for this so it's not failing), in these instances
it puts the min or max in every cell in the group, is there a way to avoid this or would it be a completely different formula.

If completely different, don't worry, it's just a learning exercise.

 

[Peter_SSs]

.. it puts the min or max in every cell in the group, is there a way to avoid this ...

There would be, but if you don't want the min (or max) in every cell in the group, you would need to specify where do you want it?

 

[Dryver14]

I was thinking the first next to each group

 

[Peter_SSs]

I was thinking the first next to each group

It's simpler than the ranking question.

Excel Workbook
	A	B
1
2	22	8
3	46
4	10
5	8
6
7	22	2
8	12
9	43
10	2
11	19
12	18
13
14	5	5
15	6
16
Min