Excel MOD – calculating remaining amounts using the MOD function in Excel

Excel is a popular application that has many useful functions for spreadsheets. If you want to impress your co-workers or use your time at the office more efficiently, you should definitely check out Excel’s range of functions.

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. For example, the MOD function is helpful for calculating the number of usable remainders from production runs and can be very useful when you need to purchase packaging units.

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. In practice, 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 calculation 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 indispensable for ensuring correct results.

Note

The remainder is always returned with the same sign as the divisor. If the sign is negative, the final value will also be negative.

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 references and the second formula consists of a cell reference and a specific number:

=MOD(A1,A2)
=MOD(A1,3)
Note

The Excel MOD function works the same way in many spreadsheet applications, whether you’re using Excel or Google Sheets or another program.

The Excel MOD function – an example

The example below illustrates how Excel’s MOD function works:

Once again, the standard division operation is used: The production 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 spreadsheet below, the first column contains the number to be divided. The second column contains the divisor by which you want to divide the number. This results in the following formula: MOD(number,divisor).

The Excel MOD function returns the error message #DIV/0! if 0 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.

The term 'MOD' refers to the modulo operation, which originates from Euclidean mathematics and is used for creating macros in Excel or calculating remainders in common programming 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. Especially when combined with other functions, it is a practical tool for solving everyday problems.

The Excel MOD function is used in formulas when you only want to count certain minimum quantities, meaning every Nth value. Typical applications include calculating packaging units or production lots, converting or calculating units of quantity and length or determining remaining dates.

In addition, you can combine the Excel MOD function with other Excel functions such as the SUMIF function, the SUMPRODUCT function or the SUBTOTAL function.

Let’s say you have a predefined number of units that are used in the production of a batch. In that case, you might have remainders. This could be because your machines have to produce a certain quantity in order to be profitable, or because only certain quantities can be packed in standardised packaging units. You can use the MOD function in Excel to calculate these remainders. By doing this, you can distinguish between usable and non-usable remainders, for example. You can also use the SUMIF function to determine whether there is waste and to what extent it can be reused.

This method can also be used when you need to consolidate large volumes of products. For example, by combining the SUMPRODUCT and the Excel MOD function, you can ensure optimal utilisation of an intermodal shipping container.

Finally, the Excel MOD function can also be useful for money transfers. For small amounts, transfers are sometimes too expensive. If a customer orders very little one month and a lot the next month, you can use the Excel MOD function together with the SUMIF and SUBTOTAL function to combine amounts or consolidate large volumes of specific product categories. This will reduce your administrative workload.


Own your online success
IONOS takes care of your online challenges
Create your website with the perfect domain and the UK's fastest hosting!