The Excel FLOOR function
rounds a given number down to the nearest specified multiple.
Syntax
The syntax for the FLOOR
function in Microsoft Excel is:
=FLOOR( number, significance )
Parameters or Arguments
number
The number that you wish to
round down.
significance
The multiple of significance
that you wish to round a number to.
Floor Function Examples
In column B of the following
spreadsheet, the Excel Floor function is used to round the positive and
negative numbers 26.75 and -26.75 to different multiples of significance.
Formulas:
|
|
A
|
B
|
|
1
|
Number
|
Floor
|
|
2
|
26.75
|
=FLOOR(
A2, 0.1 )
|
|
3
|
26.75
|
=FLOOR(
A3, 0.5 )
|
|
4
|
26.75
|
=FLOOR(
A4, 1 )
|
|
5
|
26.75
|
=FLOOR(
A5, 10 )
|
|
6
|
26.75
|
=FLOOR(
A6, 20 )
|
|
7
|
-26.75
|
=FLOOR(
A7, -0.1 )
|
|
8
|
-26.75
|
=FLOOR(
A8, -1 )
|
|
9
|
-26.75
|
=FLOOR(
A9, -5 )
|
Results:
|
|
A
|
B
|
|
1
|
Number
|
Floor
|
|
2
|
26.75
|
26.7
|
|
3
|
26.75
|
26.5
|
|
4
|
26.75
|
26
|
|
5
|
26.75
|
20
|
|
6
|
26.75
|
20
|
|
7
|
-26.75
|
-26.7
|
|
8
|
-26.75
|
-26
|
|
9
|
-26.75
|
-25
|
-
The above examples show how, when the number and
the significance arguments have the same arithmetic sign, the Floor function
rounds the supplied number towards 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 (2010 or later).
Formulas:
|
|
A
|
B
|
|
1
|
Number
|
Floor
|
|
2
|
26.75
|
=FLOOR(
A2, 1 )
|
|
3
|
26.75
|
=FLOOR(
A3, -1 )
|
|
4
|
-26.75
|
=FLOOR(
A4, 1 )
|
|
5
|
-26.75
|
=FLOOR(
A5, -1 )
|
Results:
|
|
A
|
B
|
|
|
1
|
Number
|
Floor
|
|
|
2
|
26.75
|
26
|
|
|
3
|
26.75
|
#NUM!
|
|
|
4
|
-26.75
|
-27
|
-
returns an error in Excel 2007 & earlier
|
|
5
|
-26.75
|
-26
|
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 away from 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).
Note
Ø If
either argument is nonnumeric, FLOOR returns the #VALUE! error value.
Ø If
number is positive and significance is negative, FLOOR returns the #NUM! error
value.
Ø If
the sign of number is positive, a value is rounded down and adjusted toward
zero. If the sign of number is negative, a value is rounded down and adjusted
away from zero. If number is an exact multiple of significance, no rounding
occurs.
Ø With
a positive number, the value is rounded down toward zero.
Ø With
a negative number, the value is rounded down away from zero.
Ø The
FLOOR function can be a good way to can be a good way to set pricing after
currency conversion, discounts, etc.
References
follow us on http://arivilm.blogspot.in
like us on https://www.facebook.com/Arivilm2501
No comments:
Post a Comment