I was doing an exercise with the RSA encryption scheme with small numbers in Excel. I went to use the MOD function, but it just isn't working at all.

Trying a basic MOD example, I typed the following in to a fresh Excel book:

=MOD(2,3)

This results in the following:

I installed LibreOffice as a sanity check and entered the same function and it correctly returned 2.

Am I going crazy here or is my Excel broken?

I think Excel might not be parsing commas correctly, as the UI doesn't indicate that I am moving from one argument to the next. But I am not sure how to fix it.

EDIT: As per one of the answers to the question, I attempted to use the Formula interface to execute a MOD function. The results imply that my Excel is broken in some way.

EDIT 2: This is what happens when I click OK on the previous image (but I used 6 and 4 instead of 4 and 3).

10

4 Answers

The problem has to do with list separators. For some reason, my machine had the tab character \t as the list separator. I have no idea how this happened and I certainly don't remember doing it.

I went in to the Control Panel -> Region -> Additional Settings. Then I changed it to comma and all is well!

Before:

After:

2

To get to the root of the problem, start in a blank cell, then click the Formulas ribbon, Math & Trig dropdown and select the MOD() function. Fill in the number and the divisor and look at the formula that Excel puts in the formula bar.

That will be the syntax you need to use. Confirm the dialog and compare the formula with the one you entered manually.

If you get an error after confirming this dialog, then there is a more serious problem.

1

I have been using VBA:

Originally even
XLMod = (a - (b * Int(a / b))) was giving me incorrect results so..

Function XLMod(a, b As Long)
' This replicates the Excel MOD function - the VBA mod function returns an integer
'see also Dim templong As Long templong = (b * Int(a / b)) XLMod = (a - templong) End Function

I'm not sure why but maybe forcing the first part of the equation possibly eliminates the floating point errors

1

There is a general problem with functions that take comma as separator between parameters. If the locale is set to a region that uses comma as decimal symbol, MOD function ceases to run correctly and throws syntax error. This is true even if a space is inserted after comma like in MOD(3, 2), the error is still present. The work-around is to temporary change the locale to English US, but we need a definitive fix.

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy