rank for football table

[plumberman]

HI Chaps nice site you have here

I just wondered if you could help me with little problem i have?

I created a automated football league with 5 teams in it that updated the team positions
(moved them up and down)as the new data was added in the secondary table and i used this type of formula in the secondary table on a rank system to rank positions according to points/wins/goal/difference/ect this also places the appropriate teams in alphabetical order in case the teams have equal stats

=RANK(O4,$O$4:$O$13)+RANK(G4,$G$4:$G$13)/10+RANK(N4,$N$4:$N$13)/100+RANK(F4,$F$4:$F$13)/1000+RANK(E4,$E$4:$E$13)/10000+1/100000


and this formula in the primary table that updates its self

=SMALL($A$4:$A$13,1) in the ranking


and this type of formula to transfer the data to the appropriate destination from the secondary table to the self adjusting primary table
ie points/ goals/ect

=INDEX($A$4:$O$13,MATCH($A22,$A$4:$A$13,0),4)


my question is i now want to extend my table to 24 teams so can i still use this ranking system as the range of 10/10000 only seems to let me use 10 teams( not tested yet)not sure how it works


any ideas or help would be appreciated


thx

[Gearcutter]

Hi,
Try this, in a col at the side of the static table use the formula:
=Sum(1st criteria * 10000000, 2nd criteria*10000,3rd criteria*100,100-Row())
Then copy this down your teams and then rank this column.
the last part of the sum formula gives you the alphabetical order.
Regards Howard

[plumberman]

cheers howard thanks for taking the time to help me m8

[Gearcutter]

Hi,
Your welcome
Cheers Howard

[plumberman]

OK I have got my football table table up and running , with 24 teams and the table as 5 ranks in this order

points first, then goal difference, games won, games drawn, goals scored.


the ranking format works great till a team with minus goals lets say -2 goals and lets say 10 points moves above a team in the table with lets say +1 goal and 12 points

the goal difference colum seems to take preference in the table over points scored because it runs into a minus score


any ideas why this is? any help would be appreciated

thx kev

[Gearcutter]

Hi,
post the formula that you have used. The sum formula that I posted should keep the various criteria separate, though I notice you have gone to 4 different criteria, so lets see the sum formula that you have used
regards Howard

[plumberman]

hi Howard this is the type of formula i used to collect and calculate the info from cell to cell on the table


=(I3-J3+N3-O3)


this is also one of the actual ones i used in the one of the cells in the goal difference coll um


To give you an idea how i went about this,

I created three charts one were you enterd the scores and win or lose, goals for, and goals agianst




then the second chart collected those stats and calculated the points as the sum as above, then ranked them using this rank system below in a collum to the left of the chart

=RANK(Q3,$Q$3:$Q$26)+RANK(P3,$P$3:$P$26)/10+RANK(F3,$F$3:$F$26)/100+RANK(G3,$G$3:$G$26)/1000+RANK(I3,$I$3:$I$26)
/10000+1/100000


then the third chart collected them and sorted the teams in the correct decending order in my ranking rank possions using this rank system in a collum to the left of the chart

=SMALL($B$3:$B$26,1)


then in each cell that would need to collect stats in the third chart i used a formula like this to collect the info from the cells in table two

=INDEX($B$3:$Q$26,MATCH($B42,$B$3:$B$26,0),4)



hope understood my explanation attempt lol cheers

[Gearcutter]

Hi,
In my original reply I said:

"Try this, in a col at the side of the static table use the formula:
=Sum(1st criteria * 10000000, 2nd criteria*10000,3rd criteria*100,100-Row())
Then copy this down your teams and then rank this column.
the last part of the sum formula gives you the alphabetical order"

By static table I mean your original league table in which you collate all your stats.
The Sum formula= 1st criteria(Points)*10000000,2ndCriteria(Goal Diff)*10000,3rd Criteria(Gameswon)*100,100-row()
by Multiplying by these amounts it keeps everything separate then you only need to use the rank function once

If you didn't use the above and just used the follwing:


=RANK(Q3,$Q$3:$Q$26)+RANK(P3,$P$3:$P$26)/10+RANK(F3,$F$3:$F$26)/100+RANK(G3,$G$3:$G$26)/1000+RANK(I3,$I$3:$I$26)
/10000+1/100000


then after Rank(Q3,$Q$3:$Q$26)*1000+Rank(... and the rest of formula
that should keep things separate.


Regards Howard

[plumberman]

"Try this, in a col at the side of the static table use the formula:
=Sum(1st criteria * 10000000, 2nd criteria*10000,3rd criteria*100,100-Row())

I think i understand this bit

Then copy this down your teams and then rank this column.
the last part of the sum formula gives you the alphabetical order"

not sure what you meen about this bit and not sure about this bit also

100-Row()) not sure what ths meen,,,, or what i put in it,,, i think you put the row number in the row bit but as for the other brackets at the end then not sure

If you didn't use the above and just used the follwing:


=RANK(Q3,$Q$3:$Q$26)+RANK(P3,$P$3:$P$26)/10+RANK(F3,$F$3:$F$26)/100+RANK(G3,$G$3:$G$26)/1000+RANK(I3,$I$3:$I$26)
/10000+1/100000


then after Rank(Q3,$Q$3:$Q$26)*1000+Rank(... and the rest of formula
that should keep things separate.

i tried this,,,, i think you ment like this like this


=RANK(Q3,$Q$3:$Q$26)*1000+RANK(P3,$P$3:$P$26)/10+RANK(F3,$F$3:$F$26)/100+RANK(G3,$G$3:$G$26)/1000+RANK(I3,$I$3:$I$26)
/10000+1/100000


but it just sent the high teams to the very bottom of the league?


hummm i think a step by step is the only way or if i send you the file m8

thanks for trying howard m8 but this is not my strong point lol

[Gearcutter]

Hi,
I think it would be quicker if you post the file
Regards Howard

[plumberman]

cheers Howard for your help


Well i have got it to work with my original formula i posted above..... the reason that it was returning the top team with most points {first rank} to the bottom of the table when it had a minus value in the goals for and against column which was the {2nd rank},,, is that i had not filled in all the teams cell formulas in the updating table and was just testing it with the top ten filled in as i did not think it would make any difference but of course i now know it does once i had filled in all the cell formula things just worked fine

as of my original question for the formula not letting me use more than 10 teams ......well it does it seems to let you use as many as you want



thanks from this embarrassed noob