SharePoint: RoundUp Function

Rounding number is sometimes necessary, and it’s an essential operation in math, but it can be tricky depending on how you do it. That’s why having the SharePoint “RoundUp” function is handy to have.

Let’s explore it a bit more and see how it works.

Usage

It follows a simple pattern.

1. The number that we want to round
2. Number of digits to round the number

Please note that the numbers will always be rounded up.

Example:

=RoundUp([numberColumn],0)

1.2

will return 

2

How about a negative number?

=RoundUp([numberColumn],0)

-1.2

will return 

-2

Were you expecting 0, right? But notice that we’re rounding up, and -2 is bigger than -1 (the parameter is zero, so we ignore the other digits). It’s tricky, but with negative numbers, we have to look at it in the opposite way 😀.

If we change the SharePoint “RoundUp” function and run both cases, we’ll get:

=RoundUp([numberColumn],1)

1.2

will return

1.2

and if we try

-1.9

will return 

-1.9

Why? Because we’re considering the first character after the period, and we don’t have a second one to round, so the number stays unchanged.

Now let’s try a negative number in the second parameter. In this case, we’ll consider the digit to round at the left of the comma.

=RoundUp([numberColumn],-1)

1.2

will all return 

10

It’s strange but thinks about it since we’re rounding up and indicating that we want to round one digit before the comma. Unfortunately, rounding looks at the next digit after the one we want to convert, so we need to add one, in this case, zero.

01.2

Rounding up the second number, we get one, and since we ignore the number before that, we will get 0 for the first one.

Are you confused yet? Me too, but this is so rare to use that I was tempted not to include it here. However, if you have a good reason to do something like this, please let me know.

How about string? The same behavior applies to all strings that contain numbers.

=RoundUp([textColumn],1)

"1.2"

will return 

1.2

The same result as a number column. How about choice columns? The same behavior as strings and number columns, but only if you’re using the single select columns. If you select the option “Allow multiple selections,” then you won’t even be able to pick it to generate the formula.

Finally, let’s try dates – SharePoint, as with Excel, stores internally the dates as numbers. I go into much more detail on “Convert Excel Number to Date” and “Convert Date to Excel Number,” but the short version is that Excel stores the number of days elapsed since 1st January 1900. So that’s where the number comes from. The number will be converted, and the decimal places will be considered a “normal” number column.

Limitations

There are no documented limitations for this, but if you know some please let me know by email or Twitter.

Recommendations:

“Hidden” Validation Columns

Since SharePoint works with columns, you can create hidden “validation” columns with the functions validating the data. After that, you can expose the “message” columns that have helpful error messages or actions that they can take to solve the problem.

Always deal with errors.

Leaving them for the user to see is horrible, but also you can bring trouble to your code. If you don’t validate a column, it can be used in other calculations, and the error will cascade. You can have an error in one column, and the issue could be in whole different columns, so deal with issues where they happen.

Don’t nest it.

There’s no real reason to do it, but if you find yourself in a situation where you have nested RoundUp functions in a formula, you should review it and make everything more straightforward.

Sources:

Microsoft’s RoundUp Function Reference

Introduction to SharePoint formulas and functions

Back to SharePoint’s list formula reference.

Photo by Ben Sweet on Unsplash