Calculate turns on based on initiative scores

Kyletok

New Member
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!

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

mikerickson

MrExcel MVP
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?

Last edited:

Kyletok

New Member
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
 PLAYER A PLAYER B PLAYER C PLAYER D Initiative 5 10 10 15 Divided GCD 1 2 2 3 =D2/GCD(A2:D2) Sum-Above 7 6 6 5 =SUM(\$A\$3:\$D\$3)-D3

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>

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

New Member
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:
Code:
``=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

Replies
2
Views
195
Replies
4
Views
104
Replies
21
Views
632
Replies
4
Views
838
Replies
0
Views
183

Threads
1,195,674
Messages
6,011,098
Members
441,582
Latest member
Topkapi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back