in a sheet: =mod(75,293;1) results in 0,29300000000000 6’, crazy enough i’m just working / playing around with the six,
but now, in basic macro: a = 75,293 mod 1 results in ‘0’,
afaik it’s not a wrong declaration of ‘a’ or a matter of punctuation,
a = 75.293 - int(75.293) works fine and produces ‘0,293’ …
perplexed
b.
May be these articles give an explanantion of .... 6 in the spreadsheet function:
@anon73440385: i do know - something - about the float mysteries, in fact i’m just trying to construct a macro function to substitute ‘MOD’ which doesn’t suffer from the fp-rounding issues, as a proof of concept that ‘correct’ decimal math is! possible with the tools we have also on systems which internally use binary-fp’s
… which doesn’t suffer from the fp-rounding issues,
The thing is. It is not a rounding issue. It is an issue because no rounding based on heuristics occurs. In other words: Use ROUND() function, whenever you need rounded values (and you control what you want and you know what you do).
oh, we miss each other, the ‘suffer from fp-rounding’ does not refer to this question, but to the project where this question appeared, the =75,293-int(75,293) does not refer to this problem, but to the possible assumption that wrong punctuation is responsible for this problem,
the problem for this question is if mod of a fraction should result with fraction, or be rounded away, and that it’s different between sheet and basic
Hello,
quote from the macro bible
and that is indeed different from the formula mod function
https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_MOD_function
To get the remainder in the macro you will most like have to use DIVISION with SUBTRACTION.
Hope that helps.
To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!
Have a nice day and let’s (continue to) “Be excellent to each other!”
@igorlius: thanks, i think you are right, the implementation in ‘basic’ and ‘formula’ is different,
in a way i expected that it is mentioned somewhere in the manuals, and that i am ‘wrong!’ with my anger / wonder because i ignored ‘rtfm’ …
but seriously, which programmer always looks up every function before using it, and who expects such a different way of working for the same functionality in a system?
really thanks for the help, my perplexity was so big that i didn’t feel like doing research because of my frustration,
these things are not only used by LO experts, but by ‘normal people’, why do we annoy them with such traps?
Good question.
If i had to guess, i would say somthing like this happend:
Two programmers/teams thought their mod implementation was the better aka most fitting.
Or they just did not know about the other.
You can call the Calc Cell functions if you need it in your macro code:
Function CellMod(theRealNum as double, theDivider as integer) as double
dim oFunctionaccess
dim arg as Variant
oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
arg =array(theRealNum, theDivider)
CellMod = oFunctionAccess.CallFunction("MOD",arg)
End function
And you can use the Basic MOD in the Cells:
Function iMod(theIntNum as integer, theDivider as integer) as double
iMod = theIntNum mod theDivider
End functionThis might be the easiest way to achieve the exact same behaviour, without any surpises. Nice.
yeah, had seen that and it works, but why has calc always to be so complicated?
…What is “so complicated” in the Calc?
@Zizi64: complicated … you haven’t yet spotted that? stay happy or continue reading … copy 'n paste, macro language, bug handling, 12 years unresolved bugs, evaluation of ‘-’, autofilter issues, not standard conform files, slowdown issues with comments, function wizard, undo issues, wrong calculations, ‘mod’ in formula <> ‘mod’ in macro, fp-rounding issues, autocalculate exclusions, not all functions recalcuated, parallelized computing issues (open-CL and threaded) … and some more … it’s not that calc is ‘bad’, it’s better than ex$el imho, but sometimes but sometimes i despair when i think how good it could actually be … and how hard it is to get anything forward …
“copy 'n paste,”: What is the problem with this function? Note: There is not (never was and never will be 100% compatibility between the different file formats, document types and the formatting methods. Therefore always better to paste the formatted texts from a foreign source “as unformatted text” to your documents. It is true for every editor software.
“not standard conform files”: The only one REALLY standardized file format for office documents is the ODF. The binary MS file formats never was standardized, and they have lots of poorly documented subversions. The “Standardized” OOXLM file format has a Strict version, and a Transitional one, what is often modified by MS without documentation.
"‘mod’ in formula <> ‘mod’ in macro, ": The original MOD operator was created for dividing integer by integers - nothing more. You can use the “extended” version with pure integers too.
“fp-rounding issues,”: The IEEE 754-2008 standard for the floating point numbers is independent from the office suites. Maybe the Excel rounds the results without inform you about the changing of the result. The LO will not rounding the results instead of you. It is your job, it is your responsibility. The LO will display the BEST available result by default.
“parallelized computing issues (open-CL and threaded)”: Just switch OFF this feature if your Videocard has not proper capability for this feature.
“not all functions recalcuated,”: Yes, It only recalculates functions whose at least one input parameter has changed. But you can enforce the full recalculation manually.
Conclusion:
Do not use the LibreOffice if it is not appropriate for you.
hello @Zizi64, don’t try to be a ‘fanboy’, considering the effort i invested in calc problems and quality i’m a fan as well, perhaps the bigger one,
reg. the points, i know about them, but stating copy 'n paste is working fine while i’m maintaining a incomplete! list with 49 bugs filed about it, none of them resolved except marking as duplicate, just look in ‘bugs’ … is not an improvement of the problem, similar yields for the other points …
‘The LO will display the BEST available result by default.’, i wish it would be like that, it isn’t, see https://bugs.documentfoundation.org/show_bug.cgi?id=133840, https://bugs.documentfoundation.org/show_bug.cgi?id=128312, and https://bugs.documentfoundation.org/show_bug.cgi?id=131312,
criticism is annoying, but criticism is also necessary, you won’t enjoy a calc as good as it is if not many hard-working people had invested a lot of work to spot, substantiate and document errors,
Me and our little company using the LibreOffice since the forking date without larger problems.
Yes, I know it: there are bugs (older ones and newer ones, smaller and bigger ones too).
The vast majority of problems come from such users,
who use the Office program as if it were a mechanical typewriter …
who use the LibreOffice as if it were MS Office without the “Styles” function … They expected to have an
MS Office clone in all properties.
who think that LibreOffice is a paid product and that for their “money” they are entitled to and error-free
product.
they don’t realize it’s a community where you can “get and give” something.
@Zizi64: but now it really becomes OT:
why do I always let myself get caught up in my tendency to discuss and question fundamental things?
‘a community where you can “get and give” something.’
that’s right, but!:
such a community is necessarily heterogeneous, and thus members have different views, may they have, must they even have, it is a drive for progress,
and you belong to those who think ‘the users are to blame’, I belong to those who think ‘the users have to grow into the community’, and you should not make it unnecessarily difficult for them,
you to those who think the claim ‘typewriter’ is primitive / wrong, I to those who think such claims should be easy to fulfill, why is it so difficult for us?
you to those who think we are independent of M$, I to those who see the problems that (too?) much importance is attached to compatibility, but that is mainly used to defend errors,
…