SharePoint: Round Function

Rounding is a super valuable and essential math operation, but it can be tricky depending on what you want to do. Also, some rules rely on the parameters, so it’s good that we have the SharePoint Round Function to help us.

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

Usage

It follows a simple pattern.

Number to convert
Number of digits to round the number

The direction of the rounding depends on the number that we’re checking. If it’s below five, then we’ll round down; otherwise, we’ll round up. Also, if you want, you have functions for always doing these operations called RoundUp and RoundDown

Let us start with a simple example:

=Round([numberColumn],0)

1.2

will return 

1

How about a negative number?

=Round([numberColumn],0)

-1.2

will return 

-1

and

-1.9

will return

-2

So far, so good. All makes sense. If we change the “Round” function and run both cases, we’ll get:

=Round([numberColumn],1)

1.2

will return

1.2

and if we try

-1.2

will return 

-1.2

Why? Because we’re considering the first character, and we don’t have a second one to round.

Now let’s try a negative number in the second parameter.

=Round([numberColumn],-1)

1.2
0.75
-1.9
-1.2

will all return 

0

For negative numbers for the second parameter, we’ll consider the digit to round at the left of the comma. Since we’re rounding down for the first two and up for the second two, all of the above will round to zero.

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

=Round([textColumn],-1)

"1.2"
"-1.9"
"0.75"
"-1.2"

will all return 

0

All the same results.

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 Round functions in a formula, you should review it and make everything more straightforward.