How to calculate remaining amounts using Excel MOD

One function in Excel that is rarely used but still extremely helpful is the MOD function. You can use this function to determine the remainder of two numbers after a division operation.

How do you use the MOD function in Excel?

If you want to determine the remainder after dividing one number by another, you can use the Excel MOD function to return this value. The formula looks like this:

=MOD(5,2)

In this case, the result of the division is the value ‘2’ because the integer 2 fits into 5 exactly twice. The Excel MOD function returns a remainder of 1. This cal­cu­la­tion may seem simple at first glance. However, the Excel MOD function is very helpful when you’re dealing with large sequences of numbers, and it’s virtually in­dis­pens­able for ensuring correct results.

The formula is simple enough. It consists of two parts that can be expressed as a cell reference or a specific value, as shown in the examples below. The first formula specifies two cell ref­er­ences, and the second formula consists of a cell reference and a specific number:

=MOD(A1,A2)

=MOD(A1,3)

An example of the Excel MOD function

The example below il­lus­trates how Excel’s MOD function works:

Close

Once again, the standard division operation is used. The pro­duc­tion output number is divided by the divisor 3 (the specific number given in the code above). If there is a remainder after the numbers are divided, this value is indicated in the row on the right.

In the spread­sheet below, the first column contains the number to be divided. The second column contains the divisor, which you want to divide the number by. This results in the following formula: MOD(Number;Divisor).

The Excel MOD function returns the error message #DIV/0! if the number zero is specified as the divisor because a number cannot be divided by zero. In the following example, the number 78 cannot be divided by the divisor 0, so Excel returns the #DIV/0! error message.

Close

The term ‘MOD’ refers to the modulo operation, which ori­gin­ates from Euclidean math­em­at­ics and is used when creating macros in Excel or cal­cu­lat­ing re­main­ders in common pro­gram­ming languages such as Python.

Where is the Excel MOD function used?

It’s worth taking a closer look at the possible uses of the MOD function in Excel. It is a practical tool for solving everyday problems, es­pe­cially when combined with other functions.

The Excel MOD function is used when you only want to count certain minimum quant­it­ies in formulas, meaning every Nth value. Typical ap­plic­a­tions include con­vert­ing or cal­cu­lat­ing units of quantity and length or de­term­in­ing remaining dates. The function can also be used in a number of other scenarios, for example in man­u­fac­tur­ing. Remnants left over due to stand­ard­ised packaging units or machine pro­duc­tion ca­pa­cit­ies can be de­term­ined with the MOD function in Excel. For example, you can dif­fer­en­ti­ate between re­cyc­lable and non-re­cyc­lable remnants.

In addition, you can combine the Excel MOD function with other Excel functions. The SUMIF function, can be used, for example, to determine whether there is any waste and to what extent it can be reused. This method can also be used for bundling volumes. By combining SUMPRODUCT function and the Excel MOD function, you can ensure optimum util­isa­tion of a shipping container, for example.