Bug 161015

Summary: MATH: round, roundup: 'closedness', meaningful results for excessive digits
Product: LibreOffice Reporter: b. <newbie-02>
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: NEW ---    
Severity: normal CC: stephane.guillou
Priority: medium    
Version: 24.2.0.3 release   
Hardware: All   
OS: All   
See Also: https://bugs.documentfoundation.org/show_bug.cgi?id=73410
Whiteboard:
Crash report or crash signature: Regression By:
Attachments: some examples of rounding fails,

Description b. 2024-05-09 21:15:10 UTC 
looking for differences between Calc and gnumeric I saw some  
questionable results:  
roundup of small values to digits like -309 shouldn't result  
in '0' but rather in a meaningful ERR, 
IMHO also roundup( 1.2E+308, -308 ) -> 1.2E+308 isn't correct, 
also round( 1.7E+308, -308) -> 1.7E+308 is questionaböe from 
a math POV.  

pls. be tolerant if 'me bad', that's always an option, e.g. 
my Calc too old or similar ... 
 
Version: 24.2.0.3 (X86_64) / LibreOffice Community
Build ID: 420(Build:3)
CPU threads: 8; OS: Linux 6.6; UI render: default; VCL: x11
Locale: en-US (en_US.UTF-8); UI: en-US
Debian package version: 4:24.2.0-1
Calc: threaded
Comment 1 Stéphane Guillou (stragu) 2024-05-27 13:09:13 UTC 
To clarify what you think the issue is, can you please attach a sample ODS with examples, highlighting which results are problematic, and which can be opened in both Gnumeric and LO?

See also bug 73410.
Comment 2 Mike Kaganski 2024-05-27 13:56:57 UTC 
(In reply to b. from comment #0)
> roundup of small values to digits like -309 shouldn't result  
> in '0' but rather in a meaningful ERR,

This is reasonable.

> IMHO also roundup( 1.2E+308, -308 ) -> 1.2E+308 isn't correct,

It should give an overflow (#NUM!), same way as e.g. '=1e308 * 5'. In my testing, gnumeric gave even more questionable '1e308' here, but that doesn't matter.

> also round( 1.7E+308, -308) -> 1.7E+308 is questionaböe from 
> a math POV.

This is the same. Should give an overflow.

For testing:

1. Try formula

=ROUNDUP(1;-309)

which is "round number 1 up to the next multiple of 1e309", which mathematically is 1e309, which is out of representable range.

2. Try formula

=ROUNDUP(1.2E+308;-308)

which is "round 1.2e308 up to the next multiple of 1e308", which mathematically is 2e308, which is out of representable range.
Comment 3 b. 2024-05-28 15:14:16 UTC 
Created attachment 194405 [details]
some examples of rounding fails,

hi, thanks for caring,  
  
to stay clear: I do not propose to mimic gnumeric, they have fails I'm investigating,  
I do not propose to mimic Excel, they have their own fails,  
I'd like mathematical meaningful results independent of idiotic input,  
to set users free from manual having to check. Each and any input should  
have a meaningful '0', 'ERR', or calculated value.  
 
sheet: see attached, it's surely not perfect but should provide an entry.  

# 73410 ... old problem? not completely covered?  
 
expect, not yet tested, similar problems with other rounding functions,  
also with those 'to multiples' like floor and ceiling.  
  
Mathematical it's not really difficult, I'd propose to implement some  
triaging steps returning '0' or ERR where applicable before the  
calculations.