The official Airtable Formula Field Reference states that ROUND will round up. But it doesn’t, it rounds both up and down. How can I round to the next highest full integer? For instance, 3.14 should be rounded to 4.
ROUND({Working Days Out}, 0)
Working Days Out = 3.14
Thank you,
A.
To clarify—the documentation says that ROUND will round up. This is not strictly true (in that ROUND does not function like a ceiling function). A more accurate way of wording this would be that ROUND breaks ties using the round half toward positive infinity rule. I’ll reword the documentation to make this clearer—thanks for bringing it to my attention.
If you want to force the ROUND function to work like a ceiling function, you can add 0.5 to the formula to force it to round up, i.e. ROUND({Working Days Out}+.5, 0). Hope this helps!
Could you provide a screenshot of this? I can’t reproduce this on my own account.
No problem; see attached. it’s rounding both up and down.
A.
[cid:FEA91212-E363-4FE0-BF27-A8D4E2F6004C]
No problem; see attached. it’s rounding both up and down.
A.
[cid:FEA91212-E363-4FE0-BF27-A8D4E2F6004C]
Okay, I think I misunderstood what you were saying originally.
You are correct that ROUND doesn’t round up in all cases; it actually rounds to the nearest integer with ties broken by going to the greater integer (e.g. 25.4 will round down to the nearest integer, 25; 25.5 is exactly halfway between 25 and 26 and the tie is broken by taking the greater number, 26).
I have fixed the documentation to more accurately reflect this behavior. In the meantime, to better represent your desired rounding behavior, I suggest you follow the instructions I gave in my last post here.
To clarify—the documentation says that ROUND will round up. This is not strictly true (in that ROUND does not function like a ceiling function). A more accurate way of wording this would be that ROUND breaks ties using the round half toward positive infinity rule. I’ll reword the documentation to make this clearer—thanks for bringing it to my attention.
If you want to force the ROUND function to work like a ceiling function, you can add 0.5 to the formula to force it to round up, i.e. ROUND({Working Days Out}+.5, 0). Hope this helps!
Thank you.
That workaround addresses most of my needs, once I replace 0.5 wit 0.49.
That said, it does not work for negative numbers. In which case, I’d expect -2.14 to be rounded “up” to -3. But perhaps that’s not a common expectation; I’m no mathematician.
Best,
A.
[cid:794AD3A7-246E-4C2C-AE5A-874DC0371751]
Thank you.
That workaround addresses most of my needs, once I replace 0.5 wit 0.49.
That said, it does not work for negative numbers. In which case, I’d expect -2.14 to be rounded “up” to -3. But perhaps that’s not a common expectation; I’m no mathematician.
Best,
A.
[cid:794AD3A7-246E-4C2C-AE5A-874DC0371751]
The type of tie-breaking you’re describing there (where -2.14 gets rounded to -3) is called rounding half away from zero, which is a little different from the type of rounding our ROUND function uses (rounding half up toward positive infinity). You can mimic the rounding half away from zero rule using an IF function.
IF(Value>0, ROUND(Value + .4999), ROUND(Value - .4999))
Thank you.
That workaround addresses most of my needs, once I replace 0.5 wit 0.49.
That said, it does not work for negative numbers. In which case, I’d expect -2.14 to be rounded “up” to -3. But perhaps that’s not a common expectation; I’m no mathematician.
Best,
A.
[cid:794AD3A7-246E-4C2C-AE5A-874DC0371751]
Perfect!
Thank you,
A.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
