I need a formula/formulas for creating charts/ladders in Microsoft Excel.

[Maas]

Since I was about 10 years old I've enjoyed doing tournaments for every possible sport or activity that I've been doing. Dart, hockey, computergames, everything.
So, lately I've been trying to develop my tournaments further by adding statistics to it etc. Let's get to it.

8Thkg.png

Okay, what you see here is Group A in one of my FIFA 12 tournament for PC. I'm playing it with 3 friends and as you can see there's quite alot of matches per group in this one. What I did to simplify this was to add forumlas like "=MID(A6;1;200)" in every cell that Flamengo is about to play their home or away game. By filling in these formulas in every cell I can simply fill in my teams in the group, and all matches in this tournament will be filled in by itself, awesome!

However, what I'm struggling with is that when I get to the end of a groupstage I obviously want to have a quick look of how the teams standings are. Which team is at the top of the ladder? Do I have to win this game or will I be knocked out of the tournament?

What I've been doing with my smaller tournaments is that I've just calculated it in my head and filled in the information, like this:

KbCXv.png

So, what I'm asking is if there is a way to fill the ladder automaticly when writing in the result of a game.
This is what the letters stand for:

W = Win
/ = Draw
L = Loss
+/- = Goal differential
Points = 3 points per win, and 1 point per draw.

Would really appreciate any help here because without formulas this is gonna be one hell of a brain ache when we get to the end of the groupstages!

Thank you
Marcus

[Maas]

Bring up my post!

[Maas]

Bump again!

[Maas]

Bump again.

[Maas]

Bump again

[Maas]

Bump again

[Kevin UK]

Try uploading a a spreadsheet with some dummy data. Someone might reply

[Andy Pope]

This should get you moving forward.

I have added a bunch of helper cells to calculate the points and goal differences for each game. Those cells in mustard colour.
The final dynmaic table is in green.

I use named ranges to hopefully make it clear what the formula are doing.
Only really tricky part is the addition of a same value to the calculated points in order to sort out tied rankings.

[Maas]

Wow, I can't explain how much I appreciate your work. These formulas makes little sense to me so I will have to spend hours if not days to figure out what you've done here, I will reply to this thread again when I've figued it out. Thank you alot for spending your time on this!

[Maas]

FIFA-12 CUP maas,krille,medli,bunken 4 - Kopia.xlsx

Here is my excel document of the tournament.
I've tried to paste your formulas into my own .xlsx document, editing all formulas to the correct cells for my document. Though to me it seems that I've done everything right, I don't seem to understand how to make it work.

stupid.jpg

Here is a picture of the error lines that I get.

How do I fix this, and when it's fixed, will I be able to copy paste this to Group B-H without having to redo it all?

Thank you Andy Pope!

EDIT: I probably know what is wrong, when I click one of the forumulas where it says (HOMETEAM) in blue text, for example, a border shows around the table but it is misplaced. I don't know how to move it.

[Maas]

I hope you will read this again Andy Pope, I'm so close yet so far away. Been searching internet all day trying to figure out how to edit the area of which your formulas cover (HOMETEAM, AWAYPOINTS, SUMPRODUCT etc). I just can't figure it out.

Edit: Thank you again.

[Maas]

Bump thanks

[Andy Pope]

In xl2003 you can use menu
Insert > Name > Define.

To see list of named ranges.

[Maas]

Thanks to you Andy Pope and Vlady this has been solved. Group A is finished but I'm sure I'll be able to copy paste everything to Group B-H now when I know how it works. Thank you for coming up with these formulas they are truly amazing. To be honest I did not even know it was possible at first. Again, Thank you!

Final product (only Group A)

FIFA-12 CUP maas,krille,medli,bunken 4.xlsx