There are a number of different functions that can be used for rounding numbers in Excel. Rounding is useful when you need to limit the number of decimals to match data in a lookup table, or size components to the next available size.
The most basic way to perform rounding in Excel is to use the INT function. This function essentially just chops the decimals off the end of the number and leaves only the integer portion. The syntax is really simple:
INT(number)
It’s just INT and the number you’d like to convert to an integer.
A little more sophisticated way of doing this is to use the round function, which uses the standard rules for rounding. If the number is less than 5 it rounds down, if the number is greater than 5 it rounds up. The syntax for that function looks like this:
ROUND(number, num_digits)
Where
- number is the number you want to round, and
- num_digits is the number of digits to which you want to round the number.
In other words, if you want to round a number to 2 decimal places, the argument for num_digits would be 2.
Similarly, there is the ROUNDUP function if you want to control the rounding option so that it always rounds “up” or away from zero. The syntax for this function is exactly the same as it is for ROUND:
ROUNDUP(number, num_digits)
There is also a ROUNDDOWN function, which forces a number to be rounded down or toward zero. By now, you’re familiar with the syntax:
ROUNDDOWN(number, num_digits)
But the most useful rounding function for engineers that I want to mention in this post is the MROUND function. This function is useful a lot of times in engineering because it rounds a number to a specified multiple. The syntax for the function looks like this:
MROUND(number, multiple)
Where
- number is the number to be rounded, and
- multiple is the multiple to which you want to round the number. That could be any value such as 10, 5, 2, 0.25 or whatever is appropriate.
Rounding Numbers in Excel – An Example
An example we could look at of rounding numbers in Excel is a flow calculation in a pipe where we have a known flow rate and we want to limit the velocity in the pipe to some value.
The spreadsheet converts flow from gallons per minute to cubic inches per second, calculates the required area, and also the required diameter.
But pipe is not going to be available with a diameter of exactly 4.4 inches, so we need to round to the next available size.
Of course in this case we want to round up to ensure that we don’t exceed the maximum velocity specification.
There are a few different ways to do this:
First, we can use the INT function by typing INT and selecting the value. But since the INT function is just going to chop the decimals off and leave the integer, we have to add 1.
=INT(C9)+1
Of course, we could also use the ROUNDUP function and round to the nearest inch by entering 0 for the number of digits.
=ROUNDUP(C9,0)
But what if we could get pipe in increments of half an inch?
In this situation, we could use the MROUND function, select the calculated required diameter as the number argument, and for a multiple type 0.5.
=MROUND(C9,0.5)
That returns a value of 4.5
