Excel Round Functions are used on daily basis by every Excel users.
In this post, we will discuss various Excel round functions, their syntaxes, and their uses.
You can download the Excel workbook from the link at the bottom which contains the examples of Excel round functions.
- ROUND
- ROUNDUP
- ROUNDDOWN
- MROUND
- INT
- TRUNC
- CEILING
- FLOOR
- EVEN
- ODD
- FIXED
- Custom Excel Round Function
Round Function: Rounds a number to specified decimal points
Syntax: ROUND(number, num_digits)
Where number is the number that you want to round, and 'num_digits' is the number of digits to which you want to round the number argument.
Example: 5.144523 to 5.1 by using =ROUND(C5,1)
Roundup Function: It rounds a number up
Syntax: ROUNDUP(number, num_digits)
Where 'Number' is any real number that you want rounded up, and 'Num_digits' is the number of digits to which you want to round number.
Example: 5.144523 to 5.2 by using =ROUNDUP(C6,1)
Rounddown Function: It rounds a number down
Syntax: ROUNDDOWN(number, num_digits)
Where 'Number' is any real number that you want rounded down, and 'Num_digits' is the number of digits to which you want to round number.
Example: 5.164523 to 5.1 by using =ROUNDDOWN(C7,1)
MROUND Function: This formula rounds to nearest multiple of specified number.
Syntax: MROUND(number, multiple)
Where 'Number' is the value to round and 'Multiple' is the multiple to which you want to round the number.
Example: 1,255,050 to 1,260,000 by using =MROUND(C8,10000)
INT Function: Rounds down to nearest integer.
Syntax: INT(number)
Where, 'Number' is the real number you want to round down to an integer.
Example: 9.2 to 9 by using =INT(C9)
TRUNC Function: It returns only integer portion of the number.
Syntax: TRUNC(number, [num_digits])
Where, 'Number' is the number you want to truncate and 'Num_digits' is Optional, a number specifying the precision of the truncation. Note that the default value for num_digits is 0 (zero).
Example: 55.11 to 55 by using =TRUNC(C10)
You must have noticed that TRUNC and INT are similar in that both return integers. TRUNC removes the fractional part of the number. INT rounds numbers down to the nearest integer based on the value of the fractional part of the number. INT and TRUNC are different only when using negative numbers: TRUNC(-5.3) returns -5, but INT(-5.3) returns -6 because -6 is the lower number.
CEILING Function: This formula rounds up a number to nearest multiple of 1,10,100.
Syntax: CEILING(number, significance)
Where, 'Number' is the value you want to round and 'Significance' is the multiple to which you want to round.
Example: 4,990,500 to 5,000,000 by using =CEILING(C11,10000)
FLOOR Function: This formula rounds down a number to nearest multiple of 1,10,100.
Syntax: FLOOR(number, significance)
Where 'Number' is the numeric value you want to round and 'Significance' is the multiple to which you want to round.
Example: 4,990,500 to 4,990,000 by using =FLOOR(C12, 10000)
EVEN Function: This formula rounds a number up to the nearest even integer.
Syntax: EVEN(number)
Where 'Number' is the value to round.
Example: 5 to 6 by using =EVEN(C13)
ODD Function: Gives next odd number
Syntax: ODD(number)
Where 'Number' is the value to round.
Example: 4 to 5 by using =ODD(C14)
FIXED Function: Rounds and converts to text format (with commas if you want)
Syntax: FIXED(number, [decimals], [no_commas])
Where 'Number' is the number you want to round & convert to text and 'Decimals' is Optional. The number of digits to the right of the decimal point. 'No_commas' another optional - a logical value that, if TRUE, prevents FIXED from including commas in the returned text.
Example: 5,102.45 to 5102.5 by using =FIXED(C15,1,TRUE)
You will notice that same result can be achieved by using more than one formula. There is no right or wrong, it all you about your comfort level with a formula also which formula comes to mind first.
Custom Excel Round Function:
Many a times we need custom rounding, which cannot be achieved by the formulas listed above. In each situation we will have a different requirement. For example for financial reporting we occasionally convert 5,254,265 to as 5.3 M.
We can convert 5,254,265 to 5.3 M using formula =ROUND(C20/1000000,1) & " M"
We can also do a custom formatting to achieve the same result. Right click on the cell and go to format cell. In the format cell dialog box, go to Custom and type #.#,, " M" and then press enter. The job is done, result is 5.3 M.
Hope you enjoyed this post on Excel round function; if you have any comment, let me know through the comment section below.
Also you can download the Excel Round Function Workbook for free from the below link.
Are you someone involved with real estate feasibility?
We are excited to launch the next generation of real estate feasibility software by April 2023.
claim your 15% early-bird discount
Know more about the product features
