+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 28 of 28

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

  1. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,342

    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)),"-")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  2. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,342

    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. #18
    Registered User
    Join Date
    08-18-2013
    Location
    Port Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    34

    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. #19
    Registered User
    Join Date
    08-18-2013
    Location
    Port Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    34

    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. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,342

    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. #21
    Registered User
    Join Date
    08-18-2013
    Location
    Port Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    34

    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. #22
    Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    131

    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.
    Attached Files Attached Files

  8. #23
    Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    131

    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. #24
    Registered User
    Join Date
    08-18-2013
    Location
    Port Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    34

    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.

  10. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,342

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

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  11. #26
    Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    131

    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.
    Attached Files Attached Files

  12. #27
    Registered User
    Join Date
    08-18-2013
    Location
    Port Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    34

    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. #28
    Registered User
    Join Date
    08-18-2013
    Location
    Port Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    34

    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.

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

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

Bookmarks

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