Calculate turns on based on initiative scores

[Kyletok]

Hi,

For the most part I am quite proficient in Excel, but there's something I am trying to achieve but don't exactly know how to and what approach to take.

I am making a D&D sheet for a games night I have next week and I wanted to have a hidden page that calculate's whose turn it is based on their initiative score. For every round completed in the game, the round count is going to be increased by +1 (with macro), so I want the calculator to tell me whose turn it is based on the round count of the game and the player's individual initiative score.

For example: Player A has a score of 5, Player B has a score of 10, Player C also has a score of 10, and Player D has a score of 15.
Round: 1 = Player D
Round: 2 & 3 = Players B and C in a random order since they have the same score
Particularly don't know how to handle this. Maybe choose depending on who's name comes up first, etc/
Round: 4 = Player A
Round: 5 = Player D

Lets say the rounds are just in Cell A2 increasing by 1 every turn.

I had many Idea's on how to do it, but it's when it comes to 2 or more of the same score, I don't know how to get excel to choose that. I noticed that when I use the LARGE function, it actually does take into consideration that there are two 10's and therefore shows 10 for Large(array,3). Is there maybe a way to show the corresponding column for that score? A regular lookup would just pull the first column with 10.

Hope I made sense, thanks in advance for any tips and ideas!

 

[mikerickson]

Does a player's Initiative score change during the course of a round?

If not, before round 1, sort all the players by initiative and then just keep going in that order.

If the score does change in the course of a round, when is that change reflected in the order? Is it
-sort by initiative
every one plays one round, Initiative changing as it goes
resort by the new intiative scores, everyone plays in that new order
Loop

Might it be that a player gets two turns before some other player gets their turn?

 

[Kyletok]

Does a player's Initiative score change during the course of a round?

No, but the thing is I also have a database with NPC "characters" with their own initiative score that can be added throughout the game (I have an easy macro for that). Therefore I want the excel sheet to tell us who's turn it is automatically, one of the players or an NPC.

Might it be that a player gets two turns before some other player gets their turn?

This is something I was very eager to do at first but I just don't know how I would go about letting excel know a player gets two turns this round.
I played around and came up with this so far
[TABLE="width: 695"]
<colgroup><col><col span="4"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]PLAYER A[/TD]
[TD]PLAYER B[/TD]
[TD]PLAYER C[/TD]
[TD]PLAYER D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Initiative[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Divided GCD[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD] =D2/GCD(A2:D2)[/TD]
[/TR]
[TR]
[TD]Sum-Above[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD] =SUM($A$3:$D$3)-D3[/TD]
[/TR]
</tbody>[/TABLE]

Using the red values would mean that if there was a "time count" or hypothetical seconds, Player D gets to play a turn every 5 seconds, while Player A gets to play every 7 seconds. Which means player D gets to play more often and therefore gets more turns.

how do I do all that in excel for it to automatically calculate? I have no idea haha.

 

[Kyletok]

I found a solution but ran into another problem, if I make a table and just put all the scores in them, (for example column one will have 5, 10, 15, 20 - increasing by 5) and then I used:

=SUMPRODUCT((Table=SMALL(Table,Round))*COLUMN(Table))-COLUMN(Table)+1

Table being the array and Round being which round it is, which finds the first, second, third smallest scores etc. This finds the column in which these are in and thats how I can know which player plays next, only problem is that if there are two scores of the same value in the same array, that renders the formula useless.

how can I find out which score Small() is pulling if there are values that are the same