Author Topic: Excel Fun times?  (Read 3858 times)

Offline littha

  • Epic Member
  • ****
  • Posts: 2952
  • +1 Holy Muffin
    • View Profile
Excel Fun times?
« on: November 11, 2011, 05:41:17 PM »
I was at a complete loss trying to think what forum to put this in but I guess I will go with this one.

I am making a spreadsheet that calculates the optimal amount of power attack to use for a character and the total damage output. This has quite a lot of variables as I am working out the % chance of hitting with each amount between 1 and 20 and multiplying the results with the expected damage for an average damage figure that includes miss chance.

The problem is that I am having some issues with some of the calculations, any help would be appreciated with:
- Some way of stopping cells that have a power attack number higher than your BAB being included in the calculation
- Hiding cells if possible
- Testing


The sheet is attached below but apparently wont show up until its approved by a mod
« Last Edit: November 11, 2011, 09:50:10 PM by littha »

Offline Childe

  • DnD Handbook Writer
  • ***
  • Posts: 485
  • Even forever must end, I think. ...
    • View Profile
    • Legend RPG, Rule of Cool Gaming
Re: Excel Fun times?
« Reply #1 on: November 11, 2011, 06:10:02 PM »
You can lock cells by right-clicking and going into format. You can hide entire rows or columns by right-clicking the row or column and hiding it from the drop-down. You cannot hide individual cells (though you can hide their formulas, but this does not mix well with those formulas actually running).

For testing, I recommend simply using the Move/Copy sheet option to copy the sheet and mess around with the copy.

For the BAB issue, I'd have to see the file to know how you're running the calculations. Also, remember to account for critical hits.
"You had a tough day at the office. So you come home, make
yourself some dinner, smother your kids, pop in a movie, maybe
have a drink. It's fun, right? Wrong. Don't smother your kids."
- The More You Know

Offline littha

  • Epic Member
  • ****
  • Posts: 2952
  • +1 Holy Muffin
    • View Profile
Re: Excel Fun times?
« Reply #2 on: November 11, 2011, 06:35:27 PM »
Ah, by hiding cells I mean to hide the columns for attacks 2, 3 and 4 if you don't have enough BAB to qualify for them or hiding the rows for Power attack values above your BAB.

Offline Childe

  • DnD Handbook Writer
  • ***
  • Posts: 485
  • Even forever must end, I think. ...
    • View Profile
    • Legend RPG, Rule of Cool Gaming
Re: Excel Fun times?
« Reply #3 on: November 11, 2011, 06:49:35 PM »
Ah, by hiding cells I mean to hide the columns for attacks 2, 3 and 4 if you don't have enough BAB to qualify for them or hiding the rows for Power attack values above your BAB.

If you mean for those cells to remain blank, then use
=IF([condition],[formula],"")
The "" will display a blank cell if the condition is not met. Do not use the [] brackets.

For instance:
=IF(A1>=6,[formula],"") for the first iterative attack, using A1 as an example for where the user might input the BAB of the character.
« Last Edit: November 11, 2011, 06:51:09 PM by Childe »
"You had a tough day at the office. So you come home, make
yourself some dinner, smother your kids, pop in a movie, maybe
have a drink. It's fun, right? Wrong. Don't smother your kids."
- The More You Know

Offline Prime32

  • Over-Underling
  • Retired Admin
  • *****
  • Posts: 2914
    • View Profile
Re: Excel Fun times?
« Reply #4 on: November 11, 2011, 09:41:52 PM »
The sheet is attached below but apparently wont show up until its approved by a mod
The BGs prefer that you host files on Mediafire or a similar site.
Those things take up space.

Offline littha

  • Epic Member
  • ****
  • Posts: 2952
  • +1 Holy Muffin
    • View Profile
Re: Excel Fun times?
« Reply #5 on: November 11, 2011, 09:49:41 PM »
Fair enough, I shall see if I can find an upload site that will take such a small file.

Offline littha

  • Epic Member
  • ****
  • Posts: 2952
  • +1 Holy Muffin
    • View Profile
Re: Excel Fun times?
« Reply #6 on: November 11, 2011, 10:00:11 PM »

Offline Childe

  • DnD Handbook Writer
  • ***
  • Posts: 485
  • Even forever must end, I think. ...
    • View Profile
    • Legend RPG, Rule of Cool Gaming
Re: Excel Fun times?
« Reply #7 on: November 11, 2011, 10:53:40 PM »
It may just be GoogleDocs not being Excel, but you currently have an =IF([condition],[formula],0) setup for your iterative attacks. If you're actually using 0 in Excel (and it's not simply an artifact of Google), it will say 0% rather than remaining blank when the BAB condition is not met. While technically remaining true (you won't hit with attack 2 because you won't make it), it seems misleading.

That said, it looks fine for what it covers, but I also notice it doesn't calculate the number of attacks correctly.

The formula you use, ROUNDDOWN((B32/4)+1,0), scales iterative attacks at an accelerated rate from what is actually the case. Iterative attacks scale every 5 levels after 1, not every 4, and not every 4th level (or 5th), but rather each Nth level after 1 (5th level after = 6th) so you should use ROUNDDOWN((1+(B32-1)/5),0)
"You had a tough day at the office. So you come home, make
yourself some dinner, smother your kids, pop in a movie, maybe
have a drink. It's fun, right? Wrong. Don't smother your kids."
- The More You Know