What is the MOD Function? The MOD Function[1] is categorized under Excel Math and
. The function helps find a remainder after a number (dividend) is divided by another number (divisor). As a financial analyst, the function is useful when we need to process every nth value.
1. Finding “residuals”: When modeling, there are times when you need the integer portion of a number, such as when determining how many payments fall between two dates, which may equal 9. 94 — but that’s nonsense. You would have only made nine payments in this situation, or INT(9 94). Similar to that, you might want to charge the fee for unpaid invoices. Using MOD(9. 94,1) you would obtain the result 0. 94, ie, the number after the decimal place. Note that 9. 94 – INT(9. 94) yields the same outcome in this case; the MOD approach is merely quicker.
3. Users frequently want to sum each nth row (for example, the second, third, fourth, etc.) in a spreadsheet. Excel doesn’t have a built-in function that will perform this, but MOD can help. In cell H53 of the following example, the array formula =SUM(IF(MOD($E$19:$E$48,$G$13)=0,$F$19:$F$48,0)) was used. To get this formula, press Ctrl+Shift+Enter to get the braces and.
After explaining MOD, I can now demonstrate a really effective application of this function to a scenario that causes many people working in the finance industry considerable distress. You can get my Excel file from this solution, which integrates with OFFSET, by clicking here. Let me just give you a brief reminder about our trusty OFFSET:
Using this method, various scenarios can be easily modelled by inserting a different set of input data into each column (starting with column L in this example). The active scenario is chosen using a selector (cell J11 in the illustration above), which can then be highlighted using conditional formatting (more on this later).
Clearly, the different scenarios can be laid out very easily using a columnar approach in this case. However, the majority of financial models use columns to display dates rather than rows to display dates that run down the page. In order to do this, we must transpose the data, and once more, we can do this by using OFFSET:
Actual data frequently replaces the original information when it is entered into a model. As a result, management is unable to determine how accurate the initial forecasts were and how budgeting could be made better. Have “Actuals” as one of the scenarios as a workaround to ensure that all forecasts are kept. Often, this is all that is necessary, so just do that.
Rows 9 through 13 in the screenshot above are just a review of the calculations that were previously discussed in relation to the initial forecasting. However, take note of row 18, where actual data is added. In this illustration, I just use hard-coded inputs for my data, but you could easily modify this methodology to update growth rates, etc.
Specifically, if data is present in the corresponding cell in row 18, use it; otherwise, if it is the first period, use the original input value; otherwise, simply multiply the amount from the prior period by (1 + the period’s growth rate). Even though it may contain nested IF statements, the calculation is still fairly basic and uncomplicated.
I’ve been a model reviewer for a number of years, and I’ve seen this kind of output very frequently. It is not my responsibility to challenge the status quo because many senior management teams prefer it that way, but that doesn’t stop me from trying!
— Director of SumProduct, a global consultancy that specializes in Excel training, is Liam Bastick, FCMA, CGMA, FCA. He is also the creator of Introduction to Financial Modeling and an Excel MVP (as designated by Microsoft). Send ideas for future Excel-related articles to him at liam. bastick@sumproduct. com. Contact senior editor Jeff Drew at [email protected] with any comments or ideas for additional articles. Drew@aicpa-cima. com.
Excel MOD Function – Easy Explanation & Practical Examples of MOD for Work
What kind of data would you use the Excel MOD function for?
If you frequently divide numbers in Excel and need to find remainders, the MOD function can be a useful tool for automating your work. Here are some scenarios in which you might want to employ the MOD function:
What does the Excel MOD function do?
You can determine the remainder after dividing two numbers using the Excel MOD function. The amount left over after the number has been divided by the divisor as many times as possible using only whole numbers is known as the remainder when doing division. For instance, since the highest number that can be divided by two is eight, the remainder of nine when divided by two would be one. The number and divisor are the two main values and variables that you enter into the formula to carry out the calculation.
The formula for the Excel MOD function reads as follows: MOD(number,divisor)
The value that you want to divide by the variable, or the “divisor,” is denoted by the term “number” in this formula. You can either type the number directly into the variables for this formula, or you can use the cell that contains the value you want to reference.
How to use the Excel MOD function
Here are the steps to using the Excel MOD function:
Examples of how to use the Excel MOD function
Here are a few examples of various applications for the Excel MOD function:
Example #1
When creating a spreadsheet for your moving company, you need to determine how much room will remain in your shipping truck once you’ve loaded it with boxes. The shipping truck has 22 square feet of space to hold containers, and each box is 4 square feet. To find out how much space is left, use the MOD function.
Put MOD(number,divisor) in the formula bar of your labeled cell to begin setting up the formula in your spreadsheet. Next, fill in your variables. Your equation should look like this: MOD(22,4). Excel will output a value of 2 after performing the calculation because that is what is left over after dividing 22 by 4. If you cram as many boxes into the truck as you can, there will still be 2 square feet of space.
Example #2
Your manager requests that you create a spreadsheet to share with the rest of the company. After performing division, the spreadsheet must be able to locate the unsolved portion of an equation. They want it to be accessible to all employees and universal.
A chart should first be set up and labeled so that your coworkers can enter the values themselves.
You should label the cell and enter the formula in the cell’s formula bar. ** Substitute the cells you want your coworkers to use for the variables in your formula. For example, it might be MOD(A2,B2). The function will divide the value in cell A2 by the value in cell B2 as a result. This eliminates the need for anyone in your company to create the formula in order to use the spreadsheet.
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
FAQ
What is MOD function in Excel with example?
- Summary. The remainder of two numbers after division is returned by the Excel MOD function. For example, MOD(10,3) = 1.
- Get the remainder from division.
- The remainder.
- =MOD (number, divisor)
- number – The number to be divided. divisor – The number to divide with.
