How to use Round in MS Excel

Round  |  RoundUp  |  RoundDown | Even and Odd | Nearest Multiple

This chapter illustrates three functions to round numbers in Excel. The ROUND, ROUNDUP and ROUNDDOWN function.

Before your start: if you round a number, you lose precision. If you don’t want this, show fewer decimal places without changing the number itself.

Round

1. Round a number to two decimal places.

Note: 1, 2, 3, and 4 get rounded down. 5, 6, 7, 8, and 9 get rounded up. In this example, 114.7211, 114.7221, 114.7231 and 114.7241 get rounded down to 114.72 and 114.7251, 114.7261, 114.7271, 114.7281 and 114.7291 get rounded up to 114.73.

2. Round a number to one decimal place.

3. Round a number to the nearest integer.

4. Round a number to the nearest 10.

5. Round a number to the nearest 100.

RoundUp

The ROUNDUP function always rounds a number up (away from zero). For example, round a number up to one decimal place.

RoundDown

The ROUNDDOWN function always rounds a number down (toward zero). For example, round a number down to the nearest integer.

Even and Odd

Even  |  Odd  |  Even or Odd?

This example illustrates the EVEN and the ODD function in Excel. It also shows you how to determine if a number is even or odd.

Even

1. Rounds a positive number up to the nearest even integer.

2. Rounds a negative number down to the nearest even integer.

Odd

1. Rounds a positive number up to the nearest odd integer.

2. Rounds a negative number down to the nearest odd integer.

Even or Odd?

One way to determine if a number is even or odd is to use the MOD function. The MOD function gives the remainder of a division.

1. Even numbers divided by 2 always give a remainder of 0. For example, 28 is divided by 2 (exactly 14 times) to give a remainder of 0. As a result, the IF function returns Even.

2. Odd numbers divided by 2 always give a remainder of 1. For example, 29 is divided by 2 (14 times) to give a remainder of 1. As a result, the IF function returns Odd.

Nearest Multiple

Mround  |  Ceiling  |  Floor

This example illustrates three functions to round numbers to a multiple of x in Excel. The MROUND, CEILING and the FLOOR function.

Mround

1. For example, round a number to the nearest multiple of 10.

2. For example, round a number to the nearest multiple of 5.

Ceiling

The same as MROUND but rounds up.

Floor

The same as MROUND but rounds down.

Chop off Decimals

Int  |  Trunc

This example illustrates two functions to chop off decimals in Excel. The INT and the TRUNC function.

Int

The INT (Integer) function rounds a number down to the nearest integer.

Conclusion: the INT function only chops off decimals from positive numbers.

Trunc

The TRUNC (Truncate) function always chops off decimals.

The TRUNC function can keep any number of decimals and chop off the rest.

Leave a Reply

Your email address will not be published.