Excel Rounding Formula – Once in a while we need to round a number after a decimal number in certain places/values. For example, rounding a number to two places or two digits behind a comma.
How to round numbers in excel?
Microsoft Excel has provided several excel functions or formulas that we can use for this need to round decimal numbers. The excel functions include the ROUND , ROUNDDOWN , and ROUNDUP functions .
For more details about how to round numbers behind a comma in Excel, see the explanation of the excel rounding formula below.
Rounding
Before discussing the rounding formula in Excel, we first discuss how to round numbers or numbers in general.
Definition of Rounding Numbers
Rounding a number is reducing the value of a number in mathematical calculations by rounding the digits of the number that are right at the rounded value and changing the digits to the right of the rounding number to zero (0).
The resulting numerical values will certainly be less or less accurate, but in certain situations this is usually preferable because it makes the numbers easier to work with.
Example of rounding For example rounding the number 1.234 to the nearest hundreds to 1,200 or to the nearest tens to 1,230. Another example is rounding the decimal number 12.3123 to the two digits behind the comma to 12.31.
Rounding Rules
What are the general rules for rounding numbers or numbers?
In general, the rules for rounding numbers are as follows:
- If the number to the right of the rounding place is 5,6,7,8 or 9 then round up (Rounding place number +1).
- If the number to the right of the rounding place is 0.1,2,3, or 4 or then round down (Fixed place of rounding).
- Change all numbers to the right of the rounding place to 0.
- The 0 to the right of the comma or decimal can be ignored or discarded.
Rounded place value means the last digit to be retained.
Example of Rounding Numbers
Examples of the form of rounding numbers for example:
- The number 8,765,432 if rounded to the nearest thousand becomes 8,765,000
- The number 2,345,678 when rounded to the nearest thousand becomes 2,346,000
- The decimal number 87.65432 when rounded to two places behind the comma becomes 87.65000 or 87.65.
- The decimal number 23,45678 when rounded to two places after the comma becomes 23,46000 or 23.46.
In Microsoft Excel, the functions or formulas commonly used to round numbers are the Round, Roundup and Rounddown functions which we will discuss in the next section.
Excel ROUND function
The ROUND function is a Microsoft Excel function that is used to round a decimal number to a certain number of digits.
Rounding with the ROUND function uses a general mathematical rule, namely if the number behind the comma is more than or equal to (>=) 5 it will be rounded up and vice versa if it is less than 5, it will be rounded down.
How to Use Excel’s ROUND Function
How to use the Round function in the excel formula is as follows:
Code:
ROUND(Number; Number of Digits)
The arguments of the Excel Round function include:
- Number
Is a decimal number or fraction that will be rounded to certain places.
- NumberDigit
Represents the number of digits rounded to be applied to the decimal number.
Excel ROUND Formula Example
When using the Excel Round formula, if the Sum ofDigits is greater than 0 (zero) , then the decimal number is rounded to the specified number of decimal places.
If Sum of Digits is 0 , the decimal number will be rounded to the nearest whole number (without decimal/fraction value).
If the Number of digits is less than 0 (negative) , the decimal number will be rounded to the left of the decimal point.
Excel’s ROUNDUP function
The ROUNDUP function is an excel function that is used to round a decimal number up to a certain number of digits.
With the RoundUp function, the number after the comma will always be rounded up away from the number 0.
How to Use Excel’s ROUNDUP Function
In the Excel formula, the RoundUp function follows the following writing rules:
Code:
ROUNDUP(Number;Number of Digits)
Similar to the ROUND function, the RoundUp function has two arguments:
- Number
Represents the decimal number or fraction that you want to be rounded up.
- NumberDigit
Represents the number of rounded digits that you want to apply to decimal rounding numbers.
Example of the Excel ROUNDUP Formula
The RoundUp and Round functions will both round numbers. It’s just that with the RoundUp function any number to be rounded will always be rounded up.
When using the RoundUp function or formula to round a number, if the NumberDigits argument is 0 , then the number is rounded up to the nearest integer (without a comma/decimal). So the final result will be the same as the ROUND function in excel.
If TotalDigits is less than 0 or has a negative value , the decimal number will be rounded up to the left of the decimal point.
As seen in the example above, by giving a negative value of three (-3) to the number of digits, this function can also be used to round numbers to multiples of 1000 or round numbers to the nearest tens of certain multiples as needed.
Excel’s ROUNDDOWN function
The ROUNDDOWN function is an excel function that is used to round a decimal number down to a certain significance.
With the roundDown function, decimal numbers will always be rounded down to near 0 (zero).
How to Use Excel’s ROUNDDOWN Function
How to use the RoundDown function in the excel formula is as follows:
Code:
ROUNDDOWN(Number; Number of Digits)
Similar to the ROUND and RoundUp functions, the RoundDown function also has two arguments that must be filled in when used to create an excel formula:
- Number
Represents a decimal number or fraction to be rounded down.
- NumberDigit
This is the number of rounding digits that will be applied to the rounding number.
Example of the Excel ROUNDDOWN Formula
If using the Rounddown formula with a Sum of Digits value more than 0 (zero) then the decimal value will always be rounded down to close to 0 (zero) as many decimal places as specified.
If SumDigits is 0 , the decimal number will be omitted. The RoundDown function in this case will round the decimal number down to the nearest whole number.
If NumberDigits is negative or less than 0 , the number is rounded down to the left of the decimal point.
After reading the tutorial for rounding decimal numbers in Microsoft Excel above, I’m sure you’re not confused anymore to choose which Excel function or formula is more appropriate to use according to your individual needs.
Thus the excel function that we can use to round numbers to a certain value. Hope it is useful.
If there is still something that needs to be asked, please submit it in the comments column provided.
