Rounding Functions in Excel

	CEILING	Rounds a number away from zero (i.e. rounds a positive number up and a negative number down), to a multiple of significance
	CEILING.PRECISE	Rounds a number up, regardless of the sign of the number, to a multiple of significance (New in Excel 2010)
	CEILING.MATH	Rounds a number up to the nearest integer or to the nearest multiple of significance (New in Excel 2013)
	FLOOR	Rounds a number towards zero, (i.e. rounds a positive number down and a negative number up), to a multiple of significance
	FLOOR.PRECISE	Rounds a number down, regardless of the sign of the number, to a multiple of significance (New in Excel 2010)
	FLOOR.MATH	Rounds a number down, to the nearest integer or to the nearest multiple of significance (New in Excel 2013)
	ROUND	Rounds a number up or down, to a given number of digits
	MROUND	Rounds a number up or down, to the nearest multiple of significance
	ROUNDDOWN	Rounds a number towards zero, (i.e. rounds a positive number down and a negative number up), to a given number of digits
	ROUNDUP	Rounds a number away from zero (i.e. rounds a positive number up and a negative number down), to a given number of digits
	EVEN	Rounds a number away from zero (i.e. rounds a positive number up and a negative number down), to the next even number
	ODD	Rounds a number away from zero (i.e. rounds a positive number up and a negative number down), to the next odd number
	INT	Rounds a number down to the next integer
	TRUNC	Truncates a number towards zero (i.e. rounds a positive number down and a negative number up), to the next integer.

Trunc() Function

The Excel TRUNC function returns a truncated number based on a given number of digits. For example, TRUNC(4.9) will return 4, and TRUNC(-3.5) will return -3. The TRUNC function does no rounding, it simply truncates as specified.

Syntax

The syntax for the TRUNC function in Microsoft Excel is:

=TRUNC( number, [digits] )

Parameters or Arguments

number

The number to truncate.

digits

Optional. It is the number of decimal places to display in the resulting truncated number. If this parameter is omitted, the TRUNC function will assume 0.

Trunc Function Examples

The following spreadsheet shows several examples of the Excel Trunc function:

Formulas:

	A	B
1	99.999	=TRUNC( A1, 1 )
2	99.999	=TRUNC( A2, 2 )
3	99.999	=TRUNC( A3 )
4	99.999	=TRUNC( A4, -1 )
5	-99.999	=TRUNC( A5, 2 )
6	-99.999	=TRUNC( A6, -1 )

Results:

	A	B
1	99.999	99.9
2	99.999	99.99
3	99.999	99
4	99.999	90
5	-99.999	-99.99
6	-99.999	-90

The example in cell B3 of the above spreadsheet shows that, when the [num_digits] argument is omitted, it takes the default of 0. I.e. the supplied number is truncated to an integer.

Note

Ø  A positive [num_digits] value specifies the number of digits to the right of the decimal point.

Ø  If [num_digits] is 0 (or is omitted), the supplied number is truncated to the nearest integer.

Ø  A negative [num_digits] value specifies the number of digits to the left of the decimal point.

References

http://www.excelfunctions.net/Excel-Trunc-Function.html

https://www.techonthenet.com/excel/formulas/trunc.php

https://exceljet.net/excel-functions/excel-trunc-function

follow us on http://arivilm.blogspot.in

like us on https://www.facebook.com/Arivilm2501

Int() Function

The Excel INT function returns the integer part of a decimal number by rounding down to the integer. Note the INT function rounds down, so negative numbers become more negative. For example, while INT(10.8) returns 10, INT(-10.8) returns -11.

Syntax

The syntax for the INT function in Microsoft Excel is:

=INT( expression )

Parameters or Arguments

expression

A numeric expression whose integer portion is returned.

Excel Int Function Examples

The following spreadsheets show the Excel Int function applied to different numbers:

Formulas:

	A	B
1		=INT(100.9)
2	5.22	=INT( A2 )
3	5.99	=INT( A3 )
4	99.5	=INT( A4 )
5	-6.1	=INT( A5 )
6	-100.9	=INT( A6 )

Results:

A B 1 100 2 5.22 5 3 5.99 5 4 99.5 99 5 -6.1 -7 6 -100.9 -101

The above examples show how the Int function always rounds down. Even with negative numbers, the function rounds down (away from zero).

References

http://www.excelfunctions.net/Excel-Odd-Function.html

https://exceljet.net/excel-functions/excel-odd-function

https://www.techonthenet.com/excel/formulas/odd.php

follow us on http://arivilm.blogspot.in

like us on https://www.facebook.com/Arivilm2501

Odd() Function

The Excel ODD function returns the next odd integer after rounding a given number up. The ODD function always rounds numbers up (away from zero) so positive numbers become larger and negative numbers become smaller (i.e. more negative).

Syntax

The syntax for the ODD function in Microsoft Excel is:

=ODD( number )

Parameters or Arguments

number

A numeric value that will be rounded up to the nearest odd integer.

Odd Function Examples

The following spreadsheet shows several examples of the Excel Odd function:

Formulas:

	A	B
1		=ODD( 22 )
2	1.22	=ODD( A2 )
3		=ODD( A2+4 )
4	0	=ODD( A4 )
5	-1.3	=ODD( A5 )
6	-10	=ODD( A6 )
7	-3	=ODD( A7 )

Results:

	A	B
1		23
2	1.22	3
3		7
4	0	1
5	-1.3	-3
6	-10	-11
7	-3	-3

The above examples show how the odd function always rounds away from zero. I.e. If the supplied number is positive, it is rounded up (more positive), but if the supplied number is negative, it is rounded down (more negative).

If the number argument is exactly equal to zero, as shown in cell B4 of the example spreadsheet above, the Excel Odd function rounds to the positive value +1.

References

http://www.excelfunctions.net/Excel-Odd-Function.html

https://exceljet.net/excel-functions/excel-odd-function

https://www.techonthenet.com/excel/formulas/odd.php

follow us on http://arivilm.blogspot.in

like us on https://www.facebook.com/Arivilm2501