# How To Calculate Running/Ongoing Value When More Data Is Added?

1. ## Re: How To Calculate Running/Ongoing Value When More Data Is Added?

I assume the last two matches for any team wiill be one home and one away fixture?

See if this works for home GD:

=IFERROR((LOOKUP(2,1/(C\$1:C21=C22),E\$1:E21)-LOOKUP(2,1/(C\$1:C21=C22),F\$1:F21))+(LOOKUP(2,1/(D\$1:D21=C22),F\$1:F21)-LOOKUP(2,1/(D\$1:D21=C22),E\$1:E21)),"-")

2. ## Re: How To Calculate Running/Ongoing Value When More Data Is Added?

And this for away:

=IFERROR((LOOKUP(2,1/(C\$1:C1=D2),E\$1:E1)-LOOKUP(2,1/(C\$1:C1=D2),F\$1:F1))+(LOOKUP(2,1/(D\$1:D1=D2),F\$1:F1)-LOOKUP(2,1/(D\$1:D1=D2),E\$1:E1)),"-")

3. ## Re: How To Calculate Running/Ongoing Value When More Data Is Added?

The home one looks correct - as far as producing the correct result (tried it down a few teams) - but the away one is not playing well for some reason.

Just to answer your other point above, teams will not necessarily alternate from home and away, they could play consecutive games home or away and not sequentially. I want to capture any occurrence of a team playing.

Thanks so far - great work.

4. ## Re: How To Calculate Running/Ongoing Value When More Data Is Added?

Amended the away formula to:

=IFERROR((LOOKUP(2,1/(C\$1:C21=D22),E\$1:E21)-LOOKUP(2,1/(C\$1:C21=D22),F\$1:F21))+(LOOKUP(2,1/(D\$1:D21=D22),F\$1:F21)-LOOKUP(2,1/(D\$1:D21=D22),E\$1:E21)),"-")

Seems to be working now. On my way from work, so I will give it a full bash soon and report back.

5. ## Re: How To Calculate Running/Ongoing Value When More Data Is Added?

OK, well you'll need to work on it a bit more, then. Hopefully I've pointed you in the right direction. Let us know how you get on and if you need any further pointers.

6. ## Re: How To Calculate Running/Ongoing Value When More Data Is Added?

Still haven't fully tested yet but I will do when I'm home. My other concern though is the lookups are going to spot the first occurrence and no more so when I increase the number of matches which include multiple home and away and not sequential, I am going to hit a brick wall.

I think some sort of combination of Greg's effort (sumifs based on match day column) and yours Ali is the solution just waiting to be discovered!

7. ## Re: How To Calculate Running/Ongoing Value When More Data Is Added?

The attached file works, as best as I can tell. Again, it is not the elegant solution, but it works. I haven't looked at the formula above to see if I can make it easier yet.

First, I put it in a table. Then I added two columns which count the number of times a team shows up up until that game.

Now it doesn't matter if they play every Match day or not. They can skip and it still works.

When I get a chance to look at the above formula, I will see if I can create a formula without having to add the two columns (although the 2 additional columns don't hurt anything and you could even hide those columns as they are automatically populated because of being in the table.

8. ## Re: How To Calculate Running/Ongoing Value When More Data Is Added?

By the way, my solution does not care if the teams are playing home or away in any sequence (they can play 2 home games, then 3 away games and it should still work). (An example to test is for the GD for Carpi's 4 Match Day. Carpi plays as the Away team for both match day's 2 and 3, giving a GD of -2 for Match day 4.)

9. ## Re: How To Calculate Running/Ongoing Value When More Data Is Added?

Just had a quick glance there Greg and it seems to be working. I will test it later for real but looks like excellent work.

The bigger challenge will be increasing the number of matches and allowing the goal difference to be calculated over 32 matches. But I figure this will be an easy adjustment based on your formula above.

Superb work Greg.

11. ## Re: How To Calculate Running/Ongoing Value When More Data Is Added?

I've attached a version where at the top of the table, I have a place where you can enter any number you want as far as how many matches you'd like to go back.

So just change that number and all the calculations change appropriately.

12. ## Re: How To Calculate Running/Ongoing Value When More Data Is Added?

Utterly superb work there Greg. I am going to give it a true test at the weekend. The overall aim is to use a rolling 32 matches to gather the required GD figure. This will present a further problem down the line when teams who were promoted from a division below or relegated from a division above (where applicable) have their GD calculated using games from a different division.

One step at a time though and from an idea that has been swimming around in my head for a long time, giant leaps have been taken with this forum's help.

13. ## Re: How To Calculate Running/Ongoing Value When More Data Is Added?

Just out of interest, when you change the figure at the top to change the number of matches - looking at the results, I presume it still works out the figures for every number of matches below that figure? Otherwise, if I set the figure to 10, I was half expecting the GD figures to be 0 until 10 matches were reached.

Page 2 of 2 First 1 2

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1