Share via

Excel division remainder modulo

Anonymous
2023-10-17T16:10:04+00:00

I recently had to use the modulodivide in power query,

and realized i did not know how to do this in excel; i came up with this which seems to work,

please say if not. but could I use floor in some way or is there a function just to do this.

Modulo 5 / X%5

Number -(5*(ROUNDDOWN(Number/5,0)))

Any suggestion / improvements.

Richard.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2023-10-17T16:13:13+00:00

    Excel has a MOD function and a QUOTIENT function

    =MOD(23, 5) returns 3

    =QUOTIENT(23, 5) returns 4

    because 5 goes 4 times into 23, leaving a remainder of 3.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2023-10-18T14:23:52+00:00

    It can be confusing. In maths and in VBA, we'd write 23 mod 5. But Excel does not recognize that. In Excel, MOD is a function with two arguments: MOD(number, divisor)

    0 comments
  2. Anonymous
    2023-10-18T12:26:14+00:00

    Thanks,
    I thought it odd that it did not , I had tried type Mod etc, either I couldn't see it or was typing
    wrong.

    Richard.

    0 comments