How To Use the MOD Function in Excel (With Examples)

1. Obtaining “residuals”: In some instances in modelling, you need the integer part of a number, eg, how many payments fall between two dates may calculate as 9.94 — but that’s nonsense. In this instance, you would have made only nine payments, ie, INT(9.94). Similarly, you might want to accrue the fee for payments not yet made. 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) gives the same result here; the MOD approach is simply shorter.

3. Summing every nth row: It is not uncommon for users to want to sum every nth cell (eg, second, third, fourth, …) in a spreadsheet. Excel has no standard function that will do this, but MOD can come to the rescue. For example, the array formula (obtained by pressing Ctrl+Shift+Enter to get the braces { and }), {=SUM(IF(MOD($E$19:$E$48,$G$13)=0,$F$19:$F$48,0))} was used in cell H53 in the following example:

Now that I have explained MOD, I can show you a really good use of this function for a situation that causes many working in finance plenty of consternation. You can download my Excel file with this solution, which works in conjunction with OFFSET. Let me just remind you of our old friend the OFFSET, as follows:

This method allows for various scenarios to be modelled easily with a different set of input data inserted into each column (from column L onwards in this illustration). A selector (cell J11 in the figure above) is used to select the active scenario, which may be highlighted using conditional formatting (see later).

Clearly, using a columnar approach here makes it very straightforward to set the various scenarios out. However, most financial models are displayed with dates going from left to right across columns rather than down the page using rows. This requires us to transpose the data, and again we may use OFFSET to transpose the data:

When actual data is inputted into a model, it frequently replaces the original information. Management, therefore, loses the ability to see how accurate forecasts were originally and how budgeting may be improved. One way around this would be to simply have “Actuals” as one of the scenarios so that all forecasts are retained. This is often all that is required, and if so, simply do that.

Rows 9 to 13 in the screenshot above simply reiterate the calculations already detailed earlier regarding the original forecasting. Note row 18, however: This is where actual data is added instead. In this example, I simply use hard-coded inputs for my data, but it only requires a simple variation to this methodology to revise growth rates, etc.

ie, if there is data in the corresponding cell in row 18, use it; if not, if it is the first period take the original input value, otherwise simply inflate the prior-period amount by (1 + growth rate for that period). It may include a nested IF statement, but it is still a relatively simple and straightforward calculation.

Seem familiar? I have been a model reviewer for many a year and have seen this type of output on an extremely regular basis. Many senior management teams like it this way, and it is not my role to challenge the status quo — it doesn’t stop me from trying, though!

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at [email protected]. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at [email protected].

What is the MOD Function? The MOD Function[1] is categorized under Excel Math and Trigonometry functions

Trigonometry functions
The sine function refers to the ratio of the perpendicular arm to the hypotenuse of any point in the unit circle – i.e., for any non-negative real number x, if a line is drawn from the origin to the boundary of the unit circle such that the angle between the line and the horizontal axis is x, then the sine function … › sin…

. 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.

Excel MOD Function – Easy Explanation & Practical Examples of MOD for Work

What kind of data would you use the Excel MOD function for?

Using the MOD function can be a good way to help automate your work if you regularly use division in Excel and need to find remainders. Here are some examples of situations that you may want to use the MOD function in:

What does the Excel MOD function do?

The Excel MOD function allows you to find the remainder after dividing between two numbers. When dividing, the remainder is the amount that is left after the number has been divided as many times as possible by the divisor using only whole numbers. For example, the remainder of 9 divided by 2 would be 1, since the highest number that is divisible by 2 is 8. T he number and divisor are two main arguments, or values and variables, that you place into the formula to perform the calculation.

When writing out the Excel MOD function, the formula looks like this:

In this formula, “number” represents the value that you want to divide by the variable, or the “divisor.” When filling in the variables for this formula, you can either write the number directly or simply use the cell containing 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 some examples of different ways that you can use the Excel MOD function:

Example #1

Youre working on a spreadsheet for your moving company and need to find out how much space youll have left in your shipping truck after filling it up with boxes. The boxes are 4 square feet each and the shipping truck has 22 square feet of space to hold containers. You want to use the MOD function to determine how much space is left.

First, set up the formula in your spreadsheet by entering MOD(number,divisor) in the formula bar of your labeled cell. Next, fill in your variables. Your equation should look like this: MOD(22,4). After running the calculation, Excel will return a value of 2, since that is the remainder of 22 divided by 4. There will be 2 square feet of space left if you fit as many boxes as possible into the truck.

Example #2

Your manager wants you to design a spreadsheet that you can share with your company. The spreadsheet needs to be able to find the remainder of an equation after performing division. They want it to be universal for all employees to be able to use.

Start by setting up a chart and label it so that your coworkers can fill in the values themselves.

Label the cell that you want to enter the formula in and enter it in the cells formula bar.** Replace the variables in your formula with the cells you want your coworkers to use. For example, it might be MOD(A2,B2). This will have the function divide the value in the cell A2 by the value in the cell B2. This way, anyone in your company can use the spreadsheet without needing to set up the formula themselves.

Please note that none of the companies mentioned in this article are affiliated with Indeed.


What is MOD function in Excel with example?

Excel MOD Function
  1. Summary. The Excel MOD function returns the remainder of two numbers after division. For example, MOD(10,3) = 1. …
  2. Get the remainder from division.
  3. The remainder.
  4. =MOD (number, divisor)
  5. number – The number to be divided. divisor – The number to divide with.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *