Excel - horizontal consecutive ranking in row

[SamJakob]

1st

H

Result

2nd

H

Result

3rd

H

Result

4th

H

Result

5th

H

Result

<<irrelevant>>

D

12.09

<<irrelevant>>

E

12.09

<<irrelevant>>

C

12.10

<<irrelevant>>

B

12.10

<<irrelevant>>

A

12.12

<tbody>
</tbody>

So I have the above table and then I want a consecutive rank for each 'H' value.

For example, at the moment I have

A	B	C	D	E
3	5	3	1	1

<tbody>
</tbody>

What I want is

A	B	C	D	E
2	3	2	1	1

<tbody>
</tbody>

So far I've seen methods of doing this vertically instead of horizontally but I can't manage to adapt them.

Help would be much appreciated.

 

[Eric W]

Welcome to the forum.

I found your description to be a bit confusing. Do you mean that you want a formula in the red cells below, with these values?

1st

H

Result

2nd

H

Result

3rd

H

Result

4th

H

Result

5th

H

Result

<<irrelevant>></irrelevant>

1

12.09

<<irrelevant>></irrelevant>

1

12.09

<<irrelevant>></irrelevant>

2

12.10

<<irrelevant>></irrelevant>

2

12.10

<<irrelevant>></irrelevant>

3

12.12

<tbody>
</tbody>

And you want the formula to be a ranking based on the values in the Result cells, without gaps? (The numbers in the table don't match your specified desired results. If I'm wrong, please explain how you get those values.)

If that is correct, how do you feel about rearranging your data? Most formulas of this sort use COUNTIF over the range somehow, and having the data and the results in the same range would result in a circular reference. If you want to keep it in this arrangement, I'd suggest putting the data values in a helper table somewhere, calculate the results there, then use some kind of LOOKUP to put in the H column.

Let me know if I understand your requirements correctly.

 

[SamJakob]

Sorry, no that's not what I wanted.

I want to have my ranks in a table to the right of that data.

 

[Eric W]

Please show a sample of what you want the results to look like.

 

[SamJakob]

Desired table:

1st

H

Result

2nd

H

Result

3rd

H

Result

4th

H

Result

5th

H

Result

A

B

C

D

E

<<unrequired>>

D

12.09

<<unrequired>>

E

12.09

<<unrequired>>

C

12.10

<<unrequired>>

B

12.10

<<unrequired>>

A

12.12

3

2

2

1

1

<tbody>
</tbody>

Also, sorry about the desired results I put in the first post, it seems I've confused myself there, it should have been

D - 1
E - 1
B - 2
C - 2
A - 3

(as shown in the Desired table)

 

[Eric W]

Try this:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	1st	H	Result	2nd	H	Result	3rd	H	Result	4th	H	Result	5th	H	Result		A	B	C	D	E
2	<>	D	12.09	<>	E	12.09	<>	C	12.1	<>	B	12.1	<>	A	12.12		3	2	2	1	1

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula Q2 {=SUM(IF(ISNUMBER($A$2:$O$2),IF($A$2:$O$2<INDEX($A$2:$O$2,MATCH(Q1,$A$2:$O$2,0)+1),1/COUNTIF($A$2:$O$2,$A$2:$O$2))))+1} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I'm a little puzzled why B2 is D and E2 is E, but it doesn't matter. This formula checks the header in Q1:U1 for the letter, and ranks the value it finds after that letter.

Let me know if this works for you.

 

[SamJakob]

It is not working for me.

The formula appears to have a syntax error in it:
2),IF($A$2:$O$2[should there be something here?]<font color="green" style="background-color: rgb(250, 250, 250);"><index(<font color="purple">$A$2:$O$2,MATC</index(

 

[Eric W]

Arrgh! Even after all this time, I sometimes forget that the bulletin board software here can interpret a < as an HTML tag instead of part of a formula. To avoid that, one should put a space after it. See if this works:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	1st	H	Result	2nd	H	Result	3rd	H	Result	4th	H	Result	5th	H	Result		A	B	C	D	E
2	<>	D	12.09	<>	E	12.09	<>	C	12.1	<>	B	12.1	<>	A	12.12		3	2	2	1	1

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula Q2 {=SUM(IF(ISNUMBER($A$2:$O$2),IF($A$2:$O$2< INDEX($A$2:$O$2,MATCH(Q1,$A$2:$O$2,0)+1),1/COUNTIF($A$2:$O$2,$A$2:$O$2))))+1} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[SamJakob]

Yes!!! You sir are the real Excel MVP!

 

[Eric W]

Whew! I'm glad we finally found something that works for you!