Power Automate: How to round a number?

It’s quite common to need to round a number, but when you go into Power Automate’s expression tab and search for something related to “round” you get nothing. Power Automate doesn’t have a native round function, but other functions can help us do that.

We’ll use the “formatNumber function” to do most of the heavy lifting, but there are some types of rounding that we cannot use, so I’ll show you nice workarounds.

I don’t want to bore you with the math on rounding numbers, so I’ll assume if you’re reading this article, you know how rounding up and down should work.

Rounding Strategies

There are a lot of rounding strategies, but we’re going to give examples:

• Rounding – we’ll use the most common rounding strategy called “Half Round Up” to define a rounding point and check the next digit. If the digit is five or above, we round up; otherwise, we round down.
• Round Up – we’ll always round the number up regardless
• Round Down – we’ll always round the number down regardless.

Let’s explore them.

“Natural” Rounding

As mentioned, let’s use the “formatNumber function” to round a number. Let’s use the number “3.14159” as an example. If you want to round to the first digit, you’ll want the result to be “3.1”. So let’s see if we can get the same result with the function. To do it, we need to build the formula and provide it in a “compose” action, for example.

The formula is as follows:

formatNumber(3.14159,'#.0')

Here’s an example:

Here’s the result:

The formula is simple. We want to round to the first decimal place using a dot and one zero. If you want to round to the 3rd decimal place, you will use the following formula:

formatNumber(3.14159,'#.000')

Here’s the result:

Notice that the number is rounded up now since the number after the digit we want to round is five or above.

Rounding to the next whole number

The objective is again to have a number like “3.14159” and round to the nearest whole number, in this case, “3”.

To do it, we’ll use again the “formatNumber function” like this:

formatNumber(3.14159,'#0')

The result is what we would expect:

The difference between the functions is the period, meaning that, in this case, we don’t want any decimal place to be considered in the rounding.

Rounding always Up and down

The “formatNumber function” can only get us so far. If you want to round always up or down, then there are a few tricks that you need to use. And I want to stress the word “tricks” since they only do this via workarounds.

Here’s how to always do it up. The formula is scary, and I won’t try explaining the math. But if you’re curious, here’s a good explanation.

sub(3.1415,mod(3.1415,1))

Replace the 3.1415 (in both places) with your number. This way, you’re always sure that the number is correctly rounded, even if it’s not a decimal. Here’s the result:

Now let’s look at the rounding down. Same strategy but different formula:

add(3.1415, sub(1,mod(3.1415, 1)))

Here’s the result.

Final thoughts

All of this will be fixed when Power Automate releases the support for rounding, but until then, we need to use the tools at our disposal. I’m sure that Microsoft is working on this, and it will be released soon, but in the meantime, check the formulas (including mine) that you see online and test them in your workflows to ensure that they always return the values that you expect.

Photo by Dustin Humes on Unsplash