Google Sheets Functions – QUERY

The QUERY function is in a category all on its own. It’s an extremely powerful function that will let you filter, sort, group, pivot, basically extract data from a table and present it in numerous ways. At first it can look daunting, with its own language and syntax, but once you dip your toe into the QUERY pool you’ll realise that things are not so complicated and that with just one function, you can extract and analyse your data with ease.

As always the best way to learn how to use it is through examples, and in this post we’re going to use two main sources of data, some questionnaire feedback, and some data from a HR department, building the complexity up step by step.


Analysing questionnaire feedback

Here we’ve used a Google Form to collect feedback on the teachers, the classrooms, and admin information at the end of every course in an academy. Each row is a student’s piece of feedback. They grade the various criteria from 1 to 5, 5 being ‘excellent’. Below is a snippet of that table of data.

functions20-1

In the next few examples, we’ll see how easy it is to analyse this data, each time with just one QUERY function.


Example 1a – Selecting the relevant data from the master data

The head of studies wants to look at the feedback for her teachers, and she doesn’t need to know the classroom feedback or the admin feedback. So, the info she needs is from column A to H, as shown below:

functions20-2

In cell A1 on a different page, I’ve written the following QUERY function:

functions20-3

There are 2 main parts to a QUERY function, 1) the data range, 2) the query

So, in the first part (in orange) we look at the page called “Questionnaire” and range A1 to column N (note this is an open-ended range as we will receive more entries in the future).

In the second part we tell the function what to select. So, in this example, we want columns A to H. We add the column letters followed by commas. The query part always needs to be within speech marks, so we put it before select and at the end before the bracket.

“select” is one of the keywords within the QUERY language which tells the function what to do. Here are some of the other ones, most of which we will see in the following examples.

functions20-33


Example 2a – Filter by a teacher’s name

Now the head has decided that he wants to look at the feedback of a particular teacher. She wants the following information:

functions20-4

Here’s the QUERY formula I entered in cell A2:

functions20-5

It’s the same as before except that at the end I’ve stated a condition:

functions20-37

This looks at column C and returns anything that matches “Fred”. I.e. it only returns the feedback for Fred.


Example 2b – Filter by a teacher’s name using a cell reference

The head has decided that she doesn’t want to have to change the teacher’s name within the formula every time she wants to look at the feedback of a different teacher, she wants to enter the teacher’s name in cell B1 and wants the formula to update accordingly.

That’s no problem, although the syntax looks a little ugly. Here’s the formula:

functions20-6

At the end, instead of the name “Fred” I’ve put the reference to the cell. In QUERY function you have to use this syntax: ‘”&B1&”‘ basically so it knows it’s a cell reference.

functions20-38

As you can see it produces the same information as before and now if the head wants to see another teacher’s feedback she only needs to change the name in cell B1.

functions20-4


Example 2c – Filter by a teacher’s name and sort the date in descending order

By default, the data is sorted from the oldest date to the most recent, but when there is a lot of data this means that to see the most recent and probably most relevant data, the person has to scroll down. We can remedy that easily by sorted the data by date in descending order, as we can see below:

functions20-8

To achieve this, I’ve used the same formula as before except at the end I’ve added an ORDER BY part:

functions20-9

functions20-39

This orders (or sorts) column A (the dates) and the ‘desc’ tells it to do it in descending order. If you want to tell it to do it in ascending order, write asc.

Carefully note the syntax, as one tiny error will stop this from working.

Also note that, the QUERY results aren’t formatted and the column widths aren’t automatically adjusted. This needs to be done manually either beforehand, or afterwards.


Example 3a – Filter the data between 2 dates

The head also wants to be able to filter the data for a particular period of time, e.g. september 16. As you can see below, the data has been filtered between 1/9/2016 and 30/9/2016.

functions20-31

Here’s the formula I’ve added in cell A2:

functions20-32

functions20-40

The new part is at the end. First, we tell it to look in column A for a date bigger than or equal to the date in inverted commas. Make sure you add the word date, to tell the function that you’re looking for a date and not some text.

functions20-41

Then in the next part, we add ‘and’ to tell it to look for 2 criteria. Then tell it to look for a date less than or equal to the date in inverted commas.

Note that with dates you need to write the date in the following format:

YYYY-MM-DD

Even if the date format in your sheet is different as it is in my one.

So to summarise, it gets columns A to H, and returns rows that meet the 2 criteria, i.e. 1/9/2016 to 30/9/2016.


Example 3b – Filter between 2 dates using cell references

As we saw earlier we can replace the actual dates in the formula with cell references.

functions20-10

The only thing is that to do this we need quite a complex looking formula. Here’s the formula I’ve added in cell A2:

functions20-11

Here’s the formula broken down into it’s component parts:

functions20-44

functions20-45

functions20-46

To replace the actual date we need to use the following formula after the word ‘date’:

functions20-42

This gets the date in cell D1, puts it into the correct format. The same goes for the second date:

functions20-43

If anyone reading this knows of simplier syntax to do the same thing, I’d love to hear it, as I couldn’t find on-line a better way to do this, but I’m open all ears!


Example 3c – Filter between 2 dates and by teacher

Finally, the head wants to filter the feedback between the two dates, by a specific teacher, and order the feedback by date in descending order, as you can see below:

functions20-12

As you can see the formula is getting pretty long, but you can also see that it’s made up of parts and you can extract what you want by adding extra parts.

functions20-13

Here’s the query part broken down:

functions20-44

It gets columns A to H.

functions20-45

Finds rows where the date is greater than or equal to the one in cell D1.

functions20-46

And that also is less than or equal to the one in cell F1.

functions20-14

AND where the name in column C is the same as the name in cell B1.

functions20-47

Then order the results by the dates in column A in descending order.


Example 4 – Filter against various criteria

Here the admin manager wants to use the questionnaire feedback to see how good the information is that is given to the students when they sign up and how well the service was in the office. He particularly wants to know if there was any low feedback in any of the areas under his control, so wants to know if the course info, payment info, or office service was rated less than 3 by anyone.

functions20-15

To create the table above I’ve added the following formula in cell A1:

functions20-16

Here I’ve selected 5 columns and notice that I’ve put column B at the end. This shows that when selecting the columns, you don’t have to have them in the same order as the original data. This is extremely useful at times.

In the second part, I set the criteria, i.e. he’s looking for values which are less than 3 in each of the columns, L, M, and N. To include 3 different criteria, I’ve used the ‘or’ keyword, so that it will return rows if any one of them have a value of less than 3 in it.

As we can see in the table above, it’s found 3 results where the course information was rated poorly, and we can see from the level that it was related to level B1, so clearly some work is need there. Plus, there is one incident where the service in the office was rated poorly, which may need investigating.


Example 5a – Returning the averages of data

The director of the school wants to know if there are any classrooms that are rated more poorly than others. He wants to see if the average rating is different for any of the classrooms. In the table below, we can see that clearly, there is a problem with class A1, as it is rated poorly and much lower than the others.

functions20-17

To do this, I’ve added the following formula in cell A1:

functions20-18

This time I’m interested in the classrooms in column I and the average of the scores given in column J. First, I select column I, then select the average of column J, then I group them by classroom, in other words by column I.

We can return the average, count the number of entries, return a maximum or minimum in that column, or sum up the entries, using the following syntax:

functions20-34

Quite often these work with the ‘group by’ keyword, to be able to return the results.

Note, there is an empty row in row 2, as the data range is looking below the original data and into empty rows, and it will return one. This can be eliminated by stating the exact range of your data as we will see next, but the downside is that if more data is added the range will have to be updated.


Example 5b – Returning the averages of data and ordering them

Following on from the example above, we can adjust our returned information by sorting the feedback by the lowest to the highest, i.e. in ascending order. Here we can see class A1 is the lowest rated.

functions20-19

Here’s the formula I wrote in cell A1:

functions20-20

The first part is as before, then it’s followed by:

functions20-48

This orders the results by the average of column J (in column B), in ascending order.


Example 6 – Pivot information using QUERY not pivot tables

To finish off this first part, let’s look how we can pivot the information to see the averages of 2 criteria for each teacher. We want to look at the “is clear” and “is organised” categories. If you’re familiar with pivot tables, this works in a similar way, but with the bonus of doing everything right within the QUERY function.

Here the data has extracted the information below. It looks like Fred’s class organisation may need improving a little.

functions20-21

To get this output, I’ve written the following formula in cell A1:

functions20-22

This selects the average of column D (“is clear”) and the average of column E (“is organised”) as the criteria, then pivots it by teacher (column C), so that we see an average of each criteria for each teacher.

We could look at every criteria per teacher, just by adding the average for each criteria column, e.g. avg(F), avg(G), etc.


Analysing a HR database

OK let’s look at a different set of data now. Here we have employee database with some information about them.

functions20-23


Example 7 – Returning average salaries per department

The HR director wants to know what the average salary is per department from the data above. Here’s the end result:

functions20-24

In cell A1, I’ve written the following formula:

functions20-52

This selects the departments (column B), and the average of the salaries (column C) grouped by department.


Example 8a – Listing salaries per employee in descending order

Here he wants to see the salaries per person in descending order, without any of the other information.

functions20-25

In cell A1, I’ve written the following formula:

functions20-53

This selects columns A and C, and sorts column C in descending order.


Example 8b – Limiting the number of results

The HR director actually only wants to see the 5 highest salaries. We can use the formula and add a limit to it, to show the following:

functions20-27

Here’s the formula:

functions20-54

In the last part I’ve added ‘limit 5’. This returns the first 5 rows.


Example 9 – Ordering by more than 1 criteria

Let’s now look at how we can order our results by 2 or more criteria. Here the HR guy wants to see the employee names, their departments and salaries. He wants the data organised by department then by salary, with the salaries going from highest to lowest.

functions20-29

To do this, I’ve written the following formula:

functions20-30

This selects columns A, B, and C (employee, department, and salary), orders first by department (B), then by salary (C). Note the syntax, after ‘order by’ you just add the first column letter, then the second one after a comma.


Example 10 – Relabelling column headers

Finally, let’s look at how we can rename the column headers to something different from the original data. This can be useful, if the original data is from a computer output and the column headers aren’t in everyday English, or you may simply want to change them.

Here I’ve changed the column “Employee” to “Name” using the QUERY function.

functions20-35

To do this, yes you’ve guessed it, I’ve added the following formula in cell A1:

functions20-36

The new part is at the end, (label A ‘Name’). This tells it to rename column A with the word ‘Name’. To add more labels, just add a comma and the column letter and new name.


A couple of final comments about QUERY. Be careful where you place your QUERY function, as you need to make sure that there is nothing in the cells particularly below it, as otherwise it’ll throw an error.

The syntax is very exact, so make sure you notice in the examples, how the punctuation is used.

Despite this being a long post, I’ve only scratched the surface as to what QUERY is capable of. To learn more go to Google’s page on the query language:

https://developers.google.com/chart/interactive/docs/querylanguage


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – INDEX and MATCH (VLOOKUP alternative)

In a previous post, we looked at how we can quickly look up tables for certain information, using the VLOOKUP function. This function is great but it does have some limitations. Firstly, when you look up information in the table you always have to look to the right for the matching information. Secondly, if you add a new column to the table, this messes up the references and it returns the wrong information.

This is where the functions INDEX and MATCH come in. With a combination of these two, you can look either left or right in a table, and it adapts to any added columns.


INDEX

First, let’s see how the INDEX function works. Here we have 2 columns of data (columns A and B), we can pick out a certain cell’s data by referring to its position in the table of data.

functions19-1

First we state the table range (A1:B3), then state the row number we want (3), then the column number (2). This returns the value in the cell (300).

functions19-2


INDEX and MATCH

Example 1 – Finding the classroom a specific teacher is in

Usually we don’t already know the exact row and column we want, and this is where MATCH comes in.

Here we have a table which continues a list of teachers and which classroom they are in. I want to be able to type in cell D1 and find the classroom the teacher is in, and return it in cell D2.

functions19-3

First we need to understand the following MATCH function.

functions19-16

This looks for the teacher’s name in D1 within the list of names (A2:A9). The 0 at the end looks for an exact match. This returns the number 3, as that is the number of places down the list in which Barney is. Now we have the row, we need to use that to find the classroom.

We do this by adding the INDEX function. This looks down the classroom list (range B2:B9), and picks the value at the place returned by the MATCH function. This is 3 as we saw above. This is classroom A3.

So, to reiterate that, the first range (B2:B9) is the column you want to return the value from. The second range (A2:A9) is the column you want to initially search in.

functions19-4

In this example, we could have used the following VLOOKUP function, which as you can see returns the same result in cell E2.

functions19-5


Example 2 – Finding who is in a particular classroom (looking up to the LEFT in a table)

In the above example, we looked up a teacher and then moved RIGHT, to look up the classroom. But what happens if we want to do the opposite and look up the classroom and move LEFT and find out which teacher is in that room?

functions19-6

In cell E2, we have the following VLOOKUP formula and as we can see above, it returns a N/A error. This is because VLOOKUP cannot look to the left, as the column number is always positive, i.e. moves to the right.

functions19-8

To be able to do it, in cell D2 we can use the following INDEX and MATCH formula:

functions19-7

Here all we do is swap the formula we saw earlier around. We return the teacher in range A2:A9 by matching the classroom in range B2:B9.


Example 3 – The effect of inserting a column in a table on an INDEX/MATCH and a VLOOKUP formula

Another problem with using the VLOOKUP formula is that if a column is added and deleted from the table being used, it returns the wrong result, as the column reference is then incorrect.

Below we have started with the same table and formulas that we had in example 1. Then I have added a new column with the class year in those classrooms. As we can see, the INDEX/MATCH formula in cell E2, correctly returns that the Bob is in classroom B2. The VLOOKUP formula in cell F2, returns the class year and not the classroom.

functions19-9

As we can see, the VLOOKUP formula, still refers to the second column even though the range now covers 3 columns and we in fact now want the third column.

functions19-11

Whereas, the INDEX/MATCH formula, adapts and looks up the classroom now in column C.

functions19-10


Example 4 – Returning more than one column of information

We’re not limited to just returning a cell in one specific column, we can return multiple columns or an entire row if we want. Here I want to return both the class year (in E2) and classroom (in F2), when the teacher is entered in cell D2.

functions19-12

We use the following formula:

functions19-13

Here the output range is B2:C9, which includes both the year and classroom. Note, that this returns the information, in separate columns to the right of the cell with the formula in it, i.e. cells E2 and F2.


Example 5 – Matching a range and not an exact figure

So far, we’ve been looking for exact matches, but like VLOOKUP we can also match within ranges.

Here we have a level test where the score corresponds with the student’s level. If they get 5 or less they are a beginner, if they get between 12 and 15 they are intermediate, and so on. In cell D1, we’ll add the score, then in D2 we’ll see the level.

functions19-14

We use the following formula:

functions19-15

This is the same as we’ve seen before, except that there is a “1” at the end this time. This time it’s looking for 14 in column B and looks for the largest value that is the same or less than it. As 14 doesn’t appear, it finds that 12 is the largest value. Then it returns the corresponding level in column A. So, basically, you can imagine a range from 12-14 for Intermediate, and that our value falls within that range.

There are 3 number options at the end of the formula:

1: causes MATCH to assume that the range is sorted in ascending order and return the largest value less than or equal to search_key.

0: indicates exact match, and is required in situations where range is not sorted.

-1: causes MATCH to assume that the range is sorted in descending order and return the smallest value greater than or equal to search_key.

So, if the list was sorted in  descending order we would use a “-1” instead. By default, “1” is used, so it is possible to leave the number out if you want to search a list in ascending order, like we did in this example.

In many cases VLOOKUP will do the job, but as you can see above there are times when INDEX and MATCH is better. It’s less used partly because it looks more complicated but as we’ve seen above it’s easy to set up.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – HYPERLINK

Here let’s take a quick look at the HYPERLINK function, which allows you to add hyperlinks with ease and to also rename them.

Here I’ve just copied and pasted my blog address into the cell.

functions18-1

This is fine, but sometimes we don’t want an ugly URL in our sheet. For example, I can change what shows by using the HYPERLINK function.

functions18-3

Here I add the URL between speech marks, then after the comma add the text I want visible on the sheet, again in speech marks.

Now in cell A3, I have the text I want.

functions18-2

When you hover over the text, you can see that it’s linked to a URL and clicking on the blue link, will take you to the webpage.

functions18-4

Extra tip: Links to documents on your Drive could also be done in this way. Just make sure the person using your sheet, has access to the document.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – ROUND, ROUNDUP, ROUNDDOWN

In this post we’re going to have a quick look at rounding numbers up and down, by using the ROUND, ROUNDUP, and ROUNDDOWN functions.

The syntax is very easy, we tell the function the number we want to round and then to how many decimal places.

In cell A1 we have a number and in B2 we want to reduce it to just 2 decimal places. In cell B2 we write =ROUND(A1,2), this gets the number in cell A1 and then converts it to 2 decimal places.

functions16-1a

In the examples below we can see how the same formula treats numbers that are 0.5 or higher or less than 0.5. If the number is half or more it will round the number up, so 3.56 becomes 3.6, if we’re working to 1 decimal place. If the number is less than half, it will round it down, so 3.14 becomes 3.1.

functions16-1b

We can control whether we want it to round up or down. To always round it up, we use the ROUNDUP function in the same way as before. As you can see, it’s rounded both figures up this time.

functions16-2

To always round down, we use the ROUNDDOWN function in the same way:

functions16-3

If you want to round whole numbers up, for example, to the nearest thousand, use a negative number. Here I’m rounding to the nearest thousand, by adding a -3.

functions16-4


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – IMAGE

In this post let’s look how we can insert images into our sheets. There are two main ways, either inserting the image via the Insert menu or by using the IMAGE function.


Example 1 – Inserting an image from Drive

Here let’s add an image from my Drive. Open the “Insert” menu then click “Image”.

functions17-10

Choose where your image is, in this case, let’s choose “Google Drive”.

functions17-13

Search for your image, and click on the one you want, then click Select.

functions17-12

This will place the image on top of your sheet and won’t affect the cells in any way. You can change the size of it by moving the blue squares on the border of your image.

functions17-11


Example 2 – Inserting an image within a cell using the IMAGE function

An alternative way to inserting images, is to use the IMAGE function, which will insert the image within the cell where that function is. To do this we need the URL of the image.

In the cell I type =IMAGE() then in between the brackets I add speech marks and the URL inside them, i.e. =IMAGE(“www.google.com”)

Here I’ll add an image from my blog:

functions-17-1

This adds the image within the cell. A bit small isn’t it?

functions-17-2

This is because it has adjusted the size of the image to the size of cell. To make it bigger we just make the row and/or column sizes bigger.

functions-17-3

By default, the image is inserted in “sizing mode 1”. So, what does that mean? Well, there are 4 modes and they treat the images in different ways.

Mode 1 – Resizes the image to fit inside the cell, maintaining aspect ratio.

Mode 2 – Stretches or compresses the image to fit inside the cell, ignoring aspect ratio.

Mode 3 – Leaves the image at original size, which may cause cropping.

Mode 4 – Allows the specification of a custom size.

So, let’s look at the modes 2, 3, and 4 in turn.

Mode 2

Taking the same image and formula, but this time just adding a comma and 2 at the end, will squash the image into the cell, and ignore the original aspect ratio, so it now looks too wide.

functions-17-4

functions-17-5

Mode 3

This time replacing the 2 with a 3, will insert the image as its original size, but if it is bigger than the cell, it will be cropped, as we can see below.

functions-17-6

functions-17-7

Mode 4

Finally, we can control the height and width we want, but to do this we must use mode 4.

As you can see in the formula, after the 4, we add the height (150) and then the width (120).

functions-17-8

functions-17-9

Which mode you use is of course entirely dependent on what you want to achieve.

It’s important to note that as these images are within the cells, they are affected by any cell changes, rows or columns added, etc.


 eBooks available on Drive, Forms, Sheets, Docs, Slides, and Google Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – OFFSET

Sometimes we spend time setting up beautiful spreadsheets only for us to have to add rows or columns afterwards, which then messes up our formulas and we have to change them. In this post, we’re going to look at a couple of examples of the OFFSET function, which will help us create more dynamic formulas. What we mean by this is that the formula will adapt to changes made to the spreadsheets, quite often where rows and columns have been added.


Example 1 – Creating dynamic ranges to maintain an average formula

Here we have some marks for some students. (To those who have been following my posts, honestly, I’m not obsessed with exam marks, they just make good examples!)

In cell B6 I’ve added an AVERAGE function to work out the average of the marks.

functions15-1

functions15-2

But I now have another student to add who’s done the test. I add a row and insert the student’s details, but as you can see this hasn’t changed the average figure.

functions15-3

If we look at the formula, it hasn’t changed despite there being an extra row.

functions15-2

We can solve this by using the OFFSET function in the AVERAGE one.

functions15-4

In cell E6, I’ve added the following formula:

functions15-7-1

OK, so what’s happening? Well let’s look at the syntax of the function to understand it better.

functions15-12

The OFFSET function has 3 main parts:

cell reference: this is the cell you start from

offset rows: this is the number of rows you move to; positive numbers move down and negative numbers move up. In other words, a positive number increases the row number and a negative one decreases it.

offset columns: this is the number of columns you move to

There are 2 other optional parts, height and width, but here we’re not going to use them.

So going back to our formula:

functions15-7-1

The OFFSET function starts at E6 which is where our total is.

Then moves up one row to E5 as there’s a -1 in the second part.

It doesn’t move from the column, as there’s a 0 in the third part.

So, this returns the cell E5.

Now we just add the AVERAGE part. It takes the range from E2 to the result of the OFFSET function, which is E5 (E2:E5).

As we can see it returns the correct average like we saw earlier.

functions15-4

Now let’s add the extra student and see what happens. Ah-ha, the average has changed from 8.5 to 8.8, which is what we want.

functions15-6

Looking at the formula, we can see that it has changed subtly, the start cell reference is now E7 (the total) and it’s still moving one cell up, so returns the cell E6. This means the range is now E2:E6, which is what we want.

functions15-5-1

We can add or delete rows and the average will always be correct, without having to manually change it.


Example 2 – Dynamically calculating the sales for the last X months

Here we have a company’s sales from January to June. The sales manager wants to be able to find out the sales for the last X months. Here he adds the number of months he wants to look back from the last month, e.g. in cell D1 he writes 3. Then in cell D2 it tells him the total number of sales in that period, which in this case is 2,100 (700+600+800).

functions15-9

So, how did we do that? In cell D2 is the following formula:

functions15-8-1

Let’s break it down and start with the OFFSET function.

B2: This starts from cell B2 (the first month’s sales).

COUNT(B2:B)-D1: Then it counts how many rows (months) there are from B2 to the end of column B. Then it takes away the number of months we want to report back, in this case 3. So it offsets by 3 rows (6-3), so starts from cell B5.

0: It doesn’t move columns.

D1: The height is the figure in D1, i.e. 3 rows. So it takes figures that are from B5 to B8 (i.e. 3 rows).

1: It returns just that one column.

So, when we change the number of months in cell D1, it returns the new number of sales. In this case, the last 2 months total 1,400.

functions15-10

This has the added benefit, like we saw in example 1, that when more rows are added it still works. Now, the sales manager has added the sales for the month of July. As we can see, the last 2 months now add up to 1,800.

functions15-11


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – WEEKDAY, WORKDAY, NETWORKDAYS, EDATE, EOMONTH

Following on from my post on the basic date functions, let’s look at some really useful functions that work with dates, namely: WEEKDAY, WORKDAY, NETWORKDAYS, EDATE and EOMONTH, plus we’ll see an example with the CHOOSE function. With these we’ll:

  • Find out the day of the week of a particular date
  • Work out a deadline date
  • Work out how many working days there are between two dates
  • Easily set up start and end of the month dates
  • Work out how many days there are in a month
  • Work out how many working days there are in a month

Example 1 – What day of the week was a particular date?

What day of the week was 1st January 2000 on? No, I couldn’t remember either. Let’s use the WEEKDAY function to quickly find out.

In cell A1 I’ve written the date, then in cell B1 I write the following:

functions13-1

This returns the day number for that date, where Sunday=1 and Saturday=7. So, we can see that 1st January 2000 was in fact a Saturday.

functions13-2

Personally, I find using Sunday as the first day of the week a bit confusing, but you can change which day is the first one by adapting the formula. Let’s make Monday the first day of the week. This time, after the date, add a comma then “2”. This makes Monday the first day.

functions13-3

As we can see in cell B2, it now returns “6”, as Saturday is now the sixth day of the week.

functions13-4

You can make any day the first day, just by changing the number in the formula.


Example 2 – Returning the day of the week as text not as a number

The above example’s great, but it requires you to think of what the number represents. Wouldn’t it be better to return the actual name of the day? Well, that’s easily done by adding the CHOOSE function to our WEEKDAY one.

I write the following formula:

functions13-5

This carries out the WEEKDAY function, finds the day number, then looks down the list of days. So, for the 1st Jan 2000, it will move along 6 spaces down the list and then choose the entry there, which of course is Saturday. Note, each entry needs to be in quotation marks.

functions13-6

The entries can be anything you want. For example, the other day I used this to return the days in Spanish despite using a sheet that was English-based, as we have both English and Spanish speakers using it.

A fun one to do in class with kids, is to find out what day of the week they were born.


Example 3 – Find out the date a number of days from a given date

In this example, a team have 90 working days to finish the project. I want to find out want date that is. To do so, I use the WORKDAY function:

functions13-7

This takes the start date in cell B1 and then adds 90 working days, and returns the end date. So, I can quickly see that they need to finish by 22nd March.

functions13-8

What about the Christmas holidays I hear you cry?! Well, WORKDAY can exclude a list of dates, such as holidays. In range D2 to D4, I’ve listed the Christmas and New Year holidays.

functions13-9

Back in our formula, I need to state where those holidays are, so I just add them after the “90”.

functions13-21

This time I see that the deadline’s moved out a little. Note, it’s only moved by one day, as out of 3 holidays, only one falls on a work day. In the UK, the weekend ones would in fact move to the Monday, but I just wanted to keep the example simple, and in other countries weekend public holidays don’t always move.

functions13-10


Example 4 – How many working days are there between two dates?

I’m looking forward to my Christmas break already and I want to know how more working days there are until I finish for Christmas. In cell B1 I put today’s date, in cell B2 the end of term date.

functions13-22

In cell B3, I write the following:

functions13-25

This takes the two dates and works out how many working days (Mon to Fri) there are. As we can see there are 26 days.

Ah, but in Spain we also have two public holidays on 6th and 8th December, I’m not working then.

functions13-23

We can exclude those from the total by modifying the formula:

functions13-26

This now works out the number of days and subtracts the number of days in range D2:D4. So, it turns out there are only 24 working days. Excellent!

functions13-24


Example 5 – Easily adding start of the month and end of the month dates

In the table below, I want to record how many students have had classes in each month. I need to include the start of the month and end of the month dates as I’m going to use them to filter a master list. Now, I could type in the dates, but with a long list this would be laborious. Instead I can use the EDATE and EOMONTH functions to do it for me.

functions13-14

In cell A2 I write the first date I want, in this case 1/10/2016. then in cell B2, I want to add the end of that month. I do this by writing the following:

functions13-15

This takes the date in cell A2 and then gets the date of the end of the month and as it’s the same month, I write a “0”. If I wanted the next month (i.e. 30/11), I would write “1” and so on.

On the next line I want to add the start of the next month (1/11). So, I write the following:

functions13-16

This takes the date in cell A2 and adds a month to it, keeping the same day of the month, i.e. 1st. In cell B2, I copy the same EOMONTH function as before, i.e. from cell B2.

Now for all future rows I can just copy this row and paste it below. So, for example, cell A6 is =EDATE(A5,1) and cell B6 is =EOMONTH(B5,0).

You can use EOMONTH to return the end of the month of future months, just by changing the 0 to a higher number. For example, in cell B2 =EOMONTH(A2, 3) would return 31/1/2017.


Example 6 – Working out the number of days in a month

In a salary sheet I need to know how many days there were in the month. Every month I type in the month in cell B1 and in cell B2 it tells me how many days are in that month, which I can then use in other formulas to calculate my teachers’ salaries.

functions13-17

In cell B2, I write the following:

functions13-18

This gets the date in cell B1 and gets the end of month date, then subtracts the start of the month (B1) then adds one so it starts with one and not zero.

As you can see it rightly, worked out that in 2016 February had 29 days.


Example 7 – Working out the number of working days in a month

In the same salary sheet, I also need to know how many working days there were in that month. This time I need to combine the NETWORKDAYS function with the EOMONTH function. I write the following in cell B3:

functions13-19

This gets the start date from cell B1, gets the end of the month date from the EOMONTH function and works out the number of working days in between.

So, it correctly worked out that there were 21 working days in February 2016.

functions13-20


There are two additional functions similar to WORKDAY and NETWORKDAYS, these are WORKDAY.INTL and NETWORKDAYS.INTL. These add the extra option of stating how many days of the week are non-working.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


 

Google Sheets Functions – GOOGLETRANSLATE, DETECTLANGUAGE

Lots of people know about and have used Google Translate either on their phones or on the Google website but what they often don’t know is that there is a built-in function in Google Sheets, which will allow you to translate from one language to another, and even automatically recognise the language and translate it. So, in this post we’re going to look at the functions GOOGLETRANSLATE and DETECTLANGUAGE and you’ll see how easy these are to use.


Example 1 – Translating from one language to another

I created a system where I work, where teachers can report problems in their classrooms via Google Forms on their mobiles. We’re in Seville, Spain, and some teachers can’t speak Spanish very well, so I needed a system that would allow them to report the problems in English and then it would translate it into Spanish, so that the maintenance and IT guys, who don’t speak English, can understand the problems and act on them.

To do this, I used the GOOGLETRANSLATE function. To show this, let’s look at a simple example below. In cell A2 and I have the problem reported by the teacher in English, then the translation will appear in cell B2. In cell C2 I’ve written the formula that has been used.

functions14-1

The GOOGLETRANSLATE function has 3 parts. First, the source text (here in cell A2), second, the source language (here English), and thirdly, the target language (here Spanish). Note, you have to use the codes “en” for English, “es” for Spanish (español). As you can see, it translated the original sentence fine.


Example 2 – Detecting a language

The above example works fine for our English teachers, but in our department we also have French, German, Portuguese, and Chinese teachers and not all of them speak good Spanish. So, how do allow them to use this in their own languages? This is where DETECTLANGUAGE comes in. This will look at the text and determine what language it is, then return the code for that language.

So, in cell A2 we have some text in English. in cell B2, we have the DETECTLANGUAGE function I’ve written in cell C2. As you can see, it’s correctly returned that the text is in English.

functions14-2


Example 3 – Detecting a language and translating it

Having the DETECTLANGUAGE function return the language is useful but really we then need it to combine the GOOGLETRANSLATE one to automatically change from the various languages that the problems could be written in, to Spanish.

So, in cell B2, we add the formula you can see in cell C2. Basically, we’ve replaced the source language part with the DETECTLANGUAGE function. So, it looks at the text in cell A2, then uses DETECTLANGUAGE to determine which language it is, then translates it to Spanish.

functions14-3

Here’s the same but with the problem reported in French. Notice that the translation is slightly different, as the “El” is missing this time. As we’ll see in the next example, Google Translate is good but at the moment it is still a long way off being perfect, as translating language is extremely complicated.

functions14-4


Example 4 – Automatically translating board vocabulary

Another use of this is in the classroom, where in foreign language classes it’s usual to record unknown vocabulary on the board during the lesson. I sometimes use a Google Doc and share it with the students, but here we can use a Google Sheet and it can translate the words automatically as I add them.

To do this, I have a Google Sheet with two columns. In column A I type the English words  and phrases that come up. In column B, I have a GOOGLETRANSLATE formula copied down lots of rows, so that as I type in a word, the translation appears in the cell next to it.

functions14-5

I use the same formula as in example 1, except that I’m going from English to Spanish, but also I wrap it up in an IFERROR function, just so that empty cells don’t produce an error message, instead they just leave the cells in B blank.

functions14-6

If you know some Spanish, you will notice that in fact some of the translations aren’t that good, or at least will depend on the situation. The individual words are ok, but the last two phrases aren’t what I wanted. For example, “to have a coffee” is “tomar un café” but it could be with the word “para”, if I say, “I went to the bar to have a coffee”. The last one, should translate as “hacer turismo”, as it’s come up with a translation that says “to go and see landscapes”. So, as you can see it needs to be used with care, but this can also be used as a discussion point with your students.


Example 5 – Having a conversation where neither person speaks the others language

This is a nice example where two people can have a conversation, despite not knowing each other’s language. In this case, it was two children in different countries, using a Google Sheet to communicate with each other.

In the white cells they type their conversations, one line at a time going down the page. English on the left and the other in Spanish on the right. In the yellow part are the translations. The translations are good enough for them to communicate. They could of course use this to help them learn each other’s language.

functions14-7

In column B, I’ve written this formula down the rows:

functions14-8

And in column C, I’ve written this one:

functions14-9

Again I’ve wrapped them up in the IFERROR function, so that it removes error messages.


In the GOOGLETRANSLATE we can also omit the source language and target language and let Sheets do it automatically. For example, in example 1 this function will look at cell A2, it will detect the language and then translate it to the language your Google account is in. This can be fine in many cases, but I prefer to be more prescriptive, just to avoid it coming up with strange results.

=GOOGLETRANSLATE(A2)

Also, we’ve only looked at single words and short sentences, but this works with long texts too.

Google Translate doesn’t translate all of the 6,000+ languages in the world, but it does cover the most common ones. Here’s a list of the language codes. This is continually being updated so new ones may not be on here.

Language Code Language Code
Afrikaans af Irish ga
Albanian sq Italian it
Arabic ar Japanese ja
Azerbaijani az Kannada kn
Basque eu Korean ko
Bengali bn Latin la
Belarusian be Latvian lv
Bulgarian bg Lithuanian lt
Catalan ca Macedonian mk
Chinese Simplified zh-CN Malay ms
Chinese Traditional zh-TW Maltese mt
Croatian hr Norwegian no
Czech cs Persian fa
Danish da Polish pl
Dutch nl Portuguese pt
English en Romanian ro
Esperanto eo Russian ru
Estonian et Serbian sr
Filipino tl Slovak sk
Finnish fi Slovenian sl
French fr Spanish es
Galician gl Swahili sw
Georgian ka Swedish sv
German de Tamil ta
Greek el Telugu te
Gujarati gu Thai th
Haitian Creole ht Turkish tr
Hebrew iw Ukrainian uk
Hindi hi Urdu ur
Hungarian hu Vietnamese vi
Icelandic is Welsh cy
Indonesian id Yiddish yi

In summary, it’s a great way to automatically translate within a document.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – NOW, TODAY, DAY, MONTH, YEAR

In this post we’re going to look at some of the basic date functions and in particular, how we can extract parts of a date or a time.  We’ll cover: NOW, TODAY, DAY, MONTH, YEAR, HOUR, MINUTE, and SECOND.


Example 1 – Getting the current date and time

We can add the current date and time to our sheet with the very simple function, NOW.

Type the following:

functions12-1

This will add the current date and time in the cell.

functions12-5

This doesn’t update every second, but by default will update every time there’s a change made on the sheet. We can alter this by changing the recalculation settings. Go to “File” then “Spreadsheet settings”.

functions12-2

Under “Recalculation”, click the drop-down menu that says “On change”.

functions12-4

Here you will have three options. Choose the one you want, then click “Save settings”.

functions12-3


Example 2 – Getting today’s date and using it in calculations

Similar to above, we can get today’s date by using the TODAY function. Type the following:

functions12-6

This adds today’s date in the cell.

functions12-7

We often use this function in calculations. For example, let’s find out how many days are left until Christmas day.

In cell B1 I have Christmas day and in cell C1 I type the following:

functions12-8

This just subtracts Christmas day from today’s date and returns the number of days, which at the time of writing, there are 41.

functions12-9

We can improve the returned result by adding some text to show what it is.

We start with the same formula as before, then add an ampersand and within inverted commas, we add the text we want.

functions12-10

As we can see, this adds the number of days to the text. This will count down every day.

functions12-11


Example 3 – Extracting the day from a date

Sometimes we want to extract a particular part of the date, to find out some piece of information. As an example, here we have the number of students that signed up for courses. The courses start either on 1st of the month, or on 15th. The Marketing department want to know which is more popular, the 1st or the 15th. From the data, it’s difficult to see.

functions12-12

So, first we need to highlight which days are the 1st and which are the 15th. We can do this, by adding the following DAY function to column C. In cell C2 type the following, then copy it down the rows.

functions12-13

This has extracted just the days from the dates in column A.

functions12-14

Now we can add up those which are the 1st and those which are on the 15th.

functions12-15

To do this we use a SUMIF function (see my post on this). Write the following in cell F1:

functions12-16

This looks in range C2:C13 and checks to see if there are any “1s”, if there are it adds up the number of students in column B, that correspond. Similarly, we do the same for “15ths”.

functions12-17

We can see above that the 15th has more students.


Example 4 – Extracting the month or year / Find out how old someone is

Similar to the DAY function, we can also use the MONTH and YEAR functions to extract the month and year from a date.

In cell A1, I have a birthday. In cell B1, I’ve added the MONTH function you can see in cell C1. This returns the month as a number from 1 to 12.

functions12-18

We can do the same with the year. In cell B2, I’ve added the YEAR function you can see in cell C2, and this returns the year.

functions12-19

Let’s find out how old this person is. In cell B3, I’ve written the formula you can see in cell C3. This gets the year from today’s date and then subtracts the year in cell A1, i.e. 2016-1973. It then returns the number of years.

functions12-20

Similar to example 2, we can add some text to the number to make it more meaningful, by using the ampersand.

functions12-21

The same applies for extracting times from a date and time. We have the HOUR, MINUTE and SECOND function which will extract the different parts of a time.

functions12-22

As you can see, by themselves these functions are limited but when combined with other functions, they allow you to work with dates and calculate what you want.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


Google Forms – Quizzes

In this post we’re going to look at the quiz option in Forms. Since it’s release earlier this year, I’ve been using it with various classes mainly to check their progress, and it really is a simple but highly effective tool, which I recommend everyone to use.


To start let’s just look at the 4 main areas you will need to use in order to set up your quizzes.

forms8-33 forms8-36

Settings>Quizzes – This is where you tell Forms that your form is a quiz and this then opens the quiz options. You can determine when the respondent sees their mark and whether they can see their answers corrected, the correct answers and the points allocated per question.

forms8-37

Settings>General – Here you can decide if the respondent will automatically receive a copy of what they’ve filled out or whether you give the option to do so in the form.

forms8-34

Questions – The bulk of the work is here, where you tell Forms which answers are correct and set the points per question. You can also set up automatic feedback for correct &/or incorrect answers, including links to websites or documents.

forms8-35

Responses – This is where the analysis happens after your respondents have filled out the quiz. You can see summary information for all the responses, including averages, range, problematic questions, and a summary per question so you can see which areas are causing the most problems. It also allows you to look at individual responses. Here a little summary of the main areas:

forms8-32


Setting up a quiz

Click on the Settings cog at the top of the screen.

forms8-1

There are 3 areas, General, Presentation, Quizzes. Click on “Quizzes”.

forms8-2

Then click the toggle switch “Make this a quiz”.

forms8-3

This will then open the options below. Under “Release mark” you have the choice of either allowing the respondent to see their mark as soon as they submit the form or you may decide to send it to them at a later date.

The latter is useful if you have questions which require your review first, for example, they are written answers, or you may decide to let everyone know their scores at the same time once everyone has filled in the form. To do this, the email collection is turned on, so that the score can be emailed to them.

The second part is controlling what the respondent can see when they see their marks. You can show them the answers they got wrong (“missed questions”), the answer key (“Correct answers”), and how many have been awarded per question.

forms8-4

Whilst in Settings, click on the “General” option. If you chose to release the mark later in the Quizzes menu, “Collect email address” will automatically be selected. However, even if you choose to release the mark immediately, you can manually select this open.

forms8-7

Underneath you have the “Response receipts” option. This allows the respondent to receive a copy of the form filled out with their answers in their email. Clicking in this opens 2 further options, the first “If respondent requests it” adds a question at the end of the form, to give the respondent the option of receiving the filled out form, and the second does it automatically, so doesn’t add a question.

forms8-8

Click “Save” once done.

If the “If respondent requests it” option is selected, the respondent will see this option at the bottom of the form:

forms8-31

They just click on the toggle switch if they want a copy.


Setting up the questions in the quiz

Now it’s time to tell Forms which questions are correct, etc. Click on the “Questions” tab. Here we can see that the email address collection has been added to the top of the form. You can’t move this.

forms8-9

I add a question as normal (see my post on adding questions). As you can see, at the bottom of the question, you have “Answer key”. Click on that to enter edit the answer key.

forms8-10

It’ll prompt you to choose a correct answer or answers. Just click on the answer that is correct.

forms8-11

You can also set the points value for the question. Annoyingly, this is always defaulted at 0. (Personally, most of the quizzes I’ve ever made are 1 point questions, so I think it would be better if the default was 1 point, or in the settings a global default setting could be set.)

forms8-12

You’ll then have your correct answer highlighted and the points value. If you select the wrong answer, click it again to remove it.

forms8-13


Adding feedback to your answers

You have the option to add automatic feedback to your questions. Click on “Add Answer Feedback”. You have 2 options, you can leave feedback for incorrect answers and/or correct answers.

forms8-14

To add feedback just type in the box, where it says “Enter feedback”.

forms8-15

You do the same for the correct answers, just by clicking on the “Correct answers” tab.

forms8-16

Not only can you leave text feedback but you can also add links. This is particularly nice, if you want to direct the student to some further reading related to the question or to some extra help.

Click on the link icon. There are two parts: “Link to” is where you paste in your link, and “Text to display” is where you add the name of the link, i.e. what the respondent sees to click on.

forms8-17

Here I’ve pasted in a link to a Google Doc with an explanation of this particular grammar point. The link can be to anything, YouTube video, a website, images, etc. Click “Add” then “Save”.

forms8-18

It shows you that this question has a link added to it.

forms8-19


Reviewing the summary of responses

Once you receive some responses, you will of course want to review and analyse them. Forms provides two main ways, either looking at summary of all the responses, or looking at the individual responses. Let’s look at the “summary” first of all.

Click on the “Responses” tab. Then if not already selected, click on “Summary”.

forms8-20

Under Insights, you’ll see the average (mean), median and range of the responses. Then underneath, a graph showing you the spread of the results. So, very quickly I can see that the majority of those who did my test, didn’t do particularly well, as I was hoping that most would get 4 or 5.

forms8-21

Under that Forms automatically highlights the most problematic questions, i.e. which ones respondents got wrong the most. Here I can see the last two questions caused the most problems, so immediately I can see where I need to focus my attention on in a future class.

forms8-22

Then you have the list of respondents, their total scores and when their scores were released. This is ordered by the release date, which I don’t find that useful, I would prefer it to sort by score. However, this is a nice, quick summary which will help you identify who needs help.

From this table, you can click on a line to go directly to that individual’s responses, again to see where in particular they need help.

Sending emails to respondents with their responses and the answer key

If the email setting was selected earlier, you can also send the students or particular students, a copy of their responses along with the correct answers and feedback, by clicking on “Release Scores”.

forms8-23

This opens up the Release Scores dialogue box. In here you can add a message to the respondents and then click on who you want to send the emails to. By default, all of the respondents are selected. Then click “Send Emails and Release”.

forms8-42

You can do this whether you selected early that they can see their result immediately or later.

Finally, you can also look at the summary of each question, to see not only which ones are causing problems but also what alternative answers the respondents are choosing. For example, in question 4, a lot of my students have chosen “a lot” instead of “a lot of”, so I can see that I need to remind of when we use one and not the other.

forms8-24

forms8-25


Reviewing individual responses

To see each individual’s response, click on “individual” near the top. It shows you each response in chronological order. It shows their email address, the response number, gives you the possibility of printing it, and the possibility of deleting it. To navigate through them, either click on the arrows or double-click on the number in the box and type in the response you want.

Below that it shows you the score for that respondent, if the score has already been released to them or not, and the option to release the score (similar to above).

forms8-26

Underneath it shows the questions, with whether they got them right or wrong, plus it shows the correct answers, any feedback that you set up, and any links that you added earlier. This is exactly how the respondents see it, when the score is released.

forms8-27

forms8-28

forms8-29

You also have the option of adding individual feedback, by clicking on “Add individual feedback” under any of the questions. This can be useful if you haven’t set up any automatic feedback or if you have questions that can’t be automatically corrected, for example, a piece of written text. Type in your feedback and if you want, you can add a link like we saw above. Then press “Save”.

forms8-43


How can respondents see their score?

If the “release mark immediately” option was selected earlier, when the respondent submits the forms, on the confirmation page, they will have the option “View your score”.

forms8-30

Clicking on that will take them to the form filled in with their answers, and depending on the options you chose, will have the answer key and feedback.

The other way I mentioned above is that they receive an email when the scores are released. This is the email they receive:

forms8-44-1

It contains their total and then when they click on “View”, it takes them to the form filled out with their answers and depending on the options you chose when setting up the quiz, the answer key, feedback and any links you added. this means that they have a permanent copy to review, which is better than only seeing it when they submit the form.

forms8-45-1


I’ve been using these quizzes since they were introduced a few months ago, and once set up they are extremely useful in quickly identifying where my students need extra help. In most cases I’ve found that as all the analysis is done within Forms I don’t need to set up a Sheet to analyse the data, which saves me a lot of work. However, with longer quizzes the initial set up can take a bit of time, especially if you’re adding feedback and links, but if you reuse this quizzes or share them with colleagues then they are definitely worthwhile.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)