+ Reply to Thread
Results 1 to 5 of 5

Dynamic Excel Function Based on Players Involved and Timing

  1. #1
    Registered User
    Join Date
    11-05-2020
    Location
    New York
    MS-Off Ver
    2016
    Posts
    3

    Dynamic Excel Function Based on Players Involved and Timing

    I'm trying to put together a dynamic function. the attached spreadsheet excerpt has the manual calculation.

    The calculation is dependent on how many players have entered already (e.g., Player 1's Entry assumption is multiplied by 75% in 2020 because it is the only one that has entered, but if things changed and player 2 entered in 2020, then Player 1's entry assumption would then need to be multiplied by the 48% shown in cell D6.

    The only way I can think to make this fully dynamic is an IF function, so =IF(AND(D21>0,D22=0,D23=0), and so on, but there must be a more efficient way to do this?
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,665

    Re: Dynamic Excel Function Based on Players Involved and Timing

    Please try at
    D21
    =D13*INDEX($C6:$I6,MATCH(SUMPRODUCT(2^(ROW($B$13:$B$15)-ROW($B$13))*(D$13:D$15>0)),{7,3,1,5,6,2,4},))

    or rearrange as in Column K4:R8

    M21
    =M13*INDEX($L6:$R6,SUMPRODUCT(2^(ROW($K$13:$K$15)-ROW($K$13))*(M$13:M$15>0)))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-05-2020
    Location
    New York
    MS-Off Ver
    2016
    Posts
    3

    Re: Dynamic Excel Function Based on Players Involved and Timing

    Thanks so much! This works perfectly. Would you be able to explain what this formula is doing? Having trouble wrapping my head around the first version and the second, and what the reordering does.

  4. #4
    Registered User
    Join Date
    11-05-2020
    Location
    New York
    MS-Off Ver
    2016
    Posts
    3

    Re: Dynamic Excel Function Based on Players Involved and Timing

    Bump - could anyone explain this? Need to alter / add a new player, and having some trouble.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,646

    Re: Dynamic Excel Function Based on Players Involved and Timing

    there are 2 questions:
    In real file:
    1) How many players do you have?
    2) Is player "n" real name?
    It is improtant, because the solution must base on how many columns of player combination and their duplicate prefix string "Player"

+ 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. Replies: 19
    Last Post: 02-29-2020, 12:12 PM
  2. [SOLVED] Which function do i have to use to show me the total of players-over a number(s)
    By detektiv in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-18-2018, 08:05 AM
  3. Creating teams from a list of players based on provided preferences
    By MGTobias in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-18-2018, 03:20 PM
  4. [SOLVED] VBA function for score difference among players
    By dsol108 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-15-2014, 09:17 AM
  5. Timing Macro (to calculate the time it takes to complete a function)
    By jaysknots in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-02-2014, 03:11 PM
  6. Cut and Paste Rows based on criteria : Three seperate workbooks involved
    By xiz he in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-01-2014, 05:15 PM
  7. [SOLVED] IF formula to assign players to teams based on age & weight
    By redstripe87 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-22-2013, 08:20 AM

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