EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Excel Excel Resources Maths Function in Excel Excel Rounding

Excel Rounding

Madhuri Thakur
Article byMadhuri Thakur

Updated May 8, 2023

Rounding in Excel

Rounding in Excel

Rounding in Excel alters the actual value in a cell. In simple words, rounding means eliminating unwanted numbers and keeping the nearest numbers. A simple example will make you understand better. The rounding value of 99.50 roundings is 100. We have different kinds of rounding functions available to make rounding as per requirements.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

How to Round Numbers in Excel?

Let’s understand how to Round Numbers in Excel using various functions, with examples explained below.

You can download this Rounding Numbers Excel Template here – Rounding Numbers Excel Template

ROUND Function

Round is the first function that strikes us when considering rounding in Excel. The round function makes rounding of numbers as per the number of digits we want to make rounding.

Syntax of ROUND is =ROUND(number, number_digit). Find the below screenshot for reference.

Round Function Syntax

Suppose there is a number 45.78956 in cell A2, and we wish to have only 2 digits after the decimal point. Apply the ROUND function with number_digits as 2.

Rounding in Excel Example 1

Observe the screenshot .78956 converted to 79 as we mentioned 2 in the number of digits after the decimal point; hence it makes rounding to nearer number .79

ROUNDUP Function

Roundup name itself suggests that this function rounds the number upwards only.

The syntax for ROUNDUP Function is =ROUNDUP(number, number_digits). Find the below screenshot for reference.

ROUNDUP Function Syntax

The number of digits it will take upward depends on the number of digits we give in the “number_digit” during the input roundup formula.

Rounding in Excel - Roundup Function 1

In the above screenshot, there is only one value after the decimal point, and we also give 1 in the formula; hence, the results are not changed. Suppose we increase the number of digits after the decimal point as below.

Rounding in Excel - Roundup Function 2

23.436 rounded as 23.5 as after decimal there should be only one digit; hence it makes rounding to its upper value and make as 23.5

ROUNDDOWN Function

The syntax for ROUNDUP Function is =ROUNDUP(number, number_digits). It is quite the opposite of ROUNDUP, as it rounds to a lower value and displays the number of digits the user mentions after the decimal point. Find the below screenshot for reference.

ROUNDDOWN Function Syntax

Consider the same example used for ROUNDUP; there, we got the result as 23.5, and here we got the result as 23.4 as it is rounded to the lower value.

Rounding in Excel - Rounddown Function 1

MROUND Function

MROUND function performs rounding, which may go up or down depending on the given multiple in the formula.

The syntax of MROUND is =MROUND(number, multiple). Find the below screenshot for reference.

MROUNDUP function syntax

In the below example, we use a multiple of 5; hence the results should be multiple of 5. 50 is the nearest multiple value of 5 for 51.789

Rounding in Excel - MROUND Function 2

In the example below, 55 is the nearest multiple value of 5 for 53.789.

Rounding in Excel - MROUND Function 1

FLOOR Function

The floor is another function that helps to round off numbers down. The syntax of the FLOOR Function is =FLOOR(number, significance). Find the below screenshot for reference.

FLOOR Function syntax

We want to do rounding; significance is the number we want to do the multiple. In the below example, we want the multiple of 3; hence we have given 3 in the place of significance. 9 is the nearest multiple of 3 for the value 10.20.

Rounding in Excel - FLOOR Function 1

We can perform this for negative values too; as told earlier, it helps to down the number; hence it gives the result as -12 because if we reduce -10.20 further, -12 is the nearest multiple of 3; therefore, it is -12.

Rounding in Excel - FLOOR Function 2

CEILING Function

CEILING is another function of rounding up the numbers. The syntax for the CEILING Function is =CEILING(number, significance). Find the below screenshot for reference.

CEILING Function syntax

It is similar to the FLOOR Function, but the difference is that it is used for Round Down, which is used for Roundup.

In the below example, we have given the significance as 4, which means we need a rounding value greater than 21.60 and should be a multiple of 4. 24 is greater than 21.6, and it is a multiple of 4.

Rounding in Excel - CEILING Function Example

ODD Function

The ODD function is one of the rounding functions that help to find the nearest odd number of the given number.

The syntax for ODD is =ODD(number).

Odd Syntax

In the below example, we applied the ODD function for number 38.567. 39 is the nearest Odd number available hence it gave the result as 39.

Rounding in Excel - Odd Function Example

EVEN Function

Even function works quite similarly to the ODD function, but the only difference is that it gives the nearest EVEN numbers after rounding.

Syntax of EVEN is =EVEN(number).

EVEN Function syntax

Find the below example for reference. The nearest number for 38.567 is 40.

Rounding in Excel - EVEN Function Example

TRUNC Function

TRUNC is another function available for rounding numbers. The syntax for TRUNCATE Function is =TRUNC(number, [number_digits])

TRUNC Funcion syntax

As through the number of digits, it will eliminate numbers after the decimal point. It will round to the nearest integer if we do not provide any number.

Rounding in Excel - TRUNC Funcion 1

Observe the above screenshot; TRUNC is applied without several digits. 18 is the nearest integer of 18.2543.

Now we will see how it will work if we give the number of digits as 2 for the same example.

Rounding in Excel - TRUNC Funcion 2

It keeps only two values after the decimal point and truncates the other digits. If we give the number a positive number, it will truncate from the right side of the decimal point. If we give negative values and negative numbers, it will truncate from the left side of the decimal point.

INT Function

INT is another function that rounds the value down to the nearest integer. Syntax of INT is =INT(number)

INT Function syntax

If we use a positive value or negative value, it will always be rounding down the value. Even if we use 10.7; still, it downs the value to the nearest integer, 10.

Rounding in Excel - INT Function 1

Perform the same for the negative value now. Observe below it further reduces the value of 10.70 to -11.

Rounding in Excel - INT Function 2

ROUNDING for TIME

Rounding can apply to time also. We will see a few examples of rounding the time. The syntax for rounding the time is =CEILING(time, “1:00”). Find the below screenshot for reference.

Rounding in Excel - Round time 1

Observe the above formula; similarly, I applied for FLOOR and CEILING.

We have to follow a different format if we want the rounding nearer to 20 minutes like that. Please find the below screenshot for rounding to the nearest 20 minutes.

Rounding in Excel - Round time 2

Observe the formula; the moment we give the time, it will ask for hours, minutes, and seconds; hence we can give the nearest time details for rounding. Below is the syntax for that.

Round time formula

I hope you understand how to apply rounding formulas differently per the situation.

 Time function syntax

Things to Remember While Using Rounding in Excel

  • ROUNDUP and ROUNDDOWN can be applied for negative numbers too.
  • If we use positive numbers and negative significance, the results will be #NUM, like an error message for CEILING and FLOOR Functions.
  • Rounding can also be applied to time while working with the time input formula as per minutes and hours.

Recommended Articles

This has been a guide to Rounding in Excel. Here we discussed How to Round Numbers, Time using functions like ROUND, ROUNDDOWN, CEILING, FLOOR, INT, TRUNC, etc., in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –

  1. Excel ROUNDUP Function
  2. ROUND Excel Function
  3. Excel ROUNDDOWN Function
  4. FLOOR Excel Function
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Courses
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Rounding Numbers Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

EDUCBA

Download Rounding Numbers Excel Template

EDUCBA

डाउनलोड Rounding Numbers Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW