The Excel CELING function
returns a given number rounded up to a specified multiple. CEILING always
rounds up, away from zero.
Syntax
The syntax for the CEILING
function in Microsoft Excel is:
=CEILING( number, significance )
Parameters or Arguments
number
The number that you wish to
round up.
significance
The multiple of significance
that you wish to round a number to.
Ceiling Function Examples
In column B of the following
spreadsheet, the Excel Ceiling function is used to round the positive and
negative numbers 22.25 and -22.25 to different significance values.
Formulas:
|
|
A
|
B
|
|
1
|
number
|
ceiling
|
|
2
|
22.25
|
=CEILING(
A2, 0.1 )
|
|
3
|
22.25
|
=CEILING(
A3, 0.5 )
|
|
4
|
22.25
|
=CEILING(
A4, 1 )
|
|
5
|
22.25
|
=CEILING(
A5, 10 )
|
|
6
|
22.25
|
=CEILING(
A6, 20 )
|
|
7
|
-22.25
|
=CEILING(
A7, -0.1 )
|
|
8
|
-22.25
|
=CEILING(
A8, -1 )
|
|
9
|
-22.25
|
=CEILING(
A9, -5 )
|
Results:
|
|
A
|
B
|
|
1
|
number
|
ceiling
|
|
2
|
22.25
|
22.3
|
|
3
|
22.25
|
22.5
|
|
4
|
22.25
|
23
|
|
5
|
22.25
|
30
|
|
6
|
22.25
|
40
|
|
7
|
-22.25
|
-22.3
|
|
8
|
-22.25
|
-23
|
|
9
|
-22.25
|
-25
|
-
The above examples show how, when the number and
the significance arguments have the same arithmetic sign, the Ceiling function
rounds away from zero.
Examples with Positive and Negative Arguments
Different combinations of
positive and negative arguments, are shown in the spreadsheet below.
The results shown are those
that would be obtained in current versions of Excel (Excel 2010 and later).
Formulas:
|
|
A
|
B
|
|
1
|
number
|
ceiling
|
|
2
|
22.25
|
=CEILING(
A2, 1 )
|
|
3
|
22.25
|
=CEILING(
A3, -1 )
|
|
4
|
-22.25
|
=CEILING(
A4, 1 )
|
|
5
|
-22.25
|
=CEILING(
A5, -1 )
|
Results:
|
|
A
|
B
|
|
|
1
|
number
|
ceiling
|
|
|
2
|
22.25
|
23
|
|
|
3
|
22.25
|
#NUM!
|
|
|
4
|
-22.25
|
-22
|
-
returns an error in Excel 2007 & earlier
|
|
5
|
-22.25
|
-23
|
This example shows how:
-
In current versions of Excel (2010 and later), a
negative number argument and a positive significance argument reverses the
direction of the rounding (i.e. rounds the negative number towards zero) (see
cell B4).
-
A positive number argument and a negative significance
argument still results in an error in all versions of Excel (see cell B3).
Notes
-
If the sign of the number and the significance
parameters are different, the CEILING function will return the #NUM! error.
-
If either argument is nonnumeric, CEILING
returns the #VALUE! error value.
-
Regardless of the sign of number, a value is
rounded up when adjusted away from zero. If number is an exact multiple of
significance, no rounding occurs.
-
If number is negative, and significance is
negative, the value is rounded down, away from zero.
-
If number is negative, and significance is
positive, the value is rounded up towards zero.
References
follow us on http://arivilm.blogspot.in
like
us on https://www.facebook.com/Arivilm2501
No comments:
Post a Comment