11. Analyse Data by Day of the Week

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

A clever way to understand user behaviour is to monitor their activity by day of the week. In Excel, the best way to do this is with the TEXT function

Lesson Notes

New functions

COUNTIF: Counts number of cells in a range that meet 1 criterion
=TEXT(value, "format_text"): converts date to different format
---value = the date to be converted
---format_text = the output format type

Format types for the TEXT function

mm: Displays the month as a number with a leading zero when appropriate
mmm: Displays the month as an abbreviation (Jan to Dec)
mmmm: Displays the month as a full name (January to December)
mmmmm: Displays the month as a single letter (J to D)
d: Displays the day as a number without a leading zero
dd: Displays the day as a number with a leading zero when appropriate
ddd: Displays the day as an abbreviation (Sun to Sat)
dddd: Displays the day as a full name (Sunday to Saturday)
yy: Displays the year as a two-digit number
yyyy: Displays the year as a four-digit number

Keyboard shortcuts

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
F4: Anchor cells
CTRL + →: Move to end of data region
CTRL + SHIFT + →: Select all cells within data region
CTRL + SPACEBAR: Select column
ALT + E, D: Delete selected column(s)
CTRL + SHIFT + ENTER: Implement array formula

Transcript

To gain insights into its target market, our startup would like to find out on what days new users are most likely to sign up.

This information can help create more effective marketing campaigns, and also let the startup know when their servers are likely to experience a spike in activity.

To analyze our dates by day of the week we'll need a function that converts dates into the day of the week, and this function is called Text.

So let's go to our data set and we'll create a new column.

And I'll call it Day of the Week.

And we'll auto fit so that it fits in our column.

I'll then write "= text", open a bracket, and pass in the date as our first value.

The second argument will be a string that converts the date into the day of the week.

The string that does this is four d's.

So I'll open double quotes, write four d's, close the double quotes, close the bracket, and press Enter.

And as you can see, this converts our date into the day of the week.

When we autofill for the remaining columns all we'll need to do is now write our countif function, so we'll return to the results page and I'll write "=countif", open the bracket, select our range, which will be Day of the Week, anchor it with F4, and then comma, and write our criteria, which will simply be Monday.

Close the bracket, and press Enter.

Then I can copy and paste the formulas for the remaining cells.

From our results, it doesn't appear that user signups are concentrated on one or two days.

Instead it tells us that users are likely to sign up on almost any day.

This in itself is valuable information for the startup.

The Text function in this scenario is used to calculate the day of the week.

But in fact we can use the Text function to convert dates into many different formats simply by changing the string passed to the function.

For example, we could output the month associated with the date by passing four m's instead of four d's.

In the show notes below the video I'll leave a list of the different strings the Text function can accept to show different output formats.

You might be thinking at this stage that our data set is getting a little unwieldy with all of these new columns, such as Week Number and Day of the Week, that I've added. Well it's possible to include functions such as Week Num and Text in our countif calculations and remove these columns from our data set.

Let's start off with our Day of the Week column.

So I'll select the column with Ctrl space bar and Alt E D to delete.

I'll then go back to the results sheet and go to the Monday calculation.

And I'll write a new formula, which will be "= count" and then an if statement inside that count function.

Writing countif in this way allows me to use functions such as Text in my criteria.

And so the logical test will be the Text function, which will accept my day column and transform it into the day of the week with four d's, will be equal to Monday.

And if this value is true, I'll return one, and if the value is false I'll return an empty string.

I'll then close the brackets for the two functions.

For each data entry, this formula will convert the date into the day of the week using the Text function and check if the value is equal to Monday.

If the value is equal to Monday, it will return one, and if it's not, it will return nothing.

The Count function will count the number of ones that we've collected and return that value in our cell.

So that this formula checks each value in the range of dates that we've selected, I'll need to use an array formula.

And to do this I'll simply press Ctrl Shift Enter.

And this calculates the correct answer of 48 signups on Mondays.

I'll then copy and paste for the remaining cells.

Needless to say, when starting out it's much easier to create a new column, perform the change in that new column, and then use a simple countif function. However, if you want to limit the number of new columns created on your data set, then the array formula is a nice solution. The array formula will work for most Excel functions.

But unfortunately not for Week Num, so we'll have to keep the Week Number column for these four answers.