Blog

Numbers - 10 examples of how to round numbers

When we work with numbers with Power Apps, a common requirement is to round numbers. This post summarises the formula to carry out some of the most common rounding requirements, including how to round to multiples, how to round to values such as 0.5, 0.99, 0.45, how to round to odd/even numbers, and how to return the integer portion of a decimal value.

Power Apps provides 4 key functions to round numbers. These are Round, RoundUp, RoundDown, and Trunc.

It may not be obvious how to apply these functions to carry out a specific rounding task and therefore, this post summarises some of the more common rounding requirements.

The official documentation for these functions are here:

1 - Round to 2 decimal places

Starting with the most popular and straightforward example, we can round a number 2 to decimal places by calling the Round, RoundUp, and RoundDown functions.  With all 3 of these functions, the second parameter defines the target number of decimal places.

//Round - This rounds 56.4555 to 56.46 
Round(56.4555, 2)

//Round - This rounds 56.4411 to 56.44 
Round(56.4411, 2)

//Round - This rounds 56.4555 to 56.46 
Round(56.4588, 2)

////////////////////////////////////////////////

//RoundUp -  This rounds up 56.4411 to 56.45  
RoundUp(56.4411, 2)

//RoundDown - This rounds down 56.4588 to 56.45 
RoundDown(56.4588, 2)   
The most common reason why these functions fail is in cases where Power Apps determines the input value to be of type text, rather than type number. This often happens in cases where we retrieve data from a data source through a form or gallery control. In such cases, the fix for this type of problem is to call the Value function to convert the input value to a number. The example formula looks like this:

Round(Value(ThisItem.TotalSalesAmount), 
      2
)
Another common problem app builders face is with SharePoint calculated columns. Power Apps does not correctly recognise numeric SharePoint calculated columns as numbers and there are more details in this post:

2 - Round Up to the next multiple (ie Ceiling function)

With Excel, there is a function called Ceiling. This function rounds up a number away from zero, to the nearest multiple of significance. With Power Apps, there is no equivalent function.

However, we can implement the same functionality by first rounding up the input value divided by multiple, and multiplying the output of this by multiple. The examples beneath demonstrate how to round up an input value to the next 20.

//Power Apps Ceiling function examples

//This rounds up 4565 to 4580
RoundUp(4565/20, 0)*20

//This rounds 4560 to 4560
RoundUp(4560/20, 0)*20

3 - Round Down to the next multiple  (ie Floor function)

A related Excel function where there is no direct Power Apps equivalent is the floor function. This rounds down a number to the nearest specified interval.

The examples beneath demonstrate how to round down an input value to the nearest 20.

//Power Apps Floor function examples

//This rounds 4565 to 4560
RoundDown(4565/20, 0)*20

//This rounds 4560 to 4560
RoundDown(4560/20, 0)*20

4 - Round to nearest thousand 1,000

By applying the same technique that we use to implement ceiling and floor functionality, we can round an input value to the nearest 1,000.

The typical use case for this is to round large currency values - for example - $156,876  => $157,000

//This rounds 156,876 to 157,000
Round(156876/1000, 0)*1000

//This rounds 156,476 to 156,000
Round(156476/1000, 0)*1000

5 - Round to the nearest half (eg round to 0.5)

A common requirement is to round to the nearest half, or the nearest 0.5. This is common when working with currency values, and an example is to round the value $1.34 => $1.50.

The formula beneath demonstrates how to carry out this task. Note that with these examples, we can substitute the call to Round with RoundUp or RoundDown in cases where we want to round up or to round down to the nearest half respectively.

//This rounds 6.22 to 6
Round(6.22/0.5,0)*0.5

//This rounds 6.32 to 6.5
Round(6.32/0.5,0)*0.5

//This rounds 6 to 6
Round(6.00/0.5,0)*0.5

//This rounds 6.84 to 7
Round(6.84/0.5,0)*0.5

6 - Round to the nearest .99

When working with sales or pricing type apps, a typical requirement is to set the price of items to a value that ends with .99 - for example, $5.99.

To round an input value to the nearest 0.99, we can round the input value and then subtract 0.01. 

//This rounds 6.22 to 5.99
Round(6.22,0) - 0.01

//This rounds 6.61 to 6.99
Round(6.61,0) - 0.01

7 - Round to the nearest .45 or 0.95

As an extension of the above, we can use a similar technique to round to the nearest 0.45 or 0.95. Again, this is a requirement that can occur with pricing apps.

To implement this function, we would round to the nearest 0.50 (as highlighted earlier), and we would then subtract 0.05.

//This rounds to 6.22 to 5.95
(Round(6.22/0.5,0)*0.5) - 0.05

//This rounds to 6.32 to 6.45
(Round(6.32/0.5,0)*0.5) - 0.05

8 - Round up to the next even number

In cases where we need to round up to the next even number, we can do so by rounding up to the next 2 (because 2 is always even).

The formula beneath demonstrates this method. Note that with Excel, we would call the Even function to carry out this task. Because there is no equivalent Power Apps function, this technique provides an alternative.

//This rounds 55 up to 56
RoundUp(55/2,0)*2

9 - Round to the next odd number

As an extension of the above example, we can round up to the next odd number by 'flooring' the input value divided by two (as highlighted above), multiplying this output by 2, and adding 1. 

//This rounds 82 to 83
(2 * RoundDown(82/2, 0)) +1

//This rounds 81 to 81
(2 * RoundDown(81/2, 0)) +1
Note that Excel provides a function called Odd to carry out this same task. The Excel Odd function rounds negative numbers away from zero. If we want to replicate this behaviour, we would need to subtract one from negative input values, rather than add one

10 - Remove decimal values

Finally, there is often the requirement to get rid of the digits after the decimal place or to get just the integer part of a decimal value. We can accomplish this by calling the Trunc function. 

//This returns the value 4
Trunc(4.99)

Conclusion

When we work with numbers with Power Apps, a common requirement is to round numbers. This post summarised the formula to carry out some of the most common rounding requirements.
Previous
Next
Related posts