Ask Your Question

calc: 'MOD' in sheet vs. 'MOD' in macro - want to drive me crazy?

asked 2020-09-12 10:15:08 +0100

newbie-02 gravatar image

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' ...



edit retag flag offensive close merge delete


Opaque gravatar imageOpaque ( 2020-09-12 12:44:14 +0100 )edit

@Opaque: 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

newbie-02 gravatar imagenewbie-02 ( 2020-09-12 13:27:01 +0100 )edit

... 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).

And reagarding a = 75.293 - int(75.293) works fine and produces '0,293' ...: I cannot confirm this. Just created a user function MYMOD and the formula =MYMOD(75.293) results (as expected) in: 0.293000000000006

Opaque gravatar imageOpaque ( 2020-09-12 13:47:29 +0100 )edit

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

newbie-02 gravatar imagenewbie-02 ( 2020-09-12 15:19:36 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-09-12 13:59:09 +0100

Zizi64 gravatar image

updated 2020-09-12 22:51:28 +0100

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( "" )
        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 function
edit flag offensive delete link more


This might be the easiest way to achieve the exact same behaviour, without any surpises. Nice.

igorlius gravatar imageigorlius ( 2020-09-12 15:21:05 +0100 )edit

yeah, had seen that and it works, but why has calc always to be so complicated?

newbie-02 gravatar imagenewbie-02 ( 2020-09-12 15:23:48 +0100 )edit

...What is "so complicated" in the Calc?

Zizi64 gravatar imageZizi64 ( 2020-09-12 15:36:05 +0100 )edit

@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 ...

newbie-02 gravatar imagenewbie-02 ( 2020-09-12 19:26:56 +0100 )edit

"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.

Zizi64 gravatar imageZizi64 ( 2020-09-12 20:50:04 +0100 )edit

"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.

Zizi64 gravatar imageZizi64 ( 2020-09-12 20:51:16 +0100 )edit


Do not use the LibreOffice if it is not appropriate for you.

Zizi64 gravatar imageZizi64 ( 2020-09-12 20:52:58 +0100 )edit

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,, and,
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 ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-09-12 23:57:04 +0100 )edit

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 gravatar imageZizi64 ( 2020-09-13 08:54:12 +0100 )edit

@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 ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-09-13 10:41:32 +0100 )edit

answered 2020-09-12 12:23:47 +0100

igorlius gravatar image

updated 2020-09-12 12:29:04 +0100


quote from the macro bible

image description

and that is indeed different from the formula 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!"

edit flag offensive delete link more


@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?

newbie-02 gravatar imagenewbie-02 ( 2020-09-12 13:38:23 +0100 )edit

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.

igorlius gravatar imageigorlius ( 2020-09-12 15:18:52 +0100 )edit

@igorlius - your link perfectly describes how this probably happened ;-)

Opaque gravatar imageOpaque ( 2020-09-12 15:50:07 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-12 10:15:08 +0100

Seen: 72 times

Last updated: Sep 12 '20