Bug in floor() and ceiling()

  • Thread starter Thread starter trost
  • Start date Start date
trost said:
They both yield #NUM when the input is a negative number.
Click to expand...

From online help for the CEILING function:

Syntax

CEILING(number,significance)

Number is the value you want to round.

Significance is the multiple to which you want to round.

Remarks
....
· If number and significance have different signs, CEILING returns the
#NUM! error value.

FLOOR's entry in online help contains the same remark. So they're
working EXACTLY per their specs, so they're ipso facto NOT bugs.

You can question the wisdom of those specs, but that's an exercise
every bit as profitable as banging your head against a mountainside.
This is just how Excel works. Excel isn't going to adapt to you, so
you need to adapt to Excel. In this case, try

=SIGN(x)*CEILING(ABS(x),positive_significance_value)
 
Take a look at excel's help for =floor().

You'll see:

FLOOR(number,significance)
Number is the numeric value you want to round.
Significance is the multiple to which you want to round.

Remarks
If either argument is nonnumeric, FLOOR returns the #VALUE! error value.
If number and significance have different signs, FLOOR returns the #NUM! error
value.
 
Fair enough. SIGN() is a good solution.

Seems a bit arbitrary for the cell to have to test for errors and
unusual cases though.
 
Not a bug.

Both the number and the significance have to have same sign............if
you used Help you would have found that out.

If number and significance have different signs, CEILING returns the #NUM!
error value.


=CEILING(-1234.567,-2) is proper syntax



Gord Dibben MS Excel MVP
 
zan said:
Seems a bit arbitrary for the cell to have to test for errors and
unusual cases though.
Click to expand...
....

Welcome to Excel!

This isn't the only bit of perverse behavior.

To repeat: Excel won't adapt to you, you must adapt to Excel.
 
Hi,

You might also handle this by

=FLOOR(ABS(A1),ABS(A2))

In which case both entries will be postiive regardless. Or if you prefer

=FLOOR(-ABS(A1),-ABS(A2))

in both cases the number is in A1 and the significant digits in A2.
 
Gord Dibben said:
Both the number and the significance have to have same sign...
Click to expand...
....

Semantics.

'have to' is obviously wrong. As the OP found out, it's perfectly
possible to have arguments to CEILING or FLOOR with different signs.
Excel just doesn't return anything useful in those circumstances.

More generally, this is another example of the MSFT developer
originally responsible for writing these functions either having no
discernible experience in numeric programming or (worse) being
constrained to a small number of bytes of compiled code. CEILING and
FLOOR are well defined when signs differ. Actually, the sign of the
2nd argument should be considered irrelevant since the nearest
multiple of -significance is the negative of the nearest multiple of
+significance. NOTHING would be lost by the CEILING and FLOOR
implementations always converting their 2nd arguments to their
absolute values. That is,

CEILING.CORRECT(x,y) == SIGN(x) * CEILING.CURRENT(ABS(x),ABS(y))

and

FLOOR.CORRECT(x,y) == SIGN(x) * FLOOR.CURRENT(ABS(x),ABS(y))

where *.CORRECT is how the functions should behave in terms of
*.CURRENT which are how they actually behave currently. There's no
ambiguity since CEILING rounds AWAY from 0, and FLOOR rounds TOWARDS 0.
 
SIGN didn't have the expected results for negative numbers.

Given -12,000 as the input value and 5,000 as the significance, I need a
result of -15,000 for floor, -10,000 for ceiling.

These formulas seem to work (zz is the input cell):

FLOOR:
=IF(SIGN(zz)<0,-1*CEILING(ABS(zz),significance),FLOOR(zz,significance))

CEILING:
=IF(SIGN(zz)<0,-1*FLOOR(ABS(zz),significance),CEILING(zz,significance))
 
trost said:
They both yield #NUM when the input is a negative number.
Click to expand...

Both what? Hint: Repeat your subject in the message body. You are not
charged by the character. ;)

Try, for example:

=floor(A1,sign(A1)*0.5)

=ceiling(A1,sign(A1)*0.5)

PS: #NUM is probably not a bug. RFTM: use Excel Help. If you still
believe there is a bug, post a concrete example.
 
zan said:
SIGN didn't have the expected results for negative numbers.
Click to expand...

SIGN is not the problem. The problem is: you want something different from
FLOOR and CEILING as they are defined by Excel.

FLOOR:
=IF(SIGN(zz)<0,-1*CEILING(ABS(zz),significance),FLOOR(zz,significance))

CEILING:
=IF(SIGN(zz)<0,-1*FLOOR(ABS(zz),significance),CEILING(zz,significance))
Click to expand...

Or....

Largest integral multiple of (positive) sig (B1) less than zz (A1):

=INT(A1/B1)*B1

Smallest integral multiple of (positive) sig greater than zz:

=INT((A1+B1-1)/B1)*B1


However, since it seems reasonable to me that the sign of sig and zz must be
the same (cogitate on the description "largest integral multiple of sig less
than zz"; how could that be less than negative zz unless sig is also
negative -- or the multiple is!), the following is more bullet-proof insofar
as tolerating negative as well as positive sig -- probably not a situation
that concerns you.

Largest integral multiple of sig less than zz:

=INT(A1/ABS(B1))*ABS(B1)

Smallest integral multple of sig greater than zz:

=INT((A1+ABS(B1)-1)/ABS(B1))*ABS(B1)


----- original message -----
 
Harlan,

Just a curiosity question...

Do you recall how Lotus 123 handled these two functions--or if they even had
them?

Maybe it was one of those compatibility issues like February 29, 1900???
 
Dave Peterson said:
Do you recall how Lotus 123 handled these two functions--or if they even had
them?
Click to expand...
....

123 never had either @CEILING or @FLOOR. Instead it had an optional
3rd argument to its @ROUNDM function, which is roughly comparable to
Excel's ATP's MROUND function. In 123

@ROUNDM(x,y,@SIGN(x)) and @ROUNDM(X,Y,-@SIGN(X))

produce the same results as Excel's

=SIGN(x)*CEILING(ABS(x),ABS(y)) and =SIGN(x)*FLOOR(ABS(x),ABS(y))

respectively. Excel's perverse behavior is entirely MSFT's fault.
 
zan said:
SIGN didn't have the expected results for negative numbers.

Given -12,000 as the input value and 5,000 as the significance, I need a
result of -15,000 for floor, -10,000 for ceiling.
Click to expand...
....

You want ROUNDUP and ROUNDDOWN rather than CEILING and FLOOR.

'ceiling': =y*ROUNDUP(x/y,0)

'floor': =y*ROUNDDOWN(x/y,0)
 
Thanks for the info, Harlan.

Harlan said:
...

123 never had either @CEILING or @FLOOR. Instead it had an optional
3rd argument to its @ROUNDM function, which is roughly comparable to
Excel's ATP's MROUND function. In 123

@ROUNDM(x,y,@SIGN(x)) and @ROUNDM(X,Y,-@SIGN(X))

produce the same results as Excel's

=SIGN(x)*CEILING(ABS(x),ABS(y)) and =SIGN(x)*FLOOR(ABS(x),ABS(y))

respectively. Excel's perverse behavior is entirely MSFT's fault.
Click to expand...
 
Harlan Grove said:
You want ROUNDUP and ROUNDDOWN rather than CEILING and FLOOR.
'ceiling': =y*ROUNDUP(x/y,0)
'floor': =y*ROUNDDOWN(x/y,0)
Click to expand...

I don't think so.

Zan wrote: "Given -12,000 as the input value and 5,000 as the significance,
I need a result of -15,000 for floor, -10,000 for ceiling."

5000*ROUNDUP(-12000/5000,0) yields -15000, not -10000.

5000*ROUNDDOWN(-12000/5000,0) yields -10000, not -15000.

Zan could use ROUNDUP for __floor__ and ROUNDDOWN for __ceiling__. But
that's counter-intuitive; moreover, that works when "x" is negative with
positive "y".

The point is: ROUNDDOWN always goes toward zero, and ROUNDDOWN always goes
away from zero.

Zan wants a floor function that always goes to the left; that is, the
largest integer less than "y". INT is the Excel function that provides that
functionality, namely: y*INT(x/y).

Zan wants a ceiling function that always goes to the right; that is, the
smallest integer less than "y". I don't believe Excel provides a function
for that. But as I demonstrated, you can use INT in the following manner:
y*INT((x+y-1)/y).

To reiterate, all of this assumes that "y" (significance) is positive. The
INT formulas work for both positive and negative "x".


----- original message -----
 
Errata (typo)...
The point is: ROUNDDOWN always goes toward zero,
and ROUNDDOWN always goes away from zero.
Click to expand...

Duh, the latter should read: ROUNDUP always goes away from zero.


----- original message -----
 
Back
Top