Basic Rounding of Numbers in Excel Formula

Rounding is a useful skill to make numbers simpler and easier to work with, without losing much accuracy. In Excel, there are several functions that can help you round numbers to a specified number of digits, either to the right or left of the decimal point. In this article, we will explain the basic theory and procedures of rounding in Excel, and show you some examples and scenarios of how to use the rounding functions in practice.

Rounding is the process of reducing the number of digits in a number, while keeping it close to the original value. There are different ways of rounding, depending on the desired level of precision and the direction of rounding. The most common way of rounding is to round to the nearest integer or decimal place. For example, if we want to round 3.14159 to two decimal places, we can use the following rule:

• If the digit in the third decimal place is 5 or more, round up the digit in the second decimal place by one, and drop the rest of the digits. For example, 3.14159 becomes 3.15.
• If the digit in the third decimal place is 4 or less, keep the digit in the second decimal place as it is, and drop the rest of the digits. For example, 3.14149 becomes 3.14.

This way of rounding is also called rounding half up, because it rounds up when the digit to be dropped is 5, which is half of 10. There are other ways of rounding, such as rounding half down, rounding half to even, rounding half to odd, rounding up, rounding down, etc. Each of these methods has its own rules and applications, which we will discuss later.

Procedures of Rounding in Excel

In Excel, there are several functions that can help you round numbers to a specified number of digits, either to the right or left of the decimal point. The most commonly used function is the ROUND function, which follows the rounding half up rule. The syntax of the ROUND function is:

=ROUND(number, num_digits)

Where number is the number you want to round, and num_digits is the number of digits you want to keep. For example, =ROUND(3.14159, 2) returns 3.15.

The ROUND function can also round to the left of the decimal point, by using a negative value for num_digits. For example, =ROUND(314.159, -2) returns 300.

There are other functions that can round numbers in different ways, such as:

• ROUNDUP: always rounds up, regardless of the digit to be dropped. For example, =ROUNDUP(3.14159, 2) returns 3.15, and =ROUNDUP(3.14149, 2) returns 3.15 as well.
• ROUNDDOWN: always rounds down, regardless of the digit to be dropped. For example, =ROUNDDOWN(3.14159, 2) returns 3.14, and =ROUNDDOWN(3.14149, 2) returns 3.14 as well.
• MROUND: rounds to the nearest multiple of a given number. For example, =MROUND(3.14159, 0.05) returns 3.15, and =MROUND(314.159, 10) returns 310.
• FLOOR: rounds down to the nearest multiple of a given number. For example, =FLOOR(3.14159, 0.05) returns 3.1, and =FLOOR(314.159, 10) returns 310.
• CEILING: rounds up to the nearest multiple of a given number. For example, =CEILING(3.14159, 0.05) returns 3.15, and =CEILING(314.159, 10) returns 320.
• INT: rounds down to the nearest integer. For example, =INT(3.14159) returns 3, and =INT(-3.14159) returns -4.
• TRUNC: truncates a number to a specified number of digits, without rounding. For example, =TRUNC(3.14159, 2) returns 3.14, and =TRUNC(-3.14159, 2) returns -3.14.
• EVEN: rounds a number up to the nearest even integer. For example, =EVEN(3.14159) returns 4, and =EVEN(-3.14159) returns -4.
• ODD: rounds a number up to the nearest odd integer. For example, =ODD(3.14159) returns 5, and =ODD(-3.14159) returns -5.

Examples of Rounding in Excel

To illustrate how to use the rounding functions in Excel, let’s look at some examples and scenarios with real data. Suppose we have the following table of sales data for a company:

We can use the rounding functions to perform various calculations and analyses on this data. For example:

• To calculate the average price per product, we can use the ROUND function to round the result to two decimal places. The formula in cell G2 is:

=ROUND(AVERAGE(B2:B6), 2)

The result is 12.79.

• To calculate the total sales for each product, we can use the ROUNDUP function to round the result to the nearest dollar. The formula in cell D2 is:

=ROUNDUP(B2*C2, 0)

The result is 1300. We can copy this formula down to the other cells in column D.

• To calculate the percentage of total sales for each product, we can use the ROUNDDOWN function to round the result to one decimal place. The formula in cell E2 is:

=ROUNDDOWN(D2/$D$7, 1)

The result is 0.2. We can copy this formula down to the other cells in column E.

• To calculate the sales tax for each product, we can use the MROUND function to round the result to the nearest 0.05. The formula in cell F2 is:

=MROUND(D2*0.1, 0.05)

The result is 130. We can copy this formula down to the other cells in column F.

• To calculate the net sales for each product, we can use the FLOOR function to round the result down to the nearest 10. The formula in cell G2 is:

=FLOOR(D2-F2, 10)

The result is 1170. We can copy this formula down to the other cells in column G.

• To calculate the bonus for each product, we can use the CEILING function to round the result up to the nearest 100. The formula in cell H2 is:

=CEILING(G2*0.05, 100)

The result is 100. We can copy this formula down to the other cells in column H.

The final table looks like this: