how to get modulus in excel vba on a cell

The same concept works when calculating the sum of even numbers. SUMPRODUCT function then only adds those numbers that are returning a TRUE (i.e., odd numbers). If the number is odd, it would return a TRUE, else a FALSE. This formula works by calculating the remainder using the MOD function.įor example, when calculating the sum of odd numbers, (MOD($A$2:$A$11,2)=1) would return an array of TRUEs and FALSEs. Similarly, if you want to add only the odd numbers, use the below formula: =SUMPRODUCT($A$2:$A$11*(MOD($A$2:$A$11,2)=1)) If your data is likely to change, you can use an Excel Table or created a dynamic named range. Note that here I have hard coded the range ($A$2:$A$11). Here is the formula you can use to add only the even numbers: =SUMPRODUCT($A$2:$A$11*(MOD($A$2:$A$11,2)=0)) Suppose you have a dataset as shown below: Example 1 – Add Only the Odd or the Even Numbers Here are useful examples of how you can use the MOD function in Excel. For example, if you use the function =MOD(10.5,2.5), it will return 0.5. You can use decimal numbers in the MOD function.The result always has the same sign as that of the divisor.

If the divisor is 0, then it will return the #DIV/0! error.

divisor – A number with which you want to divide the number argument.

number – A numeric value for which you want to find the remainder.

It returns a numerical value that represents the remainder when one number is divided by another. MOD function can be used when you want to get the remainder when one number is divided by another number.

Example 4 – Highlight All the Integer /Decimal Values.

Example 3 – Highlight Alternate Rows in Excel.

Example 2 – Specify a Number in Every Nth Cell.

Example 1 – Add Only the Odd or the Even Numbers.

Examples of Using MOD Function in Excel.

How to get modulus in excel vba on a cell