10. Compile Weekly Totals
Weekly data can be difficult to compile, particularly over multiple months. Using the WEEKNUM function, we'll count the number of sign ups for each week in January.
COUNTIF: Counts number of cells in a range that meet 1 criterion
WEEKNUM: Returns the week in which a date resides
CTRL + Page Up/Page Down: Move to next sheet
ALT + I, C: Insert column
ALT + O, C, A: Autofit column width
CTRL + SHIFT + #: Switch to General format
F2: Jump back into formula
CTRL + →: Move to end of data region
CTRL + SHIFT + →: Select all cells within data region
Online startups tend to run many different experiments to entice new users to sign up, and often want to measure the number of signups per week, if not per day.
Weekly data can be difficult to parse, particularly over the course of multiple months.
That is unless you know how to use the incredibly useful function WEEKNUM, which I'll show you in this lesson.
Our startup would like to find the number of signups in the first four weeks of the year, beginning from their launch date on the 1st of January.
While we could write some complex COUNTIF functions with multiple criteria to do this, instead we'll use WEEKNUM.
So let's go back to our data set, and create a new column called “Week Number”.
And I'll select this column and auto fit with Alt OCA.
We'll then write the function equals WEEKNUM, open a bracket, accept the date as our argument, close the bracket and press enter.
And when we do this we've a strange date as our output value.
But this is only because we have week number in the date format, when it should be in the general format.
So to switch it to general I'll use the shortcut Ctrl Shift #, and then we can auto fill for the remaining dates.
And as you can see, the WEEKNUM function simply assigns the week number for each of our dates, which is exactly what we want.
If we only pass one argument into the WEEKNUM function, it will assume that week one, day one is the 1st of January.
However, if you'd like your weeks to start from a specific day, you can specify this as a second argument in the formula.
So if I jump back into my WEEKNUM function F2, and write a comma, type in a number that represents weeks starting from a Sunday for one, Monday for two, etc.
As it currently stands, the first of January 2012 is a Sunday.
But if we wanted our weeks to start on a Monday, I would simply write 2 and then press enter.
When I auto fill for the remaining columns, you can see that the first of January 2012 is still week one which started on the 26th of December, which was the previous Monday.
And now the 5th of January would be week two instead of week one.
The choice of deciding whether to start your weeks on a specific day, or a specific date, really depends on your preference.
I'll return to our original format by undoing the change with Ctrl Z.
Let's now go to our other sheet and fill in the number of signups for each week.
So in the first cell I'll write equals COUNTIF, open the bracket, and select our range, which will be WEEKNUM.
I'll then press F4 to anchor this range.
We'll then write a comma and the criteria will simply be the week number.
I'll then close the bracket and press enter.
And this tells me that we had 71 signups in our first week.
If I paste formulas for the remaining cells with Alt ESF, you can see that we had a dip in signups in week two, but then steady signups again for week three and week four.
If the company was trying some new marketing campaigns in week two, this data suggests that they may not be as effective as the campaigns used in the other three weeks.
Although the company would probably need a larger data set to confirm this.
As you can see from this example, WEEKNUM is a great function for gathering data by week.
Another useful way to analyze our data set would be measuring the number of signups for each day of the week, which I'll show you how to do in the next lesson.