Question

Excel RESIDUE: calculate remaining sums

Answer 1

How do you use the RESIDUE function in Excel?
Excel RESIDUE (MOD): practical example
When is Excel's RESIDUE function used?

---

Excel RESIDUE: calculate remaining sums

The popular Excel program has some useful functions for spreadsheets. If you want to surprise your co-workers or want to manage your office hours more efficiently, we recommend that you take a look at the list of Excel tricks..

One of the least used functions in Excel, although extremely practical, is the RESIDUE function , also known as the Excel MOD function or the Excel Module function . With this function you can find out the remaining sum of two numbers after doing a division. When put into practice, it is ideal for calculating, for example, usable waste in manufacturing batches and proves its usefulness when purchasing certain packaging units.

Index

1. How do you use the RESIDUE function in Excel?
2. Excel RESIDUE (MOD): practical example
3. When is Excel's RESIDUE function used?

How do you use the RESIDUE function in Excel?

If we want to find out what remainder we have left after doing a division , we can get this value with the Excel MOD function. Let's look at a practical example of what the formula looks like:

  =REST(5,2)  

In this case, the result of the division is? 2 ?, since the number 2 fits twice inside 5. Excel's MOD function calculates the remainder of the division: 1. This calculation may seem simple at first glance, but Excel's RESIDUE function is incredibly practical when dealing with large series of numbers and indispensable if you want to get a correct result..

Note

The remainder is always found with the sign of the divisor. If it is negative, the final value will be as well.

The formula is easily constructed. It consists of two parts that can be indicated as a cell reference or by specific values, as in the following example (first, with two cell references and then with a cell reference and a specific number)..

  =REST(A1,A2)  

  =RESIDUO(A1,3)  

Note

Excel's RESIDUE function works in many spreadsheets with the same schema, it doesn't matter whether it is Excel or Google Sheets or any other program.

Excel RESIDUE (MOD): practical example

The following practical example explains how the RESIDUE (MOD) function works:

The Excel example shows the calculation of quantities remaining in production.

As already happens in division , the following also applies in this case: the production figure is divided by the proposed divisor 3 (see the specific number in the code indicated above). If the division yields a remainder, it is indicated in the cell to the right.

In the following table, the first column shows the number to be divided. The second column shows the divisor by which you want to divide. We obtain the following formula: RESIDUE (Number; Div_Num) .

Excel's RESIDUE function issues the error message # DIV / 0! if you indicate? 0? as a divisor, since you cannot divide a number by zero. In the following example, it is not possible to divide the number? 78? between the divisor? 0 ?, so the error message # DIV / 0! appears.

If you enter? 0? as a divisor, Excel's MOD function will give you an error message.

In English, Excel's RESIDUE function is called? MOD ?. The name comes from the mathematical function? Modulo Operation ?, a type of calculation in Euclidean mathematics that is used to create macros in Excel or in programming languages ​​such as Python that usually use English.

The following screenshot shows an example of the English MOD version:

Excel's RESIDUE function corresponds to the English MOD function.

When is Excel's RESIDUE function used?

It is worth knowing in detail the MOD function of Excel. In combination with other formulas, it provides practical solutions for many day-to-day problems.

Excel's RESIDUE function is used in formulas in which only certain minimum amounts are counted, that is, only each x value. The most common examples are calculations of handling units or production batches , the conversion or calculation of quantity and length units, as well as date residuals .

Additionally, Excel's RESIDUE function can be combined with other formulas , for example, with the SUMIF function, the SUMPRODUCT formula, or the SUBTOTALS function.

Let's imagine you have a predetermined number of units to be used in the production of a batch. This is a situation in which residual stocks can be generated , either because machines need to produce a certain quantity to be profitable or because regulations only allow certain quantities to be packed. Excel's RESIDUE function allows you to calculate these residual stocks. Thus, you can distinguish between usable and unusable remains. In addition, the SUMIF function allows you to determine if there is waste and if it can be reused.

This method also works for grouping volumes : the combination of the SUMPRODUCT formula and the WASTE function in Excel makes it possible to guarantee, for example, the optimal loading of a ship container.

Finally, Excel's RESIDUE function can also be useful when making transfers . With low amounts, transfers are often too expensive. If a customer places very few orders in a month, but makes many the following month, you can use Excel's RESIDUE function with the SUMIF function and SUBTOTAL function to effectively group amounts or volumes of product categories and reduce the effort in the office.

---