How to Use ROUND Function in Excel (With 9 Examples)

In certain circumstances, we prefer the rounded or the approximate number rather than the exact number for making communication easier. The ROUND function returns a rounded numerical value.

In this tutorial, I’ll discuss the basics of the ROUND function. More importantly, nine real-life examples will be shown with proper explanations. So that you can adjust the formula in your dataset.

ROUND Function in Excel (Quick View)

ROUND Function in Excel (Quick View)

Download Excel Workbook

Excel ROUND Function: Syntax & Arguments

Firstly, you’ll see the syntax and argument of the function. If you insert the function after entering equal sign (=), you’ll see the following figure.

The ROUND Formula

Summary

The ROUND function rounds a number based on the provided number of digits. Rounding up or rounding down is possible with the function.

Syntax

=ROUND (number, num_digits)

Return Value

A rounded numerical value

Arguments

Argument Required or Optional Value
number required The number to round
num_digits required The number of digits to round the numeric argument to.

Note. 

  1. The ROUND function rounds down (when the number of digits is 1-4) and up (when the number of digits is 5-9). You may utilize the ROUNDUP function to always round up. On the other hand, you can use the ROUNDDOWN function to always round down a number.
  2. The number of digits is a significant argument while using the ROUND function. The output found by using the function depends on the number of digits that are shown in the following table.
Number of Digits Forms of Rounding
>0 Rounds to the decimal point
0 Rounds to the nearest integer
<0 Rounds to the nearest 10, 100, etc

How to Use the ROUND Function in Excel (With 9 Examples)

Example 1: Using ROUND Function When the Number of Digits is Positive

Imagine the unit price of some products is given, you have to round the unit price based on the number of digits. As the number of digits is positive, you’ll get a rounded number to the decimal point. The formula will be:

=ROUND(C5,D5)

Here, C5 is the unit price whereas D5 is the number of digits.

Using ROUND Function When the Number of Digits is Positive

Example 2: Using ROUND Function When the Number of Digits is Negative

Again, if the number of digits is negative, you’ll get the rounded price to the nearest multiple of 10, 100, 1000, etc.

=ROUND(C5,D5)

Using ROUND Function When the Number of Digits is Negative

Example 3: Using ROUND Function to Get the Nearest Integer

If the number of digits is equal to zero, the ROUND function rounds the number to the nearest integer.

=ROUND(C5,0)

Using ROUND Function to Get the Nearest Integer

Example 4: Rounding A Number to Two Decimal Places

Sometimes, you may be told to round a number to two decimal points. Just use the -2 as the number of digits.

=ROUND(C5,2)

Rounding A Number to Two Decimal Places

Example 5: Using ROUND Function to Get A Specific Value

If you guys need to determine a specific rounded value, e.g. to the nearest 0.99, you can use the ROUND function to get that value.

Just insert the following formula.

=ROUND(C5,0)-0.01

Here, the ROUND(C5,0) rounds to the 90. After subtracting 0.01, you’ll get the desired number.

Using ROUND Function to Get A Specific Value

Example 6: Rounding A Number to the Nearest 10/100/1000

i. Rounding to the Nearest 10

If you wish to find the rounded number to the nearest multiple of 10, the number of digits will be -1.

=ROUND(C5,-1)

Rounding to the Nearest 10

ii. Rounding to the Nearest 100

Again, for finding the rounded number to the nearest multiple of 100, the number of digits will be -2.

=ROUND(C5,-2)

Rounding to the Nearest 100

iii. Rounding to the Nearest 1000

Furthermore, you can calculate the rounded number to the nearest 1000 or multiple of that. In such a situation, the number of digits will be -3.

=ROUND(C5,-3)

Rounding to the Nearest 1000

Example 7: Rounding Time in Excel Using ROUND Function

You can also use the ROUND function for rounding time to hours like rounding the number.

As Excel stores the dates and time as serial numbers, the function calculates the time as the serial number. You may use the Format Cells (press CTRL+1) to show the number as a time.

i. Rounding to the Nearest Hour

As you know, a day has 24 hours. Thus the formula will be like the following.

=ROUND(D5*24,0)/24-INT(D5)

Here, the INT function is used to subtract the value of dates. If you are interested to know more uses of the function, please visit INT Function.

Rounding to the Nearest Hour Using ROUND Function

ii Rounding to the Nearest 15 Minutes

Besides, you can round time to the nearest 15 minutes. Needless to say that a day has 96 times of 15 minutes. So that the formula will be:

=ROUND(C5*96,0)/96

Rounding to the Nearest 15 Minutes Using ROUND Function

Example 8: Rounding the Total of Two Numbers Using the ROUND Function

In some cases, you may need to consider two or more numbers (e.g. price in June and price in July) for rounding. You may use the following formula in case of finding a rounded number of the total value of the numbers.

=ROUND(C5+D5,0)

Rounding the Total of Two Numbers Using the ROUND Function

Example 9: Rounding the Quotient of Two Numbers Using the ROUND Function

Once more, you may have to calculate the rounded number in case of the quotient of two numbers. The formula will be:

=ROUND(D5/C5,0)

Rounding the Quotient of Two Numbers Using the ROUND Function

Common Errors While Using the ROUND Function

Common Errors When they show
#VALUE! – Occurs when the text is inserted as input

Conclusion

This is how you can apply the ROUND function to get the row number. If you have an interesting and unique method of using the ROUND function, please share it in the comments section below.

Thanks for being with me.


Further Readings

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo