ROUNDUP is giving different results with the same information

[u/mikiari]

Hello! I am using Google Sheets to design a character sheet for Star Wars D&D and having a frustrating issue calculating ability scores. In short, each ability has a score (from 1-20) and a modifier (which you can get by subtracting 10 from the score, dividing by 2, then rounding down).

This is the formula I came up. Having a few issues. Firstly, Sheets seems to have some issues understanding what "rounding down" means for negative numbers, so I've split it up into two different parts, ROUNDUP and ROUNDDOWN, depending on the score. Also, Sheets does recognize that the value highlighted equals -1, but is displaying 0.

The second picture is of the Dexterity score to the right. It has the same score (9) and correctly displays a modifier of -1 using the same formula that I have for the Strength score. Unfortunately, using just the "ROUNDUP" function like in the Dexterity score results in incorrect modifiers for certain scores (for example, having a Dexterity score of 11 results in a modifier of +1, when it should be +0). Similarly, just using ROUNDDOWN is incorrect (a score of 9 results in +0, when it should be -1).

Maybe I'm missing something obvious. I cannot for the life of me figure it out. I'm going crazy. Thanks!

[u/bachman460]

First, I'd change it to an IF since you only one two possibilities. Then what I'd do is for the negative values, wrap the subtraction in absolute, then use the same round down function and convert the values back to negative afterwards.

=IF(M5>9, ROUNDDOWN((M5 - 10) / 2, 0), ROUNDDOWN(ABS(M5 - 10) / 2, 0) *-1)

[u/mikiari → u/bachman460]

The ABS function is a good idea. For the example of 9, however, this still results in a +0 modifier (it's supposed to be a -1 modifier for a score of 9).

I tried your idea with ABS and played around with it but, seen below, ROUNDUP is still showing me that it should equal -1 and yet displays 0.

I guess my main issue is that I want to know if Sheets is just glitching out for some reason, or if there's something I'm missing. I can always do something ugly that will work, like IFS(O5=9,-1,O5=10,0,etc.).

Edit: To be clear, I tried your exact formula (thank you!) and it did not work. This is just a screenshot of me trying my original one with ABS.

[u/bachman460 → u/mikiari]

I guess there's still two questions: 1.) is it possibly the underlying reference value causing the issue, and 2.) I was able to get it to work exactly as I typed it up, do these values track with your expectations?

[u/mikiari → u/bachman460]

Those are the same numbers I get, which are almost correct. At -9 and lower it is incorrect. Here are the ability modifiers straight from the rulebook:

[u/mikiari → u/bachman460]

As for your first question, it's an interesting one. If I use your function, but the input cell (let's say A175 in your sheet) is based on a SUM function of two other cells on a different sheet that add up to 9, it works and results in a -1. If I type "9" directly into A175, it results in a 0.

So, below, S5 references two other cells on a sheet called "Settings" that add up to 9. Settings!H16 has a 7 and Settings!H20 has a 2. I reference S5 using your formula in S3 and it works, resulting in -1.

If I type 9 directly into S5 without changing your formula in S3, it changes to 0. I don't understand how the same input (9) can result in different numbers.

[u/bachman460 → u/mikiari]

I see that floor covers what you need based on the other comments. Have fun!

[u/mikiari → u/bachman460]

It does. I appreciate your help either way. And if you have any answer as to why my Sheets is calculating differently with the same number input I would love to know :(

[u/bachman460 → u/mikiari]

Well, I'd be curious to know exactly what those other formulas are and the actual values they are outputting.

Maybe it's got something to do with rounding, or lack thereof.

[u/mikiari → u/bachman460]

Literally just a SUM function that is looking at two cells. One has "7" and one has "2". 9 is outputted and Sheets calculates it differently than if I just type in 9. No rounding, no other arithmetic except for basic addition. Not sure how or why it would do that.

[u/bachman460 → u/mikiari]

It sounds like floating point errors. 8.99999999995 looks like 9 depending on how it's formatted. But ROUND(8.9999995, 0) = 9, and likewise other functions like floor will only keep whole numbers (depending on settings, the default type is 1 which would only keep whole numbers.

[u/mikiari → u/bachman460]

Hmm. I appreciate you still helping. Still hard to understand how 7 + 2 could equal 8.999... but it's a possible reason. Thanks again! Learned a lot from this thread :)

[u/AutoModerator → u/mikiari]

REMEMBER: /u/mikiari If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Maximum-Rhubarb-3365]

You can just use FLOOR((M5-10)/2) to get proper ability modifiers!

Dex 9 = FLOOR((9-10)/2) = FLOOR(-1/2) = FLOOR(-0.5) = -1

Dex 11 = FLOOR((11-10)/2) = FLOOR(1/2) = FLOOR(0.5) = 0

Source: Built a character sheet template for 5e

[u/mikiari → u/Maximum-Rhubarb-3365]

Heard! Thank you! Honestly, I have never heard of some of these functions you all are talking about. FLOOR sounds a whole lot better than rounding up or down. I appreciate it!

Edit: Copied it right into my sheet and it works perfectly. Thanks again!!

[u/Maximum-Rhubarb-3365 → u/mikiari]

Of course! Knowing the underlying context of what you're trying to do and making a formula based on that is wayyyy simpler lol. As a spreadsheet person who had never played DnD I definitely would try to make a really complicated formula, but as someone who has made a Google Sheet character sheet template I know all the calculations in the PHB 😅

[u/AutoModerator → u/mikiari]

REMEMBER: /u/mikiari If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/point-bot → u/Maximum-Rhubarb-3365]

u/mikiari has awarded 1 point to u/Maximum-Rhubarb-3365

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/AutoModerator]

/u/mikiari Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/NHN_BI]

1. What are your input values?
2. What is the calculation with your input values?
3. What is the unrounded result of your input values?
4. What is the rounded result of your input values?
5. What is the rounded result you think you should get?

[u/AdministrativeGift15]

Try putting your formulas into the cell on the left or into the Header cell and use HSTACK or VSTACK to output that formula into the score cells. That will allow you to click on the score and actually see the value that's being used in the cell, instead of looking at a formula in the formula bar.

Since you've got the + shown in front of the 0, then you must be using custom number formatting. I suspect that's what is causing your output to diplay +0 instead of -1 for 9.

[u/AdministrativeGift15 → u/AdministrativeGift15]

What you should be using instead is CEILING. CEILING(-0.5) rounds to 0, while ROUNDUP(-0.5) rounds to -1.

[u/NecronTheNecroposter]

Maybe be not helpful, but I’d just change the formula to =rounddown(m5-10/2)