MODULUS Function in Excel – Shiksha Online

The article will help you understand how the MODULUS function or MOD function in Excel works through some interesting datasets.

The MODULUS function in Excel will help you get the remainder of an inexact division. MOD function in Excel allows obtaining the remainder from the division of two numbers. It is a built-in function in MS Excel and is categorized as a Math/Trigonometry function. We can also use it as a worksheet function in Excel, which means that we can enter the MODULUS function in Excel as a part of a formula in a cell of a worksheet.

Content

• MODULUS Formula in Excel
• Examples – Use of the MODULUS function in Excel
  • Basic Calculations using the MOD function
  • MOD formula to sum every Nth row or column
  • Sum Every Nth Column
  • Concatenate Every Nth Column

MODULUS Formula in Excel

The syntax of the MOD function is:

=MOD(number, divisor)

Where, 

Number = The number for which you want to find the remainder. It is a mandatory argument.

Divisor = The number by which you will divide the Number. Another mandatory argument.

Remember –

• If the divisor is zero, the MOD function will return an error value, which is #DIV/0! 

• If the number or divisor is text, the MOD function returns the #VALUE! Error

Examples – Use of the MODULUS function in Excel

You can use the MOD function by placing the exact numbers that you want to divide in their respective arguments, or by indicating the numbers of the Excel cells on which you want to perform the division.

Read MS Excel Tutorials

Basic Calculations using the MOD function

In the below example, you can see that =MOD(12,5) returns 2 because when you divide 12 by 5, you get a quotient of 3 and a remainder of 2 (12=5*2+1). The formula MOD(12,2) returns zero because 12 is divided by 2 without remainder, similarly, 12 is divided by 3, 4, and 6 without any remainder.

MOD formula to sum every Nth row or column

Excel does not have any function to sum 2nd, 3rd, and so on rows. We can achieve this by incorporating the MOD function.

To sum every second row, we can use MOD with ROW and SUMPRODUCT:

Sum even rows:

=SUMPRODUCT((MOD(ROW(range),2)=0)*(range))

Sum odd rows:

=SUMPRODUCT((MOD(ROW(range),2)=1)*(range))

Assuming B3:B9 are the cells to sum, the formulas look as follows:

Even rows

=SUMPRODUCT((MOD(ROW($B$3:$B$9),2)=0)*($B$3:$B$9))

Odd rows

=SUMPRODUCT((MOD(ROW($B$3:$B$9),2)=1)*($B$3:$B$9))

The combination of MOD and ROW determines which rows to sum.

SUMPRODUCT adds up the values.

Sum Every Nth Column

If you have a dataset where you want to add up numbers in every Nth column, just replace ROW with COLUMN in the formula that we used in the previous example –

=SUMPRODUCT((MOD(COLUMN(range)-COLUMN(first_cell)+1,n)=0)*(range))

For the below dataset of the student’s scores in different exams, we have to calculate how much did they score in their quarterly and half-yearly exams. Here, we need to sum up numbers in every 4th column in the range B2:L2, you go with this formula:

=SUMPRODUCT((MOD(COLUMN($B2:$L2)-COLUMN($B2)+1,4)=0)*($B2:$L2))

Press enter and drag down to apply the formula to the remaining cells and get the annual result for every student.

Concatenate Every Nth Column

We can use the MOD formula to concatenate every N cell. This feature is particularly useful if some of the details of any product/item are spread across different cells. If you have a long list of such products then it gets a challenging task and here is where the MOD formula comes in. 

In the below example, we have a list of products within a retail company and the data needs to be sorted as per product details. These details are already available under Order Detail in Column B, and the MOD function will help to CONCATENATE these details to create the product IDs. 

We will use the MOD formula with the IF function.

=IF(MOD(ROW()-1, 3)=0, CONCATENATE(B2, “-“, B3, “-“, B4), “”)

You should note that you should enter the formula next to the last row of data, C4, here. Copy the formula down the column and the MOD function will concatenate the contents of the cells.

To create a formula like this, you first need to check if the row can be divisible by 3 and leaves no remainder.

=MOD(ROW()-1, 3)=0

We will then subtract 1 from the actual row number so that we get the relative position of the current row. So, IF, the remainder is 0, we will apply the concatenate function for B2, B3, and B4. These rows will be separated by a delimiter (“-” here). We use relative cell references to ensure that three different cells are concatenated every time the formula is used.

Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel