Hi, How do you populate a chart from other cells

[Arrownight]

Hi I'm a rank amateur and I'm trying to work on something for leisure. its a Rifts Initiative chart for my role playing game.

What Id like it to do:
it takes the dice roll of the initiative (1-32)
the divides that by the number of attacks (1-15)
to get the exact initiative segment that the character acts on

For instance if the player rolls a 20 initiative, and has 5 attacks,
then he acts on segments 20,16,12,8, and 4

I have managed to stumble my way into getting excel to show me the segments,

I guess my question is "How do I move the segment numbers as the come up (20,16,etc) to a chart underneath the same number? (leaving the box blank if there is no segment number)

how to I make a chart with /name/initiative/attacks/and the numbers counting down 32, 31, 30, 29, etc
And get the cell to auto-populate and "X" or color mark in the characters active segments and leave the rest blank.

Thank you for any advice you can provide, I don't know if this may be extreme for some, but it passed my ape brain long ago.

Can anyone help?

 

[SteveO59L]

Might be worthwhile posting some sample data, and what you would like to do with it

 

[Arrownight]

no prob, thanks for answering

how do i go from this
Starting (i have figured this part out already)



to make it look like this


Thank you in advance for any help you can provide.

​

 

[SteveO59L]

I think you have forgotten the data

 

[Arrownight]

Sorry, its late and I should be asleep Ok this is what I would like to do.

Have a name column, initiative column, and attacks column

the name is for the player, initiative takes any number from 1-32 (what the player rolled on the dice) and divides it by his number of attacks (1-15)

i.e. initiative 20, with 5 attacks = 4. (20/5=4)

Then I would like to Multiply the number by each attack in progression (1,2,3,4,5-up to the characters total attacks)-to get the segments.

i.e continuing with the above example (initiative 20, 5 attacks),
the character would act on segments 4,8,12,16 and 20

Then I would like to make a chart ranging in descending order from 32 to 1
with name/initiative/attacks 32 , 31, 30, 29, 28, etc

Once the initiative and attack numbers are plugged in, and the segments found, I would like the Actual numbers of that characters segment to go over and populate or place and "X" on the chart underneath the same number on the chart.

i.e. continuing - the Character acts on segments 4,8,12,16,20 - now the
chart lists in descending order 32, 31, 30, etc - Placing an "X" under 20, then 16, 12, 8, and 4, and leaving the rest blank.

Its purpose is so that I can have a fast way of determining a true, "fun" more cinematic initiative for game play, enabling me to plug in multiple character and monster names, their initiative rolls, and attacks that round,
and be left with a final master chart with names on the left/counting down from 32-1 on the top and marking X's at each person/monster's segment.

Whole numbers work the best, and I would not like to show any non-segments or when attacks go into the negative (i.e. initiative 5 with 7 attacks)

As for data, if still needed try the following
initiative 20, 7 attacks
initiative 15, 3 attacks

I'm trying to be as clear as possible, thanks for your patience and interest. its 4am and I have to work tommorrow. I think im going to head to bed.

I got the Idea to try to do it in excel from these housemade rifts rules (perhaps they better explain what I'm trying to do)

[SIZE=+2]Advanced Initiative System Rules[/SIZE]
By: Wayne Braden II (dracox@globaldsl.net) and Dustin M. Ramsey (DustinM@rifts-rpg.com)
Based on Concepts and Game Design by: Kevin Siembieda and Palladium Books (http://www.palladiumbooks.com)

<hr> The biggest advantage to this system is that with it, initiatives actually count for something! Another nice feature of this advanced system is that it helps to keep everyone's attacks distributed throughout the melee (as opposed to the existing Palladium system where everyone acts on the first few attacks of the melee, but then towards the end of the melee, a few characters [usually in power armour] keep attacking when everyone else has run out of attacks).
We have been using this initiative system for several years now, and all of our fellow gamemasters and players love it! If you have any questions about this system, or if you would like me to explain the details of any part of the system, please do not hesitate to contact either of us!

1. Roll initiative as usual on 1D20.
2. Add any applicable initiative modifiers.
3. Divide the total initiative number by the character's number of attacks. The resulting number indicates how many initiative numbers or segments will pass between the character's actions.

Example: A character with 3 attacks per melee rolls a 14 for their initiative and has a +1 bonus. 15 divided by 3 is 5, so the character goes on initiative segments 15, 10, and 5 (or every five actions, counting down from their initiative roll).​

If the number rolled is less than or equal to the number of attacks, then the character acts on every segment from one on up to their initiative number.

Example: A character has 5 attacks per melee, and rolls an initiative of 5, so she would act on initiatives 5, 4, 3, 2, and 1. Even if she rolled a 3, she would still act on those same numbers, as she could never act later than initiative 5.​

If the initiative number rolled (plus modifiers) is not equally divisible by the number of actions, then round the individual initiative segment numbers down to the nearest whole number.

Example: an initiative of 17 divided by 3 attacks is 5.666, so the character would act on initiative segments 17, 11.33, and 5.666. After rounding the individual segment numbers down, the character would act on segments 17, 11, and 5.​

6. After all initiatives have been determined for both the players and NPC's, the GM starts counting down from the highest initiative to the lowest initiative, with the players acting on each of their initiative numbers. If a dodge is used, then the character's next initiative segment is marked off.

Example:
Johann the Cyber-Knight and group leader, has four attacks per melee and an initiative of 12 (he rolled an 11, and has a +1 bonus). Dividing his initiative roll by his number of attacks equals three, so we know that Johann will act on initiative segments 12, 9, 6, and 3. Illyana the Shifter has two attacks and rolls a natural twenty initiative! By dividing her initiative by her number of attacks, we see that she will act on initiative segments 20 and 10.
Victor, the CS Military Specialist in SAMAS power armour, rolls an 18 initiative (with bonuses) and has five attacks per melee in his power armour. In dividing his initiative by his number of attacks, we know that he will act every 3.6 attacks. This means that Victor's attacks are on segments 18, 14, 10, 7, and 3.
Char, a Great Horned Dragon Hatchling, has three attacks and rolls an initiative of 1. As his initiative is lower than his number of attacks per melee, we automatically know that Char will act on every initiative segment from one up to his initiative roll: 3, 2, and 1.
Gank the Headhunter and Flying Titan pilot has five attacks per melee in power armour and has an initiative of 31 (he rolled a 19, has +6 from Sixth Sense, and +6 for Amplified Hearing). By dividing his initiative by his total number of attacks, we see that he will go on segments 31, 24, 18, 12, and 6.
Next, the GM calculates their opponent's initiative. Warhawk the Gargoyle has an initiative of 17 after bonuses, and six attacks per melee. As such, Warhawk will attack on initiative segments 17, 14, 11, 8, 5, and 2.
The GM's Chart of Initiatives would look like this:
<center><table border="1" cellspacing="1"> <tbody><tr> <td width="74" align="right">
</td> <td> <center>31</center> </td> <td> <center>30</center> </td> <td> <center>29</center> </td> <td> <center>28</center> </td> <td> <center>27</center> </td> <td> <center>26</center> </td> <td> <center>25</center> </td> <td> <center>24</center> </td> <td> <center>23</center> </td> <td> <center>22</center> </td> <td> <center>21</center> </td> <td> <center>20</center> </td> <td> <center>19</center> </td> <td> <center>18</center> </td> <td> <center>17</center> </td> <td> <center>16</center> </td> <td> <center>15</center> </td> <td> <center>14</center> </td> <td> <center>13</center> </td> <td> <center>12</center> </td> <td> <center>11</center> </td> <td> <center>10</center> </td> <td> <center>9</center> </td> <td> <center>8</center> </td> <td> <center>7</center> </td> <td> <center>6</center> </td> <td> <center>5</center> </td> <td> <center>4</center> </td> <td> <center>3</center> </td> <td> <center>2</center> </td> <td> <center>1</center> </td> </tr> <tr> <td width="74"> Char
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td> <center>3</center> </td> <td> <center>2</center> </td> <td> <center>1</center> </td> </tr> <tr> <td width="74"> Gank
</td> <td> <center>31</center> </td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td> <center>24</center> </td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td> <center>18</center> </td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td> <center>12</center> </td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td> <center>6</center> </td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr> <td width="74"> Illyana
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td> <center>20</center> </td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td> <center>10</center> </td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr> <td width="74"> Johann
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td> <center>12</center> </td> <td>
</td> <td>
</td> <td> <center>9</center> </td> <td>
</td> <td>
</td> <td> <center>6</center> </td> <td>
</td> <td>
</td> <td> <center>3</center> </td> <td>
</td> <td>
</td> </tr> <tr> <td width="74"> Victor
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td> <center>18</center> </td> <td>
</td> <td>
</td> <td>
</td> <td> <center>14</center> </td> <td>
</td> <td>
</td> <td>
</td> <td> <center>10</center> </td> <td>
</td> <td>
</td> <td> <center>7</center> </td> <td>
</td> <td>
</td> <td>
</td> <td> <center>3</center> </td> <td>
</td> <td>
</td> </tr> <tr> <td width="74"> Warhawk
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td> <center>17</center> </td> <td>
</td> <td>
</td> <td> <center>14</center> </td> <td>
</td> <td>
</td> <td> <center>11</center> </td> <td>
</td> <td>
</td> <td> <center>8</center> </td> <td>
</td> <td>
</td> <td> <center>5</center> </td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table> </center> By starting with the highest initiative number (31), we see that Gank goes first on initiative segment 31. Next is Gank again on 24. After that Illyana goes on segment 20. Both Gank and Victor act on segment 18, but since Gank has the highest initiative number (31 compared to Victor's 18), then Gank goes first ...
​

5. Once everyone has used all of their attacks, re-roll initiative for next round.

 

[SteveO59L]

send me a pm with your e-mail, and I will work up a sample sheet. See if we are on the same train of thought

 

[Arrownight]

thank you very much, pm sent

 

[Arrownight]

Amazing! Dude this is almost perfect! It is very, very, very, very, close, with an amazing improvement! I noticed you did something Awesome! You included a random number generator! That is Insane and that rocks.

Here is what will make it perfect
1. In the game, most players attacks are static, and not part of the random roll. I would like to be able to key in that information and have it remain until i change it.
2. In the game, most players roll their own initiative, so I would need to be able to key in the rolls they come up with on the table, in that way the random generator would not work for them. They would probably feel uninvolved if i decided everything for them by computer.
3. Having an automated initiative dice roll for monsters is excellent! It saves me the time of having to roll it myself for big combats (which isn't too much of a bother, as i have been doing it all these years), the initiative roll in the game is fetermined by rolling a random number from 1-20, and then adding any specific character or monster bonueses. What if there were two types of columns - one for player, or one for monster? or a box next to character name that an "X"could be placed in-to indicate monster (any creature under my control), and if the box was marked, then a random initiative roll would be done for that line? It would also need a "+" column so that I could add a number from 1-12 to the final result, resulting in and initiative range from 1-32. This seems convoluted while typing it out, and since it is such a minor function, I would be okay with removing it (we roll a lot of dice during the game anyway, its part of the fun).
4. I notices that the initiative segement doesn't always count the first segement or highest starting point of the initiative. For example:
Right now on the sheet I have:
Initiative 26 Attacks 12 I/A:2 The resulting segments are 12 and 24. I am not sure what I/A is
All I would need is to be able to input initiative, divide it by attacks, to get each segment

The result in game is Initiative 26/2 attacks=13. Then we would start counting Backwards with the highest number 26, then minus 13 from that to get 13. so this character acts on segments 26 and 13.
The formula i used in my excel sheet is this =IF(B2/C2*(C2-1)>=0,B2/C2*(C2-<wbr>1),0)
i will send my worksheet to you for your review.

On sheet 2, you will see the computation process i was using (I didn't even bother with the counting up for low rolls)
and my main goal was moving the info from sheet 2 to the big chart

5. Lastly, i would like the sheet to count down from 32-1 instead of 1-32. Its an easier and faster read.

6. Thank you so much for your help. It is nice of you to help a stranger at 6 in the morning over some abstract nonsense. Your help, however it is you have seen fit to aid someone you don't know on something you might not care about at an ungodly hour is very much appreciated and well received. Thank you.


It is awesome!

mailed you the .xls