0

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:

enter image description here

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.

enter image description here

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). enter image description here

Improve this question
10
  • What is RSA and how is it significant for the question?
    – teylyn
    Sep 22, 2020 at 21:18
  • RSA encryption scheme. I like adding a little context to my questions, but that is just one sentence of context. I will make that a bit clearer.
    – Zarquan
    Sep 22, 2020 at 21:42
  • 1
    (1) What version of Excel are you using?  (2) You say that you have the same problem with “AND” as you do with “MOD”, so I’ll assume that other functions (e.g., MAX, MIN and SUM) are similarly non-functional. How about simple formulas like =2+3 and =4-3?  (3) Are you using a language or keyboard layout other than US/English?
    – Scott - Слава Україні
    Sep 22, 2020 at 22:13
  • 1
    Perhaps your system list separator is a semicolon or some other character.
    – Ron Rosenfeld
    Sep 22, 2020 at 23:36
  • 1
    Can you screenshot what happens after you confirm the functions dialog with OK? The second argument has not yet been reflected in the formula bar, so your screenshot shows nothing wrong. What exactly is the "similar result" that you get with AND()? With such a specific problem, you need to provide specific details, not just saying that "weird things happened". Show what these weird things are.
    – teylyn
    Sep 23, 2020 at 0:52

4 Answers 4

Reset to default
3

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:

enter image description here

After:

enter image description here

Improve this answer
2
  • 1
    I do think that Excel should have told me that this was the problem when I used the formula dialog box, but the problem as a whole was not Excel's fault. Except that it uses operating system settings for this instead of it being a setting in Excel's options. Honestly, this should be adjustable on a per excel save file basis in case I ever come across a spreadsheet that uses semi-colon.
    – Zarquan
    Sep 23, 2020 at 3:01
  • 1
    Thank you for posting your solution. I have passed the question to the Microsoft Excel team. I think the behaviour you experienced is a bug.
    – teylyn
    Oct 1, 2020 at 3:50
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.

enter image description here

Improve this answer
1
  • I tried this, but weird things happened. I edited the original question with this approach.
    – Zarquan
    Sep 22, 2020 at 21:43
0

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 https://stackoverflow.com/questions/4378047/vba-equivalent-to-excels-mod-function
 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

Improve this answer
1
  • This seems unrelated to this question.
    – Zarquan
    Mar 6, 2022 at 9:30
0

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.

Improve this answer
2
  • Yes, a comma might not be the best universal solution, but perhaps the setting should be localized to the excel file rather than to the computer as a whole. If I have crazy settings, I should be able to still take my excel and use it on another computer. Any default (which I do not believe I changed) should also work out of the box.
    – Zarquan
    Jun 3, 2022 at 17:56
  • Note that my computer was configured to not use the comma as a function delimiter nor was it used as a decimal symbol, it was configured to use tab as a function delimiter and period as a decimal delimiter. Using tab as a delimiter is an illegal operation in Excel.
    – Zarquan
    Jun 3, 2022 at 18:13

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.