Solved: Unexpected modulo behavior

tkboisvert · ‎09-22-2021

Hi all,

In excel, I have this formula:

MOD(-10 + 9,12) + 1

And the expected result is 12

However, in PowerQuery the same formula:

Number.Mod(-10 + 9, 12) + 1

Results in 0

The strange thing is that for other numbers ( -1 ) I get the same result in both systems..

I expect this to have something to do with the nature of MOD, and how I'm using negative numbers.. But I would still like to know which is 'correct'..

KNP · ‎09-22-2021

Hi @tkboisvert,

The way the formulas is calculated is slightly different between the two functions.

Check out this video: https://www.youtube.com/watch?v=K4ImPRsi3vg&ab_channel=ExcelIsFun

Kim

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.

Proud to be a Super User!

View solution in original post

Greg_Deckler · ‎09-22-2021

@tkboisvert According to Modulo Calculator (calculators.org), Power Query is correct.

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...