Trying to produce a table of results based on certain conditions

[tom hatten]

Hello.
The attached file shows some results from the 2021/2022 English Premier League season.

Going based on 3 points for a win, 1 point for a draw and 0 points for a loss, I am trying to create a rating system with the following conditions.
As well as a team gaining 3 points for a win, they will also gain the points won so far by the opposing team.
A match that is drawn will mean both teams earning 1 point plus half the points won so far by the opposing team.

The file shows the table and points won by each club in columns A and B. Columns E and F show goals for and against.
In column I, at the start of the second set of fixtures, I have included an explanation of how the points are won. This can be deleted.

With a table of teams all starting on 0 points, I would like a sheet where I enter the fixtures and for each upcoming match with a table that is automatically updated after entering the results.

Thank you.

https://docs.google.com/spreadsheets...it?usp=sharing

[janmorris]

can you give better explanation of how the points are earned from beating a team that won before?

in some parts you say 1.5 points from 3 already earned (I14, I15, I18), but then you change to 3 points for 3 earned (I18), then in I24 Everton got 6 points from Brightons previous when in fact Brightons previous total is 9.

It may be better if you first broke the amounts into separate columns so that it visually matches the text notes.

If you can give Editor access for Anyone with the link then it will be much easier to develop a solution.

In lieu of Editor access, I have taken the liberty to make a copy of the sheet, and according to the text you have written, G18 is not correct, and by extension, H25 is also not correct.

[tom hatten]

A team will gain 3 pts for a win and also win the points so far won by the appossing team.
So, if Leicester and Wolves play each other and both teams have so far gained 12 points, if Leicester win, they get 3 pts for the win plus the points Wolves have won so far (12) So Leicester win a total of 15 pts.
If the match is drawn, both teams win 1 point for draw plus 6 points (half the points won so far by each team)


https://docs.google.com/spreadsheets...it?usp=sharing

Thank you for your help.

[tom hatten]

I hope this file is ok?

[tom hatten]

can you give better explanation of how the points are earned from beating a team that won before?

in some parts you say 1.5 points from 3 already earned (I14, I15, I18), but then you change to 3 points for 3 earned (I18), then in I24 Everton got 6 points from Brightons previous when in fact Brightons previous total is 9.

It may be better if you first broke the amounts into separate columns so that it visually matches the text notes.

If you can give Editor access for Anyone with the link then it will be much easier to develop a solution.

In lieu of Editor access, I have taken the liberty to make a copy of the sheet, and according to the text you have written, G18 is not correct, and by extension, H25 is also not correct.

Thank you janmorris for pointing out my mistakes. I've rectified those errors and I think all explanations are correct now.
I also include a link which I believe you can now edit.
Thank you.

https://docs.google.com/spreadsheets...it?usp=sharing

[janmorris]

Hi Tom,

Just an update, I'm working on solution, so far ive got it about half done.

I hope to have it complete within a couple of days (im working remote so i have to fit it in with internet availability)

[janmorris]

hey tom, ive got solution for you, its not all arrayformulas, but the formulas that are used are quite easy to implement.

the first step is adding in a few extra columns, to split up the different things you need calculated.

where on your example you had everything added in a single column, making it impossible to diagnose any calculation issues, i have added 3 more columns to each team, so now you calculate the 3 things you want.. separately!

now heres the formulas ive used..

in G2 (arrayformula)

Please Login or Register  to view this content.

in K2 (Arrayformula)

Please Login or Register  to view this content.

in H12 (and dragged down)

Please Login or Register  to view this content.

in I12 (and dragged down)

Please Login or Register  to view this content.

in L12 (and dragged down)

Please Login or Register  to view this content.

in M12 (and dragged down)

Please Login or Register  to view this content.

then in J2 (and dragged down)

Please Login or Register  to view this content.

and in N2 (and dragged down)

Please Login or Register  to view this content.

heres sample file of the final solution:

https://docs.google.com/spreadsheets...gid=2100307022

[tom hatten]

Thank you, janmorris. First, please let me apologise for not replying sooner. I really appreciate the time you’ve taken to help me.
It’s just after 1am here in Bournemouth so I’ll take a closer look at your solution some time later in the day and get back to you if I have any problems.
Kind regards,
Tom.