Register Register

Author Topic: Excel custom Cluster Roller  (Read 2491 times)

Legion

  • Master Sergeant
  • *
  • Posts: 305
  • Je te provoque en duel, Freebirth!
Excel custom Cluster Roller
« on: 12 January 2015, 00:42:05 »
I think this is the appropriate place to put this...

Here's a spreadsheet I put together to roll cluster hits, for a few reasons:

1.  I wanted to speed up this part of the game (love my LBX 20, afterall) but a Box o' Death approach wasn't doing it for me,
2.  I've heard there's a good rolling app on Android, but I have no Android devices, what I do have available is a Surface tablet with MS Office,
3.  I wanted to be able to customize and use my own cluster hits table (as has been done by others before me, I tweaked the curves more to my satisfaction), and
4.  I just like playing with Excel, and wanted to see if I could produce something I was happy with.

I am now happy with it, so I present it here in case anyone else may find it useful.  Naturally I have tested this, but of course may have missed some combination of variables that will return errors, so if anyone does use it and finds problems, let me know.  Un-hide cells "I" through "DK" if anybody wants to see the cluster chart I use, or input their own (there's an option to select the custom or standard chart), or just see the way I set it up, if you can make sense of it. I tried to label it so that I could remember what does what, but it's probably not created in the most efficient manner.

Oh, and if anybody actually finds it useful but wants a different feature or something else added, let me know and I might try to do it, I am no Excel god but I do like to play around with it.

idea weenie

  • Major
  • *
  • Posts: 4213
Re: Excel custom Cluster Roller
« Reply #1 on: 31 January 2015, 14:16:35 »
Your custom chart does about 3% more damage than the original, but it is smooth.  (I calculated the total damage for each column, assuming the 36 rolls for each column, multiplying the amount of damage by the number of times that was rolled.  So in th2 '2' column on the original, a 2d6 roll of 2-7 results in 1 pt of damage, and 8-12 results in 2 pts of damage.  This results in 21 rolls getting 1 pt of damage, 15 getting 2, for a total of 51 pts of damage.)

Column 9 is the highest, doing roughly 10% more damage than the original, while column 12 does only 98% the damage of the original.  Still, pretty darn close.

Legion

  • Master Sergeant
  • *
  • Posts: 305
  • Je te provoque en duel, Freebirth!
Re: Excel custom Cluster Roller
« Reply #2 on: 01 February 2015, 00:47:39 »
Certain columns did change, when working on this I basically tried to accomplish 3 things:

1.   Smooth the curves, giving more variety in damage for the higher clusters;
2.   Keep the same structure as the original chart with regard to rolling high or low, i.e. the minimum hits when you roll 2-3, and the max hits when you roll 11-12; and
3.   Bring all the curves in line concerning minimum damage, median damage, and mean damage.

To elaborate on the third point, the minimum clusters should be 1/3 max clusters, while the median and mean values should both be 2/3 max clusters.  Or as close as possible, of course, since not all numbers are nicely divisible.  There were a few columns that bugged me by not following this rule to be as close as possible, specifically where it was possible to hit with only 1 on the 5 column, and the balance of the 9 column, where the average clusters was 5.47 of 9, significantly lower than the 6 of 9 it should be to fit the trend, and easily corrected.  That's why the 9 column got a boost and 12 got a slight decrease, to bring them in line with the rest of the table.