Rounding Functions in Excel

Excel has several functions for rounding numbers.

The INT function has only one argument, the number to be rounded. This function effectively rounds down to the nearest integer.

The ROUND function is a little more sophisticated. It uses the standard rules for rounding: if a number is less than 5 it rounds down, if it’s 5 or more it rounds up. The syntax is as follows:

ROUND(number, num_digits)

number: the number to be rounded

num_digits: the number of digits to round the number argument

For example, to round the number 3.14159 to two decimal places, enter in =ROUND(3.14159,2).

There are two functions that allow you to control the direction of rounding. ROUNDUP will always round up, away from zero; ROUNDDOWN will always round down, toward zero. Both of these functions take the same arguments as ROUND above, so you can choose the number of digits to round to.

The MROUND function is useful in engineering because it rounds a number to a specified multiple. Its syntax is:

MROUND(number, multiple)

number: the number to be rounded

multiple: the multiple to which the number will be rounded (i.e. 10, 5, 2, 0.25, etc.)

Worksheet 03k contains a flow calculation for a pipe with a known flowrate. The velocity in the pipe cannot exceed 120 in/sec. Calculations have already been done to determine the required diameter. However, pipe won’t be available with a diameter of exactly 4.4 in, so we need round to the next available size.

In this case, it’s necessary to round up to ensure the maximum velocity isn’t exceeded. There are a few different ways to do this.

We can use the INT function, but this function will round down to 4. To avoid exceeding the desired velocity, simply add 1 to the pipe diameter. Enter =INT(C9)+1 into the first cell for pipe diameter:

The ROUNDUP function will ensure that we round up. To round to a whole number, enter 0 for the number of digits: =ROUNDUP(C9,0)

What if pipe is available in increments of half an inch? The MROUND function will allow us to round to the nearest half inch. Use 0.5 for the multiple: =MROUND(C9,0.5)

This returns a value of 4.5, the nearest multiple of 0.5.

One thing to note is that the ROUND function can be used to round to the tens place, hundreds place, or any higher place. Simply use a negative number in the second argument – for the tens place, -1; for the hundreds place, -2; and so on. The magnitude of this number will be equal to the number of zeroes in your final rounded number.

For example, try rounding the flow rate to the hundreds place. Choose a cell and enter =ROUND(C7,-2). To round to the tens place, use -1 as the second argument. The ROUNDUP and ROUNDDOWN functions work similarly: use a negative number as the second argument to round to a place left of the decimal.