# Dynamic Excel Function Based on Players Involved and Timing

1. ## 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?  Register To Reply

2. ## Re: Dynamic Excel Function Based on Players Involved and Timing

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)))  Register To Reply

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.  Register To Reply

4. ## 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.  Register To Reply

5. ## 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"  Register To Reply