Excel - Unlock the Power of the MOD Function in Excel | Microsoft Excel Tutorial - Episode 541
Microsoft Excel Tutorial: Unlock the Power of the MOD Function in Excel.
Welcome back to another episode of the MrExcel podcast. I'm Bill Jelen and today we're going to be talking about the MOD function in Excel. This function was brought to my attention by one of our listeners, Dan, who pointed out that it can be a powerful tool that is often overlooked. So, let's dive in and see how the MOD function works.
To understand the MOD function, let's take a trip back to elementary school when we were first learning division. Remember how we would have a problem like 14/5 and instead of giving a decimal answer, we would provide the answer as "5 goes into 14 two times with a remainder of 4"? Well, the MOD function does just that - it gives us the remainder after dividing two numbers.
In this video, I'll be using a simple spreadsheet to demonstrate the MOD function. I have a column with numbers 1-6 and I want to use conditional formatting to create a banding effect where every three rows are colored and every three rows are white. To do this, I use the MOD function to determine which row I am on. For example, if I am on row 4, the MOD function will give me a remainder of 1 when I divide 4 by 3. This allows me to easily apply conditional formatting to achieve the desired effect.
But that's not all - Dan also pointed out that the MOD function can be used in place of a clunky IF statement when trying to find the last Saturday of the month. In a previous podcast, I used an IF statement to check if the last Saturday of the month was also the last day of the month, and if so, I had to subtract 7. However, with the MOD function, I can simply divide the weekday of the end of the month by 7 and if it equals 7, I know it's the last Saturday and I don't need to subtract 7. This not only simplifies the formula, but it also makes it more efficient.
So, thank you to Dan for bringing the MOD function to our attention. It's a great tool that I have to admit, I don't think of enough. But now that I have been reminded of its power, I will definitely be using it more often. And for all of you watching, I hope this video has helped you understand the MOD function and how it can be used in your own Excel projects. Thanks for tuning in and be sure to join us for our next episode of the MrExcel podcast.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Daan Sprunken (who was a runner up in one of the last challenges) writes in to mention that there is a better way to find the last Saturday of the month, as we discussed in podcast Episode 524. Daan points out that the MOD function would have made that formula easier and he is right. Not enough people think of MOD, so in Episode 541, I take a look at several different MOD examples.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Table of Contents:
(00:00) Introduction by Bill Jelen
(00:21) The mod function and its usefulness
(00:53) Example of using the mod function in a spreadsheet
(01:21) Conditional formatting using the mod function
(02:05) Using the mod function to find the last Saturday of the month
(02:33) Listener Dan's suggestion to use the mod function
(03:09) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Watch video Excel - Unlock the Power of the MOD Function in Excel | Microsoft Excel Tutorial - Episode 541 online without registration, duration hours minute second in high quality. This video was added by user MrExcel.com 29 July 2009, don't forget to share it with your friends and acquaintances, it has been viewed on our site 3,53 once and liked it people.