No account? Create an account
D20 and Excel
7th-Feb-2007 02:06 pm
Here's a question for you excel gamers...

I'm working on an excel spreadsheet for personal purposes. Part of the spreadsheet involves calculating the bonuses derived from character/creature attributes. For attributes 10 and above, I'm using the following formula:

=roundup((A1-10)/2,0)

By itself this is working quite well. For a value of 7 or 6, for example, it returns -2. For attributes 10 and greater, I use the following formula:

=rounddown((A1-10)/2,0)

This also works out well when used by itself. For values of 12 and 13, for example, it returns 1. But when I combine them with an if statement as follows:

=if(A1<10,roundup((A1-10)/2,0),rounddown((A1-10)/2,0))

it does not return the proper values, instead acting as if I had only used =rounddown((A1-10)/2,0) as my formula. Using this, it always returns a 0 for a value of 9, -1 for values of 8 and 7, -2 for 6 and 5, and so on.

Do any of you see flaws in the latter formula?
7th-Feb-2007 10:17 pm (UTC)
I don't know Excel, but the formula is (stat / 2 - 5) (assume integer math).

7th-Feb-2007 10:21 pm (UTC)
Doh!

Occam's Razor is now slicing my pride...
7th-Feb-2007 11:05 pm (UTC)
stat/2-5 doesn't solve the problem, since the problem is rounding in Excel. For example, 11/2-5 = 1.5, which will round to 1.

The easiest way to compute ability score bonuses in Excel is: =EVEN(A1-1)/2-5 (assuming A1 contains the ability score).

7th-Feb-2007 11:08 pm (UTC)
actually, what worked was the other suggestion of "Integer"

using =INT(A1/2-5) works perfectly

7th-Feb-2007 11:09 pm (UTC)
And for the record, the INT command is the reason for slapping the forehead. It does just what I need without the need for rounddown and roundup
8th-Feb-2007 03:36 am (UTC)
For working with stats in Excel, I use a lookup table for the point buy cost of a score, and

=+FLOOR(E2/2,1)-5

for the bonus.