Pan X does not seem to have a round down function (i.e., “rounddown(” in Excel), so I wrote the following substitute, which rounds down to the nearest ones place.
Where “XX” is the number, number field, or mathematical equation in question.
I am posting this because: 1) this code might be useful to others (or at least, this discussion), and 2) to find out if there is a better way to round down, and 3) respectfully request rounddown, and roundup functions in future Pan X versions.
Revisiting this subject, I am using your formula (with ceil() to round a number up to the nearest penny. For example:
(ceil((1706.556/0.01)+0.5))*0.01
In this example, $1,706.556 is rounded to $1,706.56, which is all well and good. However, If I put a number that terminates at the hundreds place (i.e., $1,706.55), the above formula yields $1,706.56. This is not desirable. Rather, I would like to $1,706.55 to remain $1,706.55.
I assume many of Pan X aficionados compile financial reports. How do they manage rounding to the nearest cent when making financial calculations? What is the simple solution?
Taking it one step at a time
1706.556/0.01 = 170655.6
170655.6+0.5 = 170656.1
ceil(170656.1) = 170657
170657*0.01 = 1706.57
This is a problem, due to the fact that computers are binary, and our finances are decimal. The only multiples of 0.01 that have exact representations as floating point numbers are those that are also multiples of 0.25, so there is no such number as 1,706.55, only a very very very close approximation.
If I understand you correctly, you would like any number that does not terminate at the hundredths place to be rounded to the next penny. Allowing a tiny margin for roundoff error, I would recommend something like this.
(ceil((«Number»/0.01)-0.000001))*0.01
Any number that came within a millionth of a cent of the nearest penny would be considered equal to that penny. Anything else would be rounded up to the next penny. A millionth of a cent was an arbitrary choice.
Only one example:
" Round to Even (Banker’s Rounding ) We round 0.5 to the nearest even digit Example: 7.5 rounds up to 8 (because 8 is an even number) but 6.5 rounds down to 6 (because 6 is an even number) Other numbers (not ending in 0.5) round to nearest as usual …"
This means, you decide what you want in your situation and construct the appropriate formula. It’s not that Panorama doesn’t have a “round down” formula as it is the term “round” is a bit ambiguous. It seems “rounding” is a personal matter. As shown in the examples above, there are many ways to cook that broccoli (colloquialism modified in empathy for feline fanciers). If you can define it, you can write a formula/function for it.
In the example above, I see only positive numbers. Considering negative numbers further tilts the Can:worms ratio toward the worms.
This equation provided the springboard to the equation I needed:
(ceil((«Number»/0.01)-0.499999))*0.01
When the number’s thousands digit is 5 or higher, it rounds up to the nearest hundredth place. Otherwise, it keeps the same number in the hundredths place.
