+ Reply to Thread
Results 1 to 3 of 3

Calculating League Table (Dynamic Arrays)

  1. #1
    Forum Contributor
    Join Date
    04-23-2014
    Location
    United Kingdom
    MS-Off Ver
    Microsoft 365
    Posts
    149

    Question Calculating League Table (Dynamic Arrays)

    I'm having trouble getting my league table to calculate based on x number of games. I want to come up with a solution that is based in one cell as much as possible — making use of the new dynamic array functions. Please don't suggest using a pivot table, Power Query or helper columns.

    You'll see all the matches are stored in the 'Database' worksheet. The teams are featured in 'Teams' and the seasons they have participated in are represented by a 1. You'll notice there are a number of tables in the 'Table' worksheet. These are just from where I've experimented with formulas to try and gain an inkling of how to go about solving this problem.

    So let me just explain the breakdown of what needs to be included when querying the table in the 'Database' worksheet:

    • Only played games — represented by a 1.
    • Only games from the season selected in cell H2 in the ‘Table’ worksheet.
    • Only games that have a date that is less than or equal to the nth smallest date. This is based on the k number specified in cell F2. For example, if this value is 10, then the first 10 games should be considered. Remember: this includes home*AND*away games, so the formula has to look at both columns. I know I have used COUNTIFS and SUMIFS, but they don't work with this scenario, as the range can only be a single column. I have tried conditional SUMPRODUCT and AGGREGATE alternatives, however, they do not seem to handle spilled ranges like 'Teams!B2#'.

    If anyone has any possible workarounds for this it would be much appreciated.

    Please see the attached workbook.
    Attached Files Attached Files
    Last edited by Statto; 01-02-2020 at 06:42 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: Calculating League Table (Dynamic Arrays)

    Re-post your template with examples of what and where and what the results you are expecting should be, highlight those areas with coloured cells.
    Uploading an attachment will assist Forum Members in finding the correct Solution for you. Create a dummy worksheet if necessary, add your start data and show where / what your expected results should be. TO ADD A FILE:

    Go to 'Advanced. click on 'Manage Attachments. Select 'Browse & Choose your file, Select 'Upload and then close the Attachment window.

  3. #3
    Forum Contributor
    Join Date
    04-23-2014
    Location
    United Kingdom
    MS-Off Ver
    Microsoft 365
    Posts
    149

    Re: Calculating League Table (Dynamic Arrays)

    Sure. I've reattached an updated version containing the original tables in yellow. On the right there's a preparation table that is unordered, and the ordered table is on the left. I don't need it fully replicated for now because that would be one huge formula — I would be happy with just the 'Team', 'Played' and 'Points' fields contained in 'Table 1' within cell B3.

    Having played around with it quite a bit, so far I've had no success getting spilled ranges to work with the AGGREGATE, SUMPRODUCT or SMALL functions. This has made the desired solution difficult.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating Totals from dynamic table
    By sohshak in forum Excel General
    Replies: 6
    Last Post: 04-15-2018, 06:54 PM
  2. [SOLVED] Automatic League table (Not calculating Correctly)
    By pmw19800 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-22-2018, 09:46 AM
  3. [SOLVED] Golf League - problem in calculating values
    By Nobbie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-16-2017, 02:43 PM
  4. Dynamic table arrays
    By MrGawain in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-24-2015, 01:50 AM
  5. Calculating Dynamic Table Value
    By roki4 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2012, 02:43 PM
  6. Replies: 0
    Last Post: 11-29-2009, 08:41 AM
  7. [SOLVED] Create Formula for calculating Little League Age...
    By Brent in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-20-2005, 09:05 PM

Tags for this Thread

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