The Excel Ceiling.Math
function rounds a supplied number up to a supplied multiple of significance.
In general, positive values
are rounded away from zero and negative numbers are rounded towards zero.
However, the user can reverse the direction of the rounding for negative
numbers.
Syntax
The syntax for the Ceiling.Math function in Microsoft Excel is:
= CEILING.MATH(number, [significance], [mode])
Parameters or Arguments
Number
Required.
Number must be less than 9.99E+307 and greater than -2.229E-308.
Significance
Optional.
The multiple to which Number is to be rounded.
Mode
Optional.
For negative numbers, controls whether Number is rounded toward or away from
zero.
Ceiling.Math Function Examples
In column B of the following
spreadsheet, the Excel Ceiling.Math function is used to round the positive and
negative numbers 15.25 and -15.25, to different significance values.
Formulas:
|
|
A
|
B
|
|
1
|
15.25
|
=CEILING.MATH(
A1 )
|
|
2
|
15.25
|
=CEILING.MATH(
A2, 0.1 )
|
|
3
|
15.25
|
=CEILING.MATH(
A3, 5 )
|
|
4
|
-15.25
|
=CEILING.MATH(
A4, 1 )
|
|
5
|
-15.25
|
=CEILING.MATH(
A5, 1, 1 )
|
|
6
|
-15.25
|
=CEILING.MATH(
A6, 10 )
|
Results:
|
|
A
|
B
|
|
1
|
15.25
|
16
|
|
2
|
15.25
|
15.3
|
|
3
|
15.25
|
20
|
|
4
|
-15.25
|
-15
|
|
5
|
-15.25
|
-16
|
|
6
|
-15.25
|
-10
|
The above examples show how:
-
If the [significance] argument is omitted, it
takes on the default value of 1 (see cell B1).
-
the Ceiling.Math function generally rounds
positive numbers away from zero and negative numbers towards zero. However, if
the [mode] argument is provided and is non-zero, negative numbers are rounded
away from zero (see cell B5).
Note
-
the Ceiling.Math function was first introduced
in Excel 2013 and so is not available in earlier versions of Excel.
-
By default, significance is +1 for positive
numbers and -1 for negative numbers.
-
By default, positive numbers with decimal
portions are rounded up to the nearest integer. For example, 6.3 is rounded up
to 7.
-
By default, negative numbers with decimal
portions are rounded up (toward 0) to the nearest integer. For example, -6.7 is
rounded up to -6.
-
By specifying the Significance and Mode
arguments, you can change the direction of the rounding for negative numbers.
For example, rounding -6.3 to a significance of 1 with a mode of 1 rounds away
from 0, to -7. There are many combinations of Significance and Mode values that
affect rounding of negative numbers in different ways.
-
The Mode argument does not affect positive
numbers.
-
The significance argument rounds the number up
to the nearest integer that is a multiple of the significance specified. The
exception is where the number to be rounded is an integer. For example, for a
significance of 3 the number is rounded up to the next integer that is a
multiple of 3.
-
If Number divided by a Significance of 2 or
greater results in a remainder, the result is rounded up.
References
follow us on http://arivilm.blogspot.in
like
us on https://www.facebook.com/Arivilm2501
No comments:
Post a Comment