SharePoint: RoundDown Function

Rounding is a fundamental operation in math, but it can be tricky depending on what you’re rounding. That’s why having the SharePoint “RoundDown” function is helpful to avoid us having to do the math.

So 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 down.

Example:

=RoundDown([numberColumn],0)

1.2

will return 

1

How about a negative number?

=RoundDown([numberColumn],0)

-1.2

will return 

-1

Would you expect -2, right? But notice that we’re rounding down, and -1 is lower 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 “RoundDown” function and run both cases, we’ll get:

=RoundDown([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.

=RoundDown([numberColumn],-1)

1.2
-1.9
0.75

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, all of the above will round to zero.

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

=RoundDown([textColumn],-1)

"1.2"
"-1.9"
"0.75"

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

Sources:

Microsoft’s RoundDown Function Reference

Introduction to SharePoint formulas and functions

Back to the SharePoint’s list formula reference.

Photo by Chaitanya Tvs on Unsplash