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!
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!
Transcript of the video:
Welcome back to MrExcel podcast, I'm Bill Jelen.
Today a note sent in by Dan, Dan goes back to a podcast from a few weeks ago and says; hey you really missed it there was a better way to do that, I love when I find better ways to do that.
Dan said you missed the mod function and I want to just briefly talk about the mod function, Dan has a great point; he says people don't think about it enough and I want to explain to you briefly how the mod function works, I want you to go back to elementary school when you were first learning how to do division, remember when you would have a problem like 14/5 and instead of providing a decimal, you would provide an answer that five goes into 14 two times with a remainder of 4.
The mod function is a great way to provide the remainder, so I have a simple spreadsheet here and going down column A I've used a function called equal row that gives me numbers 1 2 3 4 5 6, and maybe I'd like to use conditional formatting to set up some interesting banding formatting, we're basically there's three rows that are colored and then three rows that are white, and so I want to try and figure out whether this is the first second third fourth fifth or sixth row, and so what I do is I use the mod function; I say I want the mod of that row number divided by 6 so =MOD(ROW(),6), what it does is it does the division leaves off the integer portion of the quotient and gives me just the remainder, so 1/6, 6 goes into 10 times with a remainder of 1, I get the answer of one what's interesting in this is when I get down to row 6 it gives me a remainder of 0, so quickly to show you how to do the conditional formatting if I select several cells and use conditional formatting say the formula is =MOD(ROW(A1),6<3, choose a formatting I'll choose a dark pattern here click OK, click OK and very quickly we get the green bar effect with three rows on an three rows off, sibling up well Dan pointed out that way back in podcast 524 when we were trying to find out that last Saturday of the month I ended up using this kind of clunky if statement at the end that says hey my formula is working great and less the last Saturday of the month is the last day of the month, then I have to subtract 7.
I'm rather than use the weekday function and the If statement that I did back in podcast 524.
Dan said this is a great opportunity to use the mod function, so I take the weekday at the end of month function divided by seven, if it happens to be exactly seven which is what I was testing for in the If statement before, I get a zero I'm not subtracting an extra week so I want to thank Dan for pointing out the mod function, it's a great function that I have to admit I don't think of enough well I think was one of those hard concepts when we learned it in math and it was called modulo back then and it's now instituted in Excel in the mod function, and just think back to your very first days in division it gives you the remainder any time you're interested in the fractional portion after doing the division the mod function is a great way to go.
Hey thanks for stopping by, we'll see you next time for another netcast from MrExcel.
Today a note sent in by Dan, Dan goes back to a podcast from a few weeks ago and says; hey you really missed it there was a better way to do that, I love when I find better ways to do that.
Dan said you missed the mod function and I want to just briefly talk about the mod function, Dan has a great point; he says people don't think about it enough and I want to explain to you briefly how the mod function works, I want you to go back to elementary school when you were first learning how to do division, remember when you would have a problem like 14/5 and instead of providing a decimal, you would provide an answer that five goes into 14 two times with a remainder of 4.
The mod function is a great way to provide the remainder, so I have a simple spreadsheet here and going down column A I've used a function called equal row that gives me numbers 1 2 3 4 5 6, and maybe I'd like to use conditional formatting to set up some interesting banding formatting, we're basically there's three rows that are colored and then three rows that are white, and so I want to try and figure out whether this is the first second third fourth fifth or sixth row, and so what I do is I use the mod function; I say I want the mod of that row number divided by 6 so =MOD(ROW(),6), what it does is it does the division leaves off the integer portion of the quotient and gives me just the remainder, so 1/6, 6 goes into 10 times with a remainder of 1, I get the answer of one what's interesting in this is when I get down to row 6 it gives me a remainder of 0, so quickly to show you how to do the conditional formatting if I select several cells and use conditional formatting say the formula is =MOD(ROW(A1),6<3, choose a formatting I'll choose a dark pattern here click OK, click OK and very quickly we get the green bar effect with three rows on an three rows off, sibling up well Dan pointed out that way back in podcast 524 when we were trying to find out that last Saturday of the month I ended up using this kind of clunky if statement at the end that says hey my formula is working great and less the last Saturday of the month is the last day of the month, then I have to subtract 7.
I'm rather than use the weekday function and the If statement that I did back in podcast 524.
Dan said this is a great opportunity to use the mod function, so I take the weekday at the end of month function divided by seven, if it happens to be exactly seven which is what I was testing for in the If statement before, I get a zero I'm not subtracting an extra week so I want to thank Dan for pointing out the mod function, it's a great function that I have to admit I don't think of enough well I think was one of those hard concepts when we learned it in math and it was called modulo back then and it's now instituted in Excel in the mod function, and just think back to your very first days in division it gives you the remainder any time you're interested in the fractional portion after doing the division the mod function is a great way to go.
Hey thanks for stopping by, we'll see you next time for another netcast from MrExcel.
