Floor & Ceil (RPN-67 vs Excel)

[Matt Agajanian]

Hi all.

Travel off a tangent from my other floor/ceiling post:

While writing an RPN-67 program to exercise my programming muscles, I decided to write an Excel sheet comparing Excel’s functions versus the built-in CEIL & FLOOR function in Max mode.

I’ve found different results between Excel & RPN-67’s functions. For arguments < 0, RPN-67 and Excel disagree by -1.

So. why would there be a difference? What functions are RPN-67’s FLOOR & CEIL calculating? If Excel gives the official answer, what are RPN-67’s CEIL & FLOOR functions calculating for both negative & positive values?

[stilmant]

Hi Matt,

I'm new to this forum, so first of all, hello! I have some questions related to your post, and while I not have the answers, I find this topic very interesting.

You mentioned that "Excel gives the official answer," but I am curious about why Excel's implementation should be considered the standard. The definitions of the ceiling and floor functions are quite well-defined in mathematics.

The floor function of a real number \( x \), denoted by \( \lfloor x \rfloor \), is defined as:
$$
\lfloor x \rfloor = \max \{ n \in \mathbb{Z} : n \leq x \}
$$
This means \( \lfloor x \rfloor \) is the greatest integer less than or equal to \( x \).

The ceiling function of a real number \( x \), denoted by \( \lceil x \rceil \), is defined as:
$$
\lceil x \rceil = \min \{ n \in \mathbb{Z} : n \geq x \}
$$
This means \( \lceil x \rceil \) is the smallest integer greater than or equal to \( x \).

So for your observation about the differences between Excel and RPN-67 for arguments less than 0, where you noted they disagree by -1, could you provide more specific examples? The term "disagree by -1" is a bit vague for me, and I think seeing specific numbers would help clarify this.

I am not familiar with the RPN-67, but if it is an emulator of the HP-67 calculator, I wonder if the built-in CEIL & FLOOR functions you mentioned are specific to the iOS app you're using?

Thanks

[Thomas Okken]

In the languages I use, floor() rounds toward negative infinity and ceil() rounds toward positive infinity. It sounds like either Excel or RPN-67 diverges from this convention, presumably making floor() round toward zero and ceil() round away from zero. Just try applying those functions to a negative non-integer to see which one does what.

[dm319]

I think Microsoft Excel has it wrong.

This is what wikipedia says at the bottom of their page:

Wikipedia Wrote:In Microsoft Excel the funtion INT rounds down rather than toward zero,[52] while FLOOR rounds toward zero, the opposite of what "int" and "floor" do in other languages. Since 2010 FLOOR has been changed to error if the number is negative.[53] The OpenDocument file format, as used by OpenOffice.org, Libreoffice and others, INT[54] and FLOOR both do floor, and FLOOR has a third argument to reproduce Excel's earlier behavior.[55]

[Matt Agajanian]

Why did I think Excel had the official word?

Well, when you have an app that has cornered the market such as MS Excel has done, it seems as though Excel can do no wrong. Plus, I haven’t seen an audience that has cried foul and mandated that Microsoft change its ways and rewrite Excel so it’s financially, technically, mathematically accurate in every way.

Besides, from here, MoHPC, Cuvee, Wolfram Alpha, the consensus points to Excel at fault. With both sides presenting their cases, it would seem that Microsoft dropped the ball. It took this dual sided investigation so that the record can be set straight and to find out that MS is in error.

[dm319]

Yes, it's remarkably bad isn't it, especially as Excel is the defacto standard for banking calculations.

Puts Libreoffice in a difficult position - compatibility with Excel vs being correct.

[stilmant]

Hi Matt,

Regarding why Excel might be perceived as the "official word," it's likely due to its widespread use and market dominance, as you mentioned. However, widespread use does not always equate to mathematical correctness. Users need to be cautious and understand the limitations of Excel's functions.

I noticed that you didn't provide some details of your tests. I don't understand the "It took this dual sided investigation" mentioned, but I would like to clarify that Excel has two floor functions:

• FLOOR function
• FLOOR.MATH function
  

The FLOOR.MATH function offers two modes, one of which is optional. The mode specifies the direction (toward or away from 0) to round negative numbers.

So, there are at least three ways to get floor results in Excel, and you mentioned "max mode," which also confuses me.

It would be helpful to know more about the specific examples where you see the -1 discrepancy. This might help pinpoint exactly where the differences arise and further clarify the behavior of both Excel and RPN-67.

Thanks!

Michael

[stilmant]

Hi Again,

What I'm wondering is if you may have used the Round-Toward-Zero algorithm while you were expecting Round-Floor results. Microsoft Excel was designed around the IEEE 754 specification for storing and calculating floating-point numbers. This standard proposes multiple ways of rounding:Directed roundings Could it be that this is why Excel includes multiple modes in the same floor function?

Thomas was warning exactly about that in his comment.

Here's a nice summary of different rounding algorithms, which might help answer your original question ("why would there be a difference?"):


Edit: Source of the Pic + add Thomas Okken credit.

[Matt Agajanian]

(05-24-2024 08:18 PM)stilmant Wrote:  Hi Matt,

So, there are at least three ways to get floor results in Excel, and you mentioned "max mode," which also confuses me.

Thanks!

Michael

By Max mode, I was referring to one of RPN-67's Enhancements setting (Off, Med, Max). 'Off' sets RPN-67 to operate as a vintage HP-67. Where Max mode allows 999 program steps, plus 100 extra data registers, as well as a whole bunch of new functions and lets you check & run parts of your program based on RPN-67's settings and internals (e.g. haptic level, set/check beep type, swap values between primary & secondary registers, using three additional indirect registers, I, J, and K, just a whole bunch of stuff).