Today we present a practical, personal, non-business use for Microsoft Excel: Personal Fitness Performance and Progress. Sent in by a YouTube viewer, Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen look at keeping track of 'Pullups' in a Spreadsheet and then getting the weekly and Monthly totals. Follow along with Mike and Bill, in Episode #1692, to see different methods for arriving at the anticipated results.
Dueling Excel Podcast #120...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #120...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill: Hey welcome back, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel.
I'll be joined by Mike Girvin from ExcelIsFun, this is our episode 120. Total By Week and Month.
Alright, today's question sent in.
Trying to keep track of pull-ups, logs how many pull-ups he does every day, and wants to keep a weekly total, and a monthly total, weeks always start with Monday, even if it goes into last month.
OK, so I imagine what kind of, have a spreadsheet, dates going down the left-hand side, pull ups to the right.
Let's, well first off, weeks always start on Monday, so I can go grab the calendar on the wall, or I could just change this to a Long Date temporarily.
OK, so we know that that's a Tuesday, I'll undo, Ctrl+Z, and we want to start with 12/31/2012.
So this is going to be called Week of, and build the weeks.
I'm going to use the previous cell +7, let's copy that down, alright there we go, Week of.
Alright, to figure out how many pull-ups we did during the week from 12/31 to 01/06, I'm going to use =SUMIFS, the new plural version of SUMIF, they came along in Excel 2007.
So our sum range is going to be that range there, F4.
First criteria range, we'll go look at all of the dates, press F4, and see if they are >= , so watch this, I'm going to build this on the fly, ">="& the date to the left of me.
Alright, so that's going to build a little >= 1/1/2013.
This is a little frustrating, the next criteria range is the exact same one as before, but I still have to specify that, F4, comma, and this time the criteria is going to be "<"& the date in the next row.
Let's see, sum is 420, and totals to 420.
Alright, looks great, copy that down, good.
Alright, so that gives us our week up.
Now how about monthly?
So we'll do 1/1/2013, this time I'm going to fill the months by right-clicking and dragging the fill handle, and we will say "Fill Months".
Alright, the formula here, hey, you know what, it's the same as the formula here, we just copy it over, copy down.
Alright, rock and roll, this is a month.
If you don't want the dates here, let's go in, we'll do a Custom Number Format, Ctrl+1, and just in Custom we'll type "mmmm", there we go.
Alright Mike, let's see what you have! Mike: Thanks MrExcel! Man, the SUMIFS, that is an awesome function, and I'm going to use that function also, maybe do something slightly different with the dates, but here's what I want to know.
SUMIFS, right, MrExcel and I are both saying the new SUMIFS, well, it's been around since 2007, right.
And I know lots of people have 2003 and earlier.
But SUMIFS does so many amazing things, it's worth getting 2007 or later, one of the versions, just forgetting that function.
So at some point we're going to have to stop saying new, right, and just say the SUMIFS function.
Alright, I'm going to create my upper limit and lower limit for summarizing between two dates.
I'm going to create formulas for this, based on whatever date I type here.
So I'm going to start with the first of the month, I'm going to use the EOMONTH function.
Now this, in earlier versions, 2003 and before, you could get this function if you added the Data Analysis tool-pack under the Tools menu.
I'm going to say "Hey, that's the date", comma, now remember, I'm trying to figure out the first of March, and EOMONTH calculates the end of the month.
So watch this, I'm going to say "months", go -1, this will give me the end of the previous month.
And now I can simply +1.
Now that's the beginning, I need the end, so now I use the end of the month for what it's for.
I say "There's the serial date", ,0 says "Give me the end of this month!" And now I'm going to use the same beautiful formula that MrExcel did.
Now notice, I have a first and end of the month, whereas MrExcel had only the first of each month.
So my categories have to, the comparative operator has to be >= to the start date, because I want to include any of those dates, and <= to the end date, because I have to include those also.
Alright, now what are we going to do for weeks?
I'm going to do the same thing, beginning of the week, end of the week, both inclusive, lower and upper limit, both have to be included.
Well, this is Friday, right, so if I were going to take this date and subtract a number of days, how many days would I have to go backwards to get to Monday?
Well 4, right, Friday, Thursday, Wednesday, Tuesday, Monday, that's 4 days.
So we can use the WEEKDAY function.
Now let's take a look at the WEEKDAY function.
Absolutely awesome, I give it a serial number, and then look at this, return type, look at that beautiful drop-down.
Now remember, our goal was to -4 from a Friday, and look at this, if we use 3, Monday is 0, Tuesday is 1, 2, 3, 4, would be Friday, all the way until 6 is Sunday.
Absolutely beautiful, exactly what we want here.
I just have some dates here, right, just to show you, and there it is.
When we get to Friday, we subtract the actual serial date, subtract from it, the WEEKDAY result 4, absolutely what we want.
So ready?
equals, ,3 , look at that, and that will always give us a Monday, so whatever we type in here, this will change, this will change.
Now this, I'm going to +6.
Now I want to copy this down, but the problem for the way I'm attempting to do this, right, have any date here, the month, and all the weeks, is sometimes we might get 5, 4, or 6, right?
So I need to do something here, because I could just go like this, +7 and then copy this down, for this particular month I'll get one too many.
For September, if I do this here(?), that would work, right, because if I'm going back to the previous Monday, I'm going to have 6 weeks, Ctrl+Z.
So I'm going to change this right here, and add an IF.
I'm going to say "If the MONTH", and the MONTH will give me a number from 1-12, "If the MONTH of this +7", right.
So I want to know if that date +7, the month of that, is equal to the month of this, a locked F4.
If that's TRUE, then I'm running that right there, otherwise, let's show a null text string, "", and so that will turn that off.
If I change this to 9, it will work just fine, Ctrl+Z.
I'm going to have to do something similar right here, =IF this equals, and I've already established that there's going to be a null text string, if it's a null text string, then please show another null text string, otherwise run that.
And lo and behold, I already have the SUMIF out here, it's that same exact formula just like MrExcel did it here, and copied here because we have lower and upper limits in this formula here, they're both included.
If you're in earlier versions, you didn't have SUMIF, you could use SUMPRODUCT, the absolutely awesome SUMPRODUCT.
Alright, throw it back over to MrExcel! Bill: Hey, alright Mike! Boy, EOMONTH, I love that, the WEEKDAY function, pretty slick.
Hey, as I was thinking about this, you know, if he's going to be entering new data as he goes along, those formulas that I wrote are going to have to be adjusted.
It might make sense to Ctrl+T and make this into a table here.
So let's check this out, this formula that's currently going through row 90, let's go down to the bottom, and we'll add some more dates, grab the fill handle, and drag.
I'll do a 1000 each day just to make sure that it's really going up, and come back up, and you see that our formulas now are automatically extending.
I guess that's a critical piece if he's going to be entering more pull-up data each day.
Alright well hey, I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
I'm Bill Jelen from MrExcel.
I'll be joined by Mike Girvin from ExcelIsFun, this is our episode 120. Total By Week and Month.
Alright, today's question sent in.
Trying to keep track of pull-ups, logs how many pull-ups he does every day, and wants to keep a weekly total, and a monthly total, weeks always start with Monday, even if it goes into last month.
OK, so I imagine what kind of, have a spreadsheet, dates going down the left-hand side, pull ups to the right.
Let's, well first off, weeks always start on Monday, so I can go grab the calendar on the wall, or I could just change this to a Long Date temporarily.
OK, so we know that that's a Tuesday, I'll undo, Ctrl+Z, and we want to start with 12/31/2012.
So this is going to be called Week of, and build the weeks.
I'm going to use the previous cell +7, let's copy that down, alright there we go, Week of.
Alright, to figure out how many pull-ups we did during the week from 12/31 to 01/06, I'm going to use =SUMIFS, the new plural version of SUMIF, they came along in Excel 2007.
So our sum range is going to be that range there, F4.
First criteria range, we'll go look at all of the dates, press F4, and see if they are >= , so watch this, I'm going to build this on the fly, ">="& the date to the left of me.
Alright, so that's going to build a little >= 1/1/2013.
This is a little frustrating, the next criteria range is the exact same one as before, but I still have to specify that, F4, comma, and this time the criteria is going to be "<"& the date in the next row.
Let's see, sum is 420, and totals to 420.
Alright, looks great, copy that down, good.
Alright, so that gives us our week up.
Now how about monthly?
So we'll do 1/1/2013, this time I'm going to fill the months by right-clicking and dragging the fill handle, and we will say "Fill Months".
Alright, the formula here, hey, you know what, it's the same as the formula here, we just copy it over, copy down.
Alright, rock and roll, this is a month.
If you don't want the dates here, let's go in, we'll do a Custom Number Format, Ctrl+1, and just in Custom we'll type "mmmm", there we go.
Alright Mike, let's see what you have! Mike: Thanks MrExcel! Man, the SUMIFS, that is an awesome function, and I'm going to use that function also, maybe do something slightly different with the dates, but here's what I want to know.
SUMIFS, right, MrExcel and I are both saying the new SUMIFS, well, it's been around since 2007, right.
And I know lots of people have 2003 and earlier.
But SUMIFS does so many amazing things, it's worth getting 2007 or later, one of the versions, just forgetting that function.
So at some point we're going to have to stop saying new, right, and just say the SUMIFS function.
Alright, I'm going to create my upper limit and lower limit for summarizing between two dates.
I'm going to create formulas for this, based on whatever date I type here.
So I'm going to start with the first of the month, I'm going to use the EOMONTH function.
Now this, in earlier versions, 2003 and before, you could get this function if you added the Data Analysis tool-pack under the Tools menu.
I'm going to say "Hey, that's the date", comma, now remember, I'm trying to figure out the first of March, and EOMONTH calculates the end of the month.
So watch this, I'm going to say "months", go -1, this will give me the end of the previous month.
And now I can simply +1.
Now that's the beginning, I need the end, so now I use the end of the month for what it's for.
I say "There's the serial date", ,0 says "Give me the end of this month!" And now I'm going to use the same beautiful formula that MrExcel did.
Now notice, I have a first and end of the month, whereas MrExcel had only the first of each month.
So my categories have to, the comparative operator has to be >= to the start date, because I want to include any of those dates, and <= to the end date, because I have to include those also.
Alright, now what are we going to do for weeks?
I'm going to do the same thing, beginning of the week, end of the week, both inclusive, lower and upper limit, both have to be included.
Well, this is Friday, right, so if I were going to take this date and subtract a number of days, how many days would I have to go backwards to get to Monday?
Well 4, right, Friday, Thursday, Wednesday, Tuesday, Monday, that's 4 days.
So we can use the WEEKDAY function.
Now let's take a look at the WEEKDAY function.
Absolutely awesome, I give it a serial number, and then look at this, return type, look at that beautiful drop-down.
Now remember, our goal was to -4 from a Friday, and look at this, if we use 3, Monday is 0, Tuesday is 1, 2, 3, 4, would be Friday, all the way until 6 is Sunday.
Absolutely beautiful, exactly what we want here.
I just have some dates here, right, just to show you, and there it is.
When we get to Friday, we subtract the actual serial date, subtract from it, the WEEKDAY result 4, absolutely what we want.
So ready?
equals, ,3 , look at that, and that will always give us a Monday, so whatever we type in here, this will change, this will change.
Now this, I'm going to +6.
Now I want to copy this down, but the problem for the way I'm attempting to do this, right, have any date here, the month, and all the weeks, is sometimes we might get 5, 4, or 6, right?
So I need to do something here, because I could just go like this, +7 and then copy this down, for this particular month I'll get one too many.
For September, if I do this here(?), that would work, right, because if I'm going back to the previous Monday, I'm going to have 6 weeks, Ctrl+Z.
So I'm going to change this right here, and add an IF.
I'm going to say "If the MONTH", and the MONTH will give me a number from 1-12, "If the MONTH of this +7", right.
So I want to know if that date +7, the month of that, is equal to the month of this, a locked F4.
If that's TRUE, then I'm running that right there, otherwise, let's show a null text string, "", and so that will turn that off.
If I change this to 9, it will work just fine, Ctrl+Z.
I'm going to have to do something similar right here, =IF this equals, and I've already established that there's going to be a null text string, if it's a null text string, then please show another null text string, otherwise run that.
And lo and behold, I already have the SUMIF out here, it's that same exact formula just like MrExcel did it here, and copied here because we have lower and upper limits in this formula here, they're both included.
If you're in earlier versions, you didn't have SUMIF, you could use SUMPRODUCT, the absolutely awesome SUMPRODUCT.
Alright, throw it back over to MrExcel! Bill: Hey, alright Mike! Boy, EOMONTH, I love that, the WEEKDAY function, pretty slick.
Hey, as I was thinking about this, you know, if he's going to be entering new data as he goes along, those formulas that I wrote are going to have to be adjusted.
It might make sense to Ctrl+T and make this into a table here.
So let's check this out, this formula that's currently going through row 90, let's go down to the bottom, and we'll add some more dates, grab the fill handle, and drag.
I'll do a 1000 each day just to make sure that it's really going up, and come back up, and you see that our formulas now are automatically extending.
I guess that's a critical piece if he's going to be entering more pull-up data each day.
Alright well hey, I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
