July 19, 2024

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.
There are other rounding mechanisms that we can explore. If you want me to add more, please let me know, and I would be happy to add more.

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:

Power Automate how to round a number formula

Here’s the result:

Power Automate how to round a number naturally with decimal places

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:

Power Automate how to round a number always naturally with decimal places

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:

Power Automate how to round a number always naturally

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:

Power Automate how to round a number always down

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.

Power Automate how to round a number always up
I see a lot of solutions for this, most of them using complex formulas. If you decide to use them, test them carefully because they may not cover all cases, for example, if you provide a number that is not decimal.

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

 

Manuel Gomes

I have 18 years of experience in automation, project management, and development. In addition to that, I have been writing for this website for over 3 years now, providing readers with valuable insights and information. I hope my expertise allows me to create compelling, informative content that resonates with the audience.

View all posts by Manuel Gomes →

3 thoughts on “Power Automate: How to round a number?

  1. Should the round up and down formulas be swapped around? You say “here’s how to do it up” but I think it should read “here’s how you do it down”. The screen snips and the text don’t match.

  2. Excelled workaround Manuel. Referred multiple sources, but this logic and code to RoundUp worked smoothly.
    I second Sarah’s comment though, the blog needs to be updated for the section where RoundUp is mentioned as Round Down.
    Thanks a ton!

Leave a Reply

Your email address will not be published. Required fields are marked *

Mastodon