4 Excel MOD Function Examples – Excel Formula Tutorial

The MOD function of Excel is used to return the remainder after one number is divided by another. Simple, but there are some very useful examples of the MOD function.

In this blog post we explore 4 Excel MOD function examples. Hopefully this will help to see how versatile this function can be.

To start we better have a quick introduction to how to use the MOD function. It looks like this;

=MOD(number, divisor)

You need to provide it first with the number you want to divide, and then the number you want to divide it by (divisor).

You can see some basic examples of this below.

Basic MOD function examples

Ok, lets get on with some more impressive real-world MOD function examples. Continue reading

Excel Formula for Elapsed Time in Days, Hours and Minutes

Having to calculate date and time difference in Excel is a common task. Unfortunately, depending on your requirements it is also not always a simple one.

Take this example where column A contains a start date and time, and column B an end date and time. We wish to calculate the elapsed time in days, hours and minutes e.g. 11 days 4 hours 9 minutes.

Difference between two date and times

There are multiple ways of calculating date and time difference in Excel. In this scenario we will need to get a little clever.

As you may well know, date and time values are stored as numbers in Excel. For example, the 05/01/2017 10:10 is stored as 42740.42.

Therefore, if I write the formula as =B2-A2, then the result is returned as 2.993056.

To return a result that makes sense to us, we will tackle the date and time parts of the cell separately. Continue reading

Sum Every Nth Row in a List

At some point you may need to sum the value from every nth row in a large list. Excel does not provide a function for this. Excel has a few different Sum functions, but not one to sum the value from every other, or every third, fourth, or fifth row in a list.

The spreadsheet below contains totals in every fifth row starting from row 3. We want to only add these sales totals.

Continue reading

Check if a Value is an Even Number

I am currently working on an Excel VBA project that requires me to check if the number obtained by Excel is an even number, or if it is an odd number.

I accomplished this using the Mod operator. The Mod (modulus) operator is the VBA equivalent to the MOD function in Excel. It’s purpose is to find the remainder after a number has been divided by a divisor.

The Mod operator is written as below where a and b  represent variables containing values;

Continue reading