Rounding in QSpread

[swensont]

I've been tinkering with Qspread and I found that despite having rounding turned off, it will still round up larger numbers.

If I type in a 6 digit number with a decimal (123456.5) it will display it as 123456.5. If I type in a 7 digit number with a decimal (1234567.5) it will display it as 1234568. The 1234567.5 will display in the formula window next to the "TAB" text, but in the sheet itself, it will round it up.

Has anyone else seen this behavior? Is there a way to have it stop rounding up for larger numbers?

Thanks,

Tim

[olifink]

hi Tim,

I'm amazed some people still tinker with it and find these things yes, you're right - and the reason for this rounding is that the QL internal floating point routines are not that precise. You actually see the same in SuperBASIC when you PRINT the two numbers:

Code: Select all

PRINT 123456.5
PRINT 1234567.5

gives you

Code: Select all

123456.5
1.234568E6

the latter being the scientific notation as in 1.234568 * 10^6; they are internally stored as two byte exponent and four byte mantissa (with a couple of bit used for other stuff). I don't recall if Abacus is different, it might be PSION brought their own floating point routines.

For QSpread, I decided to call existing QDOS routines rather than trying to write my own (the expression/formula parser alone was already a hard enough nut to crack, figuring out how to do it all in assembler).

Funnily, when thinking about your question last night I actually recalled we knew this limitation, and, discussing it with Jochen and we decided we should be ok: if somebody needed to balance their checkbook with numbers that large, we figured they'd probably be in a position to buy a PC and Excel anyway... LOL, thanks for bringing back that memory.

so, if you find some other weird stuff in QSpread just blame it on my being lazy...

O

[swensont]

I was not trying to balance my checkbook, but converting Gregorian data to Julian Day. The current Julian Day is 2459847.5, which will round up.

Tim

[stevepoole]

Bonjour,

There is an accepted method of converting Julian dates to Gregorian ones, and somewhere in my backup hard disk is the routine to do it in superbasic.

But I have not seen Gregorian to Julian method mentioned elsewhere than on your posting...

The problem with rounding is difficult : How do you round 1.49999999 for example ? The correct answer is of course 1, but some would round to 2 !

I seem to remember somewhere that the Psion suite were accurate to 14 digits. Can anyone confirm ?

Steve.
___________________

[swensont]

I've used the method of converting between Gregorian Date and Julian Date from Jean Meeus. It is working fine with Abacus.
I'm doing the same method with SuperBasic, but using "PRINT USING" to get the output to be what I need.

I've tested with Abacus, and it is 14 digits. I was able to get "1234567890.1234" but not "1234567890.12345". When I entered the second number it just showed up as the first number.

Tim

[janbredenbeek]

I was not trying to balance my checkbook, but converting Gregorian data to Julian Day. The current Julian Day is 2459847.5, which will round up.

The QL's FP system uses 31-bit mantissa so should be sufficient for about 9.5 digits accuracy. However when converting to ASCII it rounds to only 7 digits. This is even true for FP constants in S*BASIC programs so when saved and loaded back you may lose accuracy. When I used to write programs for astronomical calculations I had to get around this by splitting up large numbers (e.g. '2459847+.5') or reducing them to a different epoch (e.g. 1900 or 2000-based).

Speaking of Julian Days, when I enter JD=2459847.5, then do JD=JD+1/24 and then PRINT (JD-2459847.5)*24-1/24 the error seems to be about 1 hour which is considerably more accurate than when rounded to only 7 digits (one day).

Jan

[janbredenbeek]

I've used the method of converting between Gregorian Date and Julian Date from Jean Meeus. It is working fine with Abacus.
I'm doing the same method with SuperBasic, but using "PRINT USING" to get the output to be what I need.

Ah yes, I used the 'bible' from Meeus too (Astronomical Formulae for Calculators, which is a bit outdated now and superseded by Astronomical Algorithms).
Most if not all calculations in these books are 1900 or 2000-based so I didn't use the 'real' JD but a modified JD so as not to lose accuracy.

I wrote my own 'PRINT USING' function which is able to display full 9 or 10 digit FP numbers.

(note to self: I still must post these 30+ year old programs to GitHub but have to work a bit on the UI side of them...)

Jan

[stevepoole]

Hi Folks,

You can check your results using this link :

Online calculator: Gregorian date to Julian date - PLANETCALC https://planetcalc.com/8497

The main problem with such routines is that different countries adopted the Gregorian calender at different dates !

For example, the routine I wrote for 'Julian to Gregorian' dates only works for France as it stands.... (or would need tweaking).
It does no calculations, just using look-up table cross-reference indexing, so there were no rounding problems.

Bon continuation !

Steve.
_________________

[janbredenbeek]

The Julian Day is not the same as the Julian Calendar. It is the date expressed in days from 1st January 4713 BC at noon and mainly used for astronomical calculations where a continuous day numbering comes in handy.

https://en.wikipedia.org/wiki/Julian_day

[RalfR]

BTW: Does anyone know, if there is a printer filter for QSpread, which saves in an *.csv format for importing to Excel? Oli doesn't know