RANK.EQ Not Returning Without Duplicate References

[ejackson37]

Hi Everyone,

I am trying to rank teams in a table for a charity sports event.

I want to rank by:
1. Points (PTS)
2. Goal Difference (GD)
3. Goals Scored (GS)
4. If all are tied, revert to Alphabetical order.

However the formula I have put in, does not return the Alphabetical order.

=RANK.EQ([@PTS],[PTS])+COUNTIFS([PTS],[@PTS],[GD],">"&[@GD])+COUNTIFS([GD],[@GD],[GS],">"&[@GS]+COUNTIFS([Team],"<="&[@Team]))

Where the black team and blue team have drawn 1-1 (testing the formula) it returns them both as Rank 2 however I want black to return as 2 and blue as 3.

The reason I'm using a table and Rank.EQ is because I want this to be live update as the event goes on.

Can anyone help please?

Thank you. You're a great community that has always solved issues I have had

 

[Fluff]

Looks like you have a closing bracket in the wrong place.
Try

=RANK.EQ([@PTS],[PTS])+COUNTIFS([PTS],[@PTS],[GD],">"&[@GD])+COUNTIFS([GD],[@GD],[GS],">"&[@GS])+COUNTIFS([Team],"<="&[@Team])

 

[ejackson37]

Looks like you have a closing bracket in the wrong place.
Try

=RANK.EQ([@PTS],[PTS])+COUNTIFS([PTS],[@PTS],[GD],">"&[@GD])+COUNTIFS([GD],[@GD],[GS],">"&[@GS])+COUNTIFS([Team],"<="&[@Team])

Hi,

Thanks for your response. I amended the formula but now instead of ranking 1-4 it now says 7, 5, 3 & 4.

Do you know how to get it to rank 1-4?

Thanks again, appreciate your help

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ejackson37]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi,

I tried downloading it but my IT department blocked downloading add in. Other then using link below to sharing a WeTransfer, not sure how I will be able to send it:

Football Table Charity A.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

Please let me know if you can get a copy of the spreadsheet from the above.

Again thank you for your help, apologies not straight forward

 

[Fluff]

Thanks for that.
If you are happy with a helper column how about

Football Table Charity A.xlsx
	I	J	K	L	M	N	O	P	Q	R	S
10	Team	P	W	D	L	GS	GA	GD	PTS	Helper	Rank
11	Red	1	0	0	1	0	3	-3	0	4443	4
12	White	1	1	0	0	3	0	3	3	1114	1
13	Black	1	0	1	0	1	1	0	1	2221	2
14	Blue	1	0	1	0	1	1	0	1	2222	3
Sheet1

Cell Formulas
Range	Formula
J11:J14	J11	=SUM(K11:M11)
K11	K11	=IF(C11>E11,1,0)
L11	L11	=IF(C11=E11,1,0)
M11	M11	=IF(C11<E11,1,0)
N11	N11	=C11
O11	O11	=E11
P11:P14	P11	=N11-O11
Q11:Q14	Q11	=SUM((K11*3)+(L11*1))
R11:R14	R11	=--(RANK([@PTS],[PTS])&RANK([@GD],[GD])&RANK([@GS],[GS])&COUNTIFS([Team],"<="&[@Team]))
S11:S14	S11	=RANK([@Helper],[Helper],1)
K12	K12	=IF(E11>C11,1,0)
L12	L12	=IF(E11=C11,1,0)
M12	M12	=IF(E11<C11,1,0)
N12	N12	=E11
O12	O12	=C11
K13	K13	=IF(C12>E12,1,0)
L13	L13	=IF(C12=E12,1,0)
M13	M13	=IF(C12<E12,1,0)
N13	N13	=C12
O13	O13	=E12
K14	K14	=IF(E12>C12,1,0)
L14	L14	=IF(E12=C12,1,0)
M14	M14	=IF(E12<C12,1,0)
N14	N14	=E12
O14	O14	=C12

 

[ejackson37]

Thank you! Seems to have solved the issue. Great Community

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ejackson37]

Hi Fluff,

Me again, sorry! I tried applying the logic from the above which resolved my ranking issue for the football table to the "Top Goalscorer" table I have.

I added in helper, to rank. Each person has a rank from 1-80. However my rank is not returning:
1. Goals
2. Alphabetical order

You can see from my attached screenshot and excel linked below that Jack has 5 goals but is not top of the ranking:

Football Table Charity Bxlsx.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

Are you able to help explain where I have gone wrong? I want it to rank based on number of goals first and then alphabetical if the values match.

Thank you again, I'm learning alot but not quite there! Thanks

 

[Fluff]

That will only work for upto 9 players, but you can use this instead

=RANK([@Goals],[Goals])*100+COUNTIFS([Name],"<="&[@Name])

You can also replace the formula in AY:BA with

=SORT(Table9[[Name]:[Goals]],{3,1},{-1,1})