Google Sheets Functions – TRANSPOSE

Here we’re going to look at how we can use the TRANSPOSE function to change our data from being vertical to being horizontal, or vice versa.


Changing a single column or row of data

Here we have the days of the week in a column and we want to put them horizontally, so that each day is in a different column across the page.

functions9-1

In cell C1, I write the following function:

functions9-2

As you can see, it changes the information from being vertically stacked to being horizontally laid out.

functions9-3

This works both ways. If the original data was horizontally laid out, then in cell A1, we could write this function:

functions9-11

This would display the days vertically.


Converting 2 vertical columns to 2 horizontal ones

Here we have the days of the week and some teachers. I want to lay both sets of information horizontally.

functions9-4

This is easy, just include both columns in the range in the TRANSPOSE function.

functions9-5

This will then take column A and put it in row 1, then take column B and put it in row 2.

functions9-6


Converting multiple horizontal rows into vertical columns

Similar to the previous example, we can do the same for more than 2 sets of data.

functions9-7

Include the complete range in the TRANSPOSE function.

functions9-8

And sure enough, it transposes the information from rows to columns.

functions9-9


The above is using the TRANSPOSE function, to do it automatically for you, but remember you can also copy your data and using paste transpose, you can transpose it manually. Just right click, select Paste special, then Paste transpose. See my post on moving and pasting data for more examples.

functions9-10


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – PROPER, UPPER, LOWER, TRIM

In this post we’re going to look at how we change the format of text to suit our needs, using the functions PROPER, UPPER, LOWER, and TRIM. It’s particularly useful when working with text that has come from, for example, a form, a different computer system, or indeed when someone has typed in on your Sheet. This is because the capitalisation isn’t the way we want it and the text may contain unwanted spaces, which can cause problems on your Sheet. We’ll cover the following areas:

  • Using the PROPER function to capitalize each word
  • Using the UPPER function to capitalize all letters
  • Using the LOWER function to put words into lowercase
  • Using the PROPER and TRIM functions to clean up text
  • Using ARRAYFORMULA to copy PROPER function to all rows
  • Capitalizing only the first letter of a sentence and putting the rest in lowercase
  • Changing a name to initials

Example 1 – Using the PROPER function to capitalize each word

The PROPER function capitalizes every word in the text, which is useful for correcting the format of names. Here we’ve got my name in various formats.

functions8-1

As we can see the syntax of the PROPER function and indeed the UPPER, LOWER and TRIM functions is very simple, just add the text or cell reference in brackets.

functions8-2

So I write the following in cell A2.

functions8-3

Then I copy that down in cells B2 and B3, and as we can see it’s corrected the format of the name.

functions8-4


Example 2 – Using the UPPER function to capitalize all letters

Here we have a part number which should be in UPPERCASE.

functions8-5

Similar to PROPER you just put the text or cell reference in brackets.

functions8-6

And it changes all the letters to uppercase.

functions8-7


Example 3 – Using the LOWER function to put words into lowercase

Here I want to change my messy name to lowercase.

functions8-8

I just write the following function:

functions8-9

functions8-10


Example 4 – Using the PROPER and TRIM functions to clean up text

One problem with receiving text input from forms, computer systems, etc is that text input can be in different formats and also can have unwanted spaces, which can mess up your formulas and how you use the data.

Here is an example where my name has been filled out in a form, but the users have entered it in different ways.

functions8-11

We can use the PROPER function to tidy up the format of the text, as we saw above, and we can use the TRIM function to get rid of those unwanted spaces, whether they are before the text, in the middle of the text, or afterwards.

So, we wrap the TRIM function up in the PROPER one:

functions8-12

As you can see, it tidies it up well, making it easy to use the data afterwards, or even just to make it look better.

functions8-13


Example 5 – Using ARRAYFORMULA to copy PROPER function to all rows

In the example 1, I copied the PROPER function down into each row. There’s a quicker and better way. Here I have some students’ names and I want to tidy up the format.

functions8-14

In cell B1, I write the following formula:

functions8-15

This looks at everything in column A and places the corrected format in column B, without having to copy down the formula into rows 2 and below.

functions8-16

Note, that when using the PROPER function, names which contain a capital letter in the middle of the word, such as, McCarthy, aren’t displayed correctly, in that the second c is also in lowercase, e.g. Mccarthy.


Example 6 – Capitalizing only the first letter of a sentence and putting the rest in lowercase

The PROPER function works well with names, but what about normal sentences? We usually don’t want every word to start with a capital letter.

Here I have a sentence all in uppercase and as it seems that the person is ‘shouting’, I want to correct it automatically and add a capital letter at the start.

functions8-23

This sounds like a simple thing to do, but the formula involved is quite long. However, the way it works is quite simple.

functions8-18

Let’s go through it from left to right. Firstly, it puts the first character to the left in uppercase, i.e. the first letter of the sentence. Then it puts the rest of the sentence in lowercase, by finding out the length of the text and ignoring the 1 character at the start of the sentence.

Here’s the result:

functions8-24

Here’s the formula in case you want to copy and paste it into your sheet:

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))

To adapt it to your needs, just edit the cell reference A1.


Bonus Example – Changing a name to initials

In Spain, names can be quite long as people can have two first names and also two surnames. A common practice in business, is to refer to people in emails and documents by their initials. We can get sheets to display the initials by looking at the full name and returned the first letter of each name.

functions8-20

This example is not strictly to do with the functions in this post, but is an example of how other functions can be used to manipulate names. The formula is particularly long, but in fact, from the second MID function, it just the same formula repeated to analyse each word in sequence.

functions8-21

As we can see it takes the four word name and returns the person’s initials.

functions8-22

If the initial name was in lower case, the initials would also be in lowercase.

With the formula split into 4 component parts, you can see that after the initial LEFT function, there are 3 parts that are almost identical. The only difference is that in the SUBSTITUTE formula, the number changes from 1 to 3.

=LEFT(A1)

&MID(A1,FIND(“#”,SUBSTITUTE(A1&” “,” “,“#”,1))+1,1)

&MID(A1,FIND(“#”,SUBSTITUTE(A1&” “,” “,“#”,2))+1,1)

&MID(A1,FIND(“#”,SUBSTITUTE(A1&” “,” “,“#”,3))+1,1)

To adapt it to your needs, just edit the cell reference A1. If you only have 3 names, you can omit the last part. If you have more than 4 names, just add another &MID part, and make sure the number on the SUBSTITUTE brackets goes up by 1 (for example to 4).


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – FILTER

Here we’re going to look at the FILTER function. But you can add a filter to a table via the filter option in the menu, I hear you cry! You can, but the FILTER function allows you to put those filtered results on a different part of your page or on a different sheet, and thus, not affect the original table. It also allows for more complex filtering, which the filter option in the menu doesn’t offer.

Here are some examples showing you the various uses of the FILTER function and how it can also be combined with other functions, to filter out the information you need, in one single formula.


Example 1 – Filtering a list by a piece of text

Here we have a list of companies and the products they sell. I want to a list of the ones only Widgets Ltd sell.

functions7-1

I want to leave the original list untouched, so I put my FILTER function on a different part of the page, for example, in cell D2. First, I put the same headers as the original list (just by copying and pasting).

functions7-2

Then in cell D2, I write the following FILTER function:

functions7-3

This looks at the range A1:B12 (i.e. the data in the original table), then shows anything that matches the condition. In this case, it’s looking in column A (A1:A12) and shows anything that matches the name “Widgets Ltd”. Note, the use of speech marks for text.

It then automatically lists just those products that Widgets Ltd sells.

functions7-4


Example 2 – Filtering a list by those who still need to pay

An academy has a list of students, the cost of their course and how much they still need to pay. Here I want to make a list of those who still need to pay, so we can contact them.

functions7-5

I put the headers in columns E to G.

functions7-6

Then in cell E2, I write the following FILTER function:

functions7-7

Here I’m looking at the range A2:C12. Note, it’s usually better to not include the header in the range, i.e. not A1.

Then, I want to know who hasn’t paid, which will be anyone who has more than 0 euros to pay in column C. So, I write the range C2:C12 and state anything more than 0.

This then makes a list of those who still need to pay for their course.

functions7-8


Example 3 – Filtering a table by a specific date

Here we have a list of appointments, with the date, time and person’s name.

functions7-9

I want to filter the table to just show those appointments on 24/10.

functions7-33

In cell E2, I write the following FILTER function:

functions7-34

It looks at the table (A2 to C12) and then looks at column A to find dates that match the date in cell H1, i.e. 24/10.

I could also find out the appointments I have today, by just adding the TODAY() function instead of the cell reference:

functions7-35

This then displays just the appointments with today’s date.


Example 4 – Filtering a table between two dates

Here we have the attendance of a particular group of 6 students for the period between 3/10 and 21/10. I want to show the attendance between two dates.

functions7-10

To the side I set up my filtered list, with the same headers as the original list, plus I want to be able to type in the date range (i.e. start and finish date), so I add that in columns P & Q.

functions7-11

In cell I3 I write the following FILTER function:

functions7-15

This looks at the range A3 to G17 (the original list), then filters by two conditions. The first looks in column A (the dates) and shows anything that is equal to or greater than the date set in cell Q1 (i.e. 10/10). The second looks in column A and shows anything that is equal to or less than the date set in cell Q2 (i.e. 16/10). So, effectively it’s looking for dates between 10/10 and 16/10.

It then shows just those rows that meet the criteria.

functions7-12


Example 5 – Filtering a table by a specific month

Here we’ve got a similar situation to the one before, where we have the attendance of a group of students. This time they have attended class across 3 different months, October, November, and December.

functions7-13

I want to filter the information so that it just shows the attendance for November. So, first I set up a place to show the filtered results by copying the first two rows from the original list.

functions7-14

Then in cell I3, I add the following FILTER function:

functions7-18

This looks in the table (A3 to G17) and then looks for a month in column A that equals the 11th month, i.e. November. In case you haven’t come across the MONTH function before, this returns a the month number from a date, so, January is 1 and December is 12, and so on.

As we can see it gets just those rows which are in November:

functions7-16


Example 6 – Counting the number of values that meet the filter condition

This time instead of returning the actual filtered data, we’re going to count how many times the data in the table meets our criteria.

So, using an example, we have similar data to what we saw in Example 2, where we have a list of students, this time with the course month they want, and how much they still need to pay.

functions7-19

I want to find out how many students still need to pay for the November course. So, to the side, I set up a place to enter the month I want (cell F1) and where the result will appear (cell F2).

functions7-20

In cell F2, I write the following FILTER function combining it with the COUNT function:

functions7-21

Let’s first look at the FILTER part. This looks a the table (A2 to C12), then looks to see if two criteria have been meet. Firstly, do the months in column B equal the month in cell F1 (i.e. November)? Secondly, are the figures in column C over 0€? If both criteria are met then it returns a match.

This would normally, display the 2 matches, but I’ve surrounded the FILTER function in a COUNT one, which will just return the number of matches, in this case, two.

functions7-22

The use of this is similar to that of COUNTIF. See my post on this for more info.


Example 7 – Summing up the total of values that meet the filter condition

Using the same data, this time I want to find out how much in total is still to be paid for the November courses. Instead of using the COUNT function I’ll use the SUM one.

functions7-23

I enter the month I want in cell F1 as before.

functions7-24

This time I just want to look in column C, as that’s where the figures I need to add up are. Then it needs to look in column B and find any months which match the month in cell F1, i.e. November. Finally, I surround it all in a SUM function to add up the money that is still to be paid for the November courses, i.e. 0€ + 200€ + 600€ = 800€.

functions7-25

Note, here we don’t need to worry about the values in column C being 0, as adding these up won’t make any difference to the total.

functions7-26

The use of this is similar to that of SUMIF. See my post on this for more info.


Example 8 – Filtering a table using one criterion OR another

In examples 4 and 6, we looked for data that met BOTH criteria. In this final example, we’re going to see how we can look for data that meets one criterion OR another.

Here we have a list of classes and teachers. In column A, we have the level name, column B the different for that level, and the teachers who are teaching those classes.

functions7-27

I want to list just the Junior 1 and 3 classes. So, in columns E, F, and G I set up a place to put the filtered responses.

functions7-28

Then in cell E2, I write the following FILTER function:

functions7-29

This looks at the table (A2 to C18), then looks for data that meets 1 of 2 criteria. Firstly, in column A it’s looking for levels that are called Junior 1 and secondly, in column A, levels that are called Junior 3.

The important thing here is the plus sign between the two criteria. This means that it will display any row that has the level Junior 1 OR Junior 3.

functions7-30


You may have noticed that when the filtered results are returned, only the values are returned and the formatting is not copied. So, sometimes afterwards you will need to add borders, colours, etc to the filtered results.

Also, sometimes you may get an error message instead of returned results. This is sometimes because you’ve not left enough blank space below the filter cell for the results. It won’t overwrite any cells that aren’t empty, it will just return an #REF! error. So make sure you put your FILTER function where there is plenty of space below it.

If you decide to put your filtered results on a different page, you will need to include the sheet name in your formulas. For example, the main table is on Sheet 1, so in the first part of the formula, I write Sheet1!A1:1B12, which refers to that range on Sheet 1.

functions7-32


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

Baz Roberts (Flipboard / Twitter / Google+)


 

Google Sheets Functions – COUNTIF & SUMIF

In this post we’re going to look at how we can count things and add things up depending on certain conditions that we set. There are four functions we’ll look at here: COUNTIF, SUMIF, COUNTIFS, and SUMIFS. Here I’ll show you the various aspects of the functions through some practical examples.


Example 1 – COUNTIF: Counting the number of instances of a specific number

In this example, and to introduce COUNTIF, we have a list of part numbers of some products and we want to know how many parts we have that are numbered “123”.

functions6-1

In cell B2, we write the following formula:

functions6-2

There are two parts, first you state the range of values you want to look at, then in the second part, you state what you want to look for. So, in this case, we’re looking in cells A2 to A16 and we are looking for the number 123. As it looks down the row, every time it finds the number 123, it adds it to the count. So, in this example, it found 6 instances of the number 123.

functions6-3


Example 2 – COUNTIF: Counting how many values are over a certain number

We can also use COUNTIF to count how many values are greater than or less than a particular number. In fact, it can check against pretty much any type of mathematical condition, an example of which we’ll see later.

Here we want to see how many students got over 60 in a test, maybe to divide the group into a stronger one and a weaker one.

functions6-4

In cell C2, as before we start off with the COUNTIF function, set the range we want to look at, in this case it’s B2 to B9, then set the condition to look for any tests over 60. Notice the syntax of the condition. When we use the operators, like greater than or less than, we have to wrap them up in speech marks.

functions6-5

And correctly, it’s found 5 students with a test result of more than 60.


Example 3 – COUNTIF: Counting the number of times a word or phrase appears in a range

This time we’re going to look for a piece of text. We have a book inventory and we want to know how many books called “English File· we have.

functions6-6

In cell C2, we write the following formula:

functions6-7

Like the operators above, text needs to be in speech marks. Here it’s found 6 books called English File.


Example 4 – COUNTIF: Using a cell reference to find instances of whatever has been written in that cell

So far, we’ve stated the condition within the formula but often we want a flexible formula that can look for any condition we type in a particular cell.

Here we have the same book inventory but this time we give the user the option to type in the book they are looking for in cell C1. Then in cell C2 it will tell them how many books we have of that name.

functions6-8

In the condition part, the important part and the part where most problems occur, is the syntax here. First, we wrap the equals operator up in speech marks like we saw in example 2. Then to refer to a cell we need to first add the ampersand (&) then the cell reference.

Whatever we type in cell C1, the COUNTIF will look for in the range, making this far more flexible than stating it within the formula.

functions6-9

For those who have read my post on CONCATENATE, this is one reason why I use CONCATENATE rather than the ampersand to join cells together, as the ampersand has different uses and can be a bit confusing if you have more complicated formulas with the ampersands doing different jobs.


Example 5 – COUNTIF: Using wildcards to look for broader values

Here we have a list of classes and we want to know how many junior classes we have. The problem is that, there are different levels of Juniors, 1, 2, and 3, so our formulas so far wouldn’t be able to find all these different ones. That’s where a wildcard comes in.

functions6-10

In cell B2, we type the following formula:

functions6-11

The key part here, is the use of the asterisk (*) after the word Junior. This allows it to look for anything that starts with the word Junior and it doesn’t matter what comes afterwards. Hence it will count the number of times there is a class starting with the word Junior. In this case there are 8.

The wildcard can also be used before the word, for example, you could look for all the classes in level 1, by typing “*1”, which would look for anything ending in 1.


Example 6 – COUNTIF: Counting dates

The final example of COUNTIF, shows us that we can also count the number of times a date appears. For example, here we want to know how many appointments we have on 17/10/2016.

functions6-12

Dates like text, need to be in speech marks. It’s also important to match the format. I usually state the day, month and year, even if the dates in the table are formatted in a different way. I.e. if the data just showed the day and month, the formula would still work.

functions6-13


Example 7 – SUMIF: Adding up the number of products sold on a particular date

Now let’s look at SUMIF. This is similar to COUNTIF but the syntax can be slightly different as quite often we’re not looking at just one range.

Here we have some products that have been sold on different days and we have how many of each product has been sold. What we want to know is how many products did we sell on a particular date, e.g. 17/10. In cell E1, the user types in the date they want to find the information for, then in cell E2, it will tell us how many have been sold.

functions6-14

In the brackets, we first type the range where we’re looking for the date, e.g. A2 to A12. Then we add the criteria, in this case whatever’s in cell E1 (the date). Then for SUMIF, we add a third part, this is the range which contains the values we going to sum, e.g. quantities sold (‘sum range’).

functions6-16

functions6-29

So in our example, it’s found 3 dates that equal the 17/10 and summed up the quantities sold on those dates, i.e. 100+300+600 = 1,000.


Example 8 – SUMIFS: To find the quantity sold within a date range

Similar to SUMIF, we have the function SUMIFS, which uses more than one criteria before adding up. Here we have the same information as before, but this time we want to find out how many products we’ve sold between two dates (17/10 to 19/10).

functions6-17

Important: The syntax here is different from SUMIF, and let’s go through it step by step.

functions6-31

functions6-18

We start with the range we want to get the values from, i.e. the sum range (C2:C12).

Then we state the range we want to find the dates in, i.e. A2 to A12.

Then the first criteria we want to use, i.e. the date is greater than or equal to the date in cell E1, in this case, 17/10/2016 (“>=”&E1). As before the operator is in speech marks, and the cell reference has an ampersand before it.

Then we add the second condition. In this example, the range is the same, i.e. A2:A12.

Then we want to look for dates less than or equal to the date in cell E2, in this case, 19/10/2016 (“<=”&E2).

It then adds up the products sold between those dates, 100+300+150+200+600+200+300+450+100 = 2,400.

It looks a fairly complicated formula but it really just has three parts:

sum range, criteria 1 (range & criteria), criteria 2 (range & criteria)

We can add more criteria if we want just by adding another range and criteria on the end.


Example 9 – COUNTIF: Counting how many students are above average

Returning back to COUNTIF, we can use other functions as the criteria within the COUNTIF one.

Here we want to see how many students got over the average mark in an exam. Normally, we would have to find the average of all the marks, then see which marks were above that figure. Well, that’s exactly what the COUNTIF function can do for you.

functions6-19

In the second part, we want to know what marks are greater than the average of all the marks in that range. So, we start off with the greater than operator in speech marks, then like the cell referencing we need to add an ampersand before the function AVERAGE. Then we add the range we want to average.

functions6-20

The average was 69%, and so correctly it’s found that 4 students got an above average mark.


Example 10 – COUNTIFS: Highlighting duplicate rows

In this final example, we can use the COUNTIFS function in conjunction with conditional formatting, to highlight rows on our sheet which have duplicate information.

Here we have a list of classes, their timetables and teachers. Some of the rows are duplicates and we can use COUNTIFS to find the duplicates. We add a fourth column to record if the row is duplicate or not.

functions6-32

In column D we add the following formula:

functions6-22

It works by looking in the 3 columns A, B, and C and sees if there is more than 1 occurrence of the contents of that row. So taking the first row (row 2) as an example:

It checks to see if the content of cell A2, B2, and C2 occurs more than once in the rows below, which it does, i.e. in row 6. So, it states that it’s TRUE that it occurs more than once, in other words, it’s duplicated.

If we look at row 3, we can see that this time that the combination of those three pieces of information are not repeated in the other rows. So, it states that it’s FALSE that it occurs more than once.

We could leave it like that and if we wanted to remove the duplicates or edit them, we just look for those rows which have TRUE in them. However, to make it easier to find them, we can colour the rows to highlight them.

Having selected the contents of the table (range A2:A13), right-click and select “Conditional Formatting…” from the menu.

functions6-24

This opens the Conditional format rules sidebar menu. Click on the drop down menu, which by default says “Cell is not empty”.

functions6-25

Then scroll down to the bottom of the menu and select “Custom formula is”.

functions6-26

Here type in the formula below:

=$D2=TRUE

functions6-27

This checks to see if the value in column D is TRUE. If it is, it adds the formatting to the whole line. If you want to know who this works better, read my post on Conditional Formatting.

functions6-33


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets – Alternating colours in a table

When working with tables in a spreadsheet, we often want them to be easy to read and understand. To help us Sheets now has a quick little tool, which allows us to add alternating colours to the rows of our data, making them not just prettier but much easier to read, whilst being really quick to create.

Here we have a plain table of data.

sheetsb1-1

First, highlight the area you want to format (usually the whole table).

sheetsb1-2

Go to the Format menu, then click “Alternating colours”.

sheetsb1-3

This will open the sidebar. Here from top to bottom you have:

The range the formatting will apply to.

Whether you want a different colour for the header of the data or the footer.

A set of default colours to choose from.

The option to create your own default colour combination, which is then saved for future use.

At the bottom, you have the option to remove the formatting.

sheetsb1-4

When you first open the sidebar, by default it adds a grey and white format to your data.

sheetsb1-5

To change this, just select one of the default options.

sheetsb1-6

Here I’ve selected a purple combination. As you can see, it’s easier to read and looks better too.

sheetsb1-8

If you want to choose, your own colours, either select a default one and edit it, or click the plus button to add a new one. Then click on the fill symbols to the right, to change the colours.

sheetsb1-7

By default, this adds a darker header row at the top. You can choose whether you want to have this and also whether you want the last row, the footer, to be a different colour, for example, usual for totals. Just tick the ones you want.

sheetsb1-9

Here I’ve added both a header and a footer.

sheetsb1-10

Once you’ve selected the options, click Done to confirm the changes.

sheetsb1-11


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – OR & AND

With the IF function we usually check if one condition is true or false, but what happens if we want to check multiple conditions? This is where the OR or AND functions come in handy. With OR we can check if one or other conditions are true, and with AND we can check if all the conditions are true.

We’ll start with how the function works then look at some practical examples of both.


Example 1 – How does the OR function work? And how does it work with IF?

Let’s first look at how the OR function works. Here we have two numbers in cells A1 and A2 and we want to know if either number equals 10.

functions5-2

In cell B1, we write the following formula:

functions5-1

This looks at the contents of cell A1 and decides if it’s equal to 10, and then looks at cell A2 and again decides if it equals to 10. If either cell has a 10 in it, then it will display TRUE. If there isn’t a 10 in either cell, then it will be FALSE.

So, in the example above, we have a 10 in cell A1, and correctly it displays TRUE in cell B1 meaning that it’s found a number 10 in one of the cells.

This is great, but displaying TRUE or FALSE, often doesn’t help us very much, and what we want is that if it’s TRUE, then the formula does something, and if it’s FALSE, it does something else.

So, quite often we wrap up the OR function in an IF function, to allow us to take action depending on whether it’s TRUE or FALSE. See my post on the IF function if this is new to you.

So we modify the formula like this:

=IF(OR(condition 1, condition 2), Action if true, Action if false)

functions5-3

We add IF and an open bracket before the OR function, then after it, we add a comma and the two actions we want to take. In this case, if the function finds a number 10, it displays the text “At least one is true”, and if it doesn’t, it displays the text “Both are false”.

functions5-4


Example 2 – Checking to see if at least one race time is below the target time

Let’s use the above formula in a practical example. Here we have some athletes who need to have run under 10.5s in the 100m in two pre-competition races, in order to be able to enter the competition. We have the times they ran in both races, then in column D, we add the OR formula to see if they can compete or not.

functions5-5

Here the formula in column D is similar as before, we start with an IF function, then the OR function.  Then we add the two conditions, in this case, a check to see if the value in column B is less than 10.5s or if the one in column C is less than 10.5s. If it finds that the athlete has run under 10.5s in one or more of the races, it states “Yes” they can compete in the competition. If they haven’t run under 10.5s, then it displays “No”.

functions5-6

So, we can see that Paul and John are ok to enter the competition.


Example 3 – Looking at more than two race times

In the example above we looked at two race times, but the OR function can look at lots of different values. Here the athletes have run three races.

functions5-7

The formula in column E is as before, except that we just add a third condition in the brackets.

functions5-8


Example 4 – Using the AND function

The AND function works in a similar way to the OR function. OR looks to see if 1 or more of the conditions are met, whereas, AND checks to see if ALL the conditions are met.

Here we have some students who have to get more than 60% in all three tests on the course, in order to pass the course. If they have, it displays “Passed”, if not they haven’t it displays “Failed”.

functions5-9

So, in place of the OR function, we add the AND one in column E.

functions5-10


Example 5 – Using AND to check if the values are within a range

Here we want to see which groups the students should be in. We’ve already put those who got 80% or more in group 1, and now we want to divide the remaining students into either group 2 or 3. Those who got more than 60% and less than 80% will go in group 2, and the rest will be in group 3.

functions5-11

To do this, we can use an AND function in column C. We look at the values in column B to see if they are more than 60% or less than 80%. If the test results meet this condition, then it displays “Group 2”, otherwise it displays “Group 3”.

functions5-12


Example 6 – Categorising the values based on certain conditions

In the example above, we had already allocated some students to Group 1, but this time let’s divide up the students into 3 groups all automatically.

functions5-13

First, in column C we start off with an IF function, and check for those who have 80% or more. If their test result is 80% or more, it displays “Group 1”. If the test is less, it moves onto the next IF statement.

This time it checks to see if the result is between 60 and 80%, as we saw in the example above. If it does, then it displays “Group 2”, if it doesn’t it shows “Group 3”.

functions5-14


Example 7 – Using functions within an OR or AND function

Introducing the WEEKDAY function

We can also use other functions in the OR function. Here we have some prices where if the work was carried out on a weekend, a weekend supplement is added.

We need to check to see if the date fell on a weekend, then based on whether that is TRUE or FALSE, we add a supplement or not.

functions5-15

In column B, we add this formula:

functions5-16

The WEEKDAY function looks at the date in column A and converts that into a number between 1 and 7. Sunday is 1 and Saturday is 7. So, if it reports a number that is a 1 or a 7, then the date is at the weekend.

In column E we then check to see if column B is TRUE or FALSE, if it’s TRUE we add the supplement, if not, we leave the price as it is.

functions5-17

We could combine the formulas in columns B and E by wrapping the OR function in an IF one:

So, we have if the date in column A is Saturday (7) or Sunday (1), it adds the price (C) and the supplement (D), if not it just displays the price (C).

functions5-21

Just as a little extra, if we wanted to check to see if it is a weekday, we could use an AND function. This checks to see if the date isn’t a Saturday (7) and isn’t a Sunday (1).

functions5-18


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

Baz Roberts (Flipboard / Twitter / Google+)


 

Google Sheets Functions – VLOOKUP

Here’s how to look up values in tables in Google Sheets using the really useful VLOOKUP function (Vertical Look up). You give it something to look for in the table and it gets the value you’re after from a different column in that row. As always the best way to show what it does is through examples, showing different aspects and uses of the VLOOKUP function.


Example 1 – Look up a book number and get the name of who has it

Here we have a list of books, which have an individual reference number, a title and the teacher who is using it.Here we want to find out who has book number 0185. With a small list like this we can easily see who has which book, but imagine a list with hundreds or even thousands of books, that would require looking through from list manually. Of course, this is where the VLOOKUP function comes in, as this will deliver the information you need instantly.

functions4-42

We need two things, firstly, a cell to type in the book number we want to find, e.g. C14. Then secondly, a cell to deliver the result, in this case, C15.

functions4-2

In cell C15, we add the VLOOKUP formula. As we can see from the help information, we can see that it requires 4 bits of information.

search_key: What we’re looking for

range: where we should look for the information, often this is the whole table

index: which column is the information we’re looking for in. The first column of the range, is index 1, the second is index 2, and so on.

is_sorted: Is the information we’re looking up, i.e. the search info, sorted in order or not? We either put TRUE or FALSE. Another way to look at it is whether you want to find a specific piece of information, i.e. an exact match, or whether it falls within a range (which we’re look at in example 3).

functions4-3

So, in this example, we’re looking at the contents of cell C14 (book number), looking in the table (range A2:C14), then looking up the value in the third column (teacher in column C), and finally I’ve stated the range is not sorted as I want an exact match to the book number, i.e. FALSE.

functions4-4

As you can see it’s correctly found that John has book number 0185.

functions4-5


Example 2 – Display a message instead of an error, if something cannot be found

If I tried looking for a book that’s not on the list, e.g. book number 0191, it will report an error “#N/A”.

functions4-6

If we hover over the error, it will tell us that it couldn’t find that book number.

functions4-7

The error looks a little unsightly, and we can modify the VLOOKUP function up so that if it does find an error that it displays a message instead.

To do this, we wrap the VLOOKUP function up in an IFERROR function. Before the VLOOKUP function we type IFERROR then open the brackets. Here it needs two parts, the value (here the VLOOKUP formula), and what to display if it there’s an error.

functions4-8

After the first part we add a comma and then for example, write a message we want to display if it finds an error.

functions4-9

In this case, let’s display “Not found”, if it doesn’t find the book number. Then we close the brackets, so it’s all wrapped up.

functions4-10

As we can see the book number in cell C17, hasn’t been found and so the message “Not found” appears in C18.

functions4-11


Example 3 – Finding values within ranges

We can also look for values that fall within different ranges. Here we different marks in a level test that correspond to different levels. So, for example, someone who gets 60 in the test, has an advanced level.

functions4-12

So, here we can set it up to look for the level that corresponds to the test mark in cell B9. So in the formula, we add the B9 as the search key, the range where the table is (A2:C7), column 3 (level) and this time we state it’s TRUE as the value we’re looking for is within one of the ranges and not an exact value, so we need to tell Sheets that the ranges are sorted. If we put FALSE, we’ll get an error as “20” isn’t specifically stated in columns A or B.

functions4-13

Sure enough, it finds that 20 is in between 16 and 25 and therefore the level that corresponds to that is Intermediate.

functions4-14

Sometimes it’s more convenient to store the reference table on a different sheet. For example, we use a Google Form connected to a Google Sheet to record level tests completed and the results of the tests appear on one sheet, then using VLOOKUP, the results are compared with the reference table on another sheet.

functions4-15   functions4-16

To do that we modify the formula, so that the sheet name is added to the range. E.g. the table is on the sheet called “Ref” in cells A2:C7, so we write Ref!A2:C7.

functions4-17


Example 4 – Including formulas in a VLOOKUP function

In the example above, we had already calculated the total mark the student got in a test, then used that total to find their level. We can also do the calculation directly within the VLOOKUP formula.

Here there are four parts of the test, Reading, Listening, Speaking, and Writing. What we want to do is sum those parts then look up the level that corresponds to that total.

functions4-18

In the search key we add a SUM function, i.e. sum(F2:I2), which adds up the four parts of the test, that we complete the VLOOKUP formula as before.

functions4-19

And again it finds the correct level.

functions4-20


Example 5 – Using wild cards in VLOOKUP

Sometimes we don’t want to state the complete search term we’re looking for. For example, here we want to look up a class for a teacher and either we don’t want to type in their full name, or maybe we don’t know their surname.

Here we have a list of teachers and the classes they have.

functions4-21

We want to find out which class Betty has. Using a normal VLOOKUP formula won’t find her as her full name in used in the table.This is where wildcards can be used.

functions4-22

In the search key refer to the cell and add the wild card symbol “*”. This then looks for anything beginning with the name in B7, i.e. the first person it can find with the name Betty. We join the contents of B7 with the wild card by typing B7&”*”.

functions4-23

The rest of the formula is as normal.

functions4-24

Note, if there were two Bettys in the list, it would only find the first one.

There are two wild cards we can use. “*” will ignore any number of characters, and “?” will ignore a single character. So, in this case B7&”?” wouldn’t find Betty Rubble.

functions4-25


Example 6 – Looking up multiple values in a table

So far we’ve been searching for one thing, but what happens if we want to search for more than one? Well, that’s possible too. In this example, we want to find the quantity of a particular product by a particular company, i.e. we’re looking for two values then finding the quantity that corresponds to those.

We have two companies (Widgets and Gizmos) and they both sell two products (VR Headsets and Virtual Assistants). We want to find how many VR Headsets Gizmos has.

functions4-26

To allow us to look for two values, one of the easiest ways to do this is to create an additional column where we combine the information in the first two columns. So here we’ve added a ‘helper’ column in column C, where we join the company and product together. In effect we’re creating one value which we can look up later on.

functions4-27

In column C, we type the following formula:

functions4-28

This gets the value in cell A2 adds a space then adds the value in cell B2. We could also use CONCATENATE (see my post on how to do this). We then copy that formula down column C, so we end up with all the companies and products.

In cell B9, we add the VLOOKUP function. As we are going to allow the user to type in the company in B7 and the product in B8, we need to get those two values and join together, then look for that combined value in the table.

So, our search key is B7 and B8 joined together with a space in the middle, similar to the formula used in the helper column.

functions4-29

Then we add the range, note I’ve just put columns C and D instead of the whole table. It doesn’t make any difference, just remember that when it comes to the index, as I’ve only selected two columns, the information I’m looking for is in the second column, i.e. index 2.

functions4-30


Example 7 – Retrieving multiple values from a table

In a way this example is the opposite to example 6, in that this time we have one value, a name, and we want to retrieve more than one value from the table.

Here we have some students and their test results across the year. Every semester I need to complete a report and I only want semester 1’s results in one report and then semester 2’s in the other. I could refer to the individual cells, for example, in the semester 1 report for Ringo, I could write in cell B10, =B5, to get his test 1 result, but of course having to do that for all his tests and then for all the other students is a slow way to do it. Instead we can use a VLOOKUP formula with an array.

functions4-31

functions4-32

An array in this case, is just telling Sheets how many spaces to the right you want to go to get the value. In the table above, our table goes from column A to G, or if they were numbered it would go from column 1 to 7. So to get the test results for Ringo for semester 1, we want the cells in columns B, C, and D, or in terms of numbers, 2, 3 and 4.

To do this, we need use the ARRAYFORMULA function along with the VLOOKUP one. All it’s doing is allowing us to retrieve more than one value.

In cell B10, we start off with =ARRAYFORMULA then the VLOOKUP function. We refer to A10 (student’s name), A3:G6 (the table), then within CURLY brackets we add the column numbers we want to retrieve. As stated before, that’s columns 2, 3, and 4.

functions4-33

As we can see it’s retrieved the 3 test results from the table. Note, there’s no need to type anything in cells C10 or D10, they are filled in automatically.

functions4-34

To get Ringo’s second semester tests, we use the same formula, except this time we looking in columns 5, 6, and 7 (i.e. columns E, F, and G).

functions4-35

functions4-36


Example 8 – Retrieving multiple values from a table (part 2)

The multiple values we extract from a table don’t have to be next to each other as in the example above. We can get them from anywhere along that row.

In this example, the students have done 3 tests and each test has a written component and an oral test. I want to separate their written marks from their oral ones and put them in the tables below.

functions4-37

This is just like the example above. We get the student’s name from cell I10, look in the table I3:O6, then get the values from columns 2, 4, and 6 (columns J, L, and N).

functions4-38

functions4-39

Similarly, we can get his oral marks, but this time we get the values from columns 3, 5, and 7 (columns K, M, and O).

functions4-40

functions4-41


Example 9 – Using Named Ranges with VLOOKUP

Sometimes it’s useful to name the range in your table, so you know which table you’re referring to, particularly if you have more than one in your document. Also, if for some reason there’s a chance that the formula will be moved or copied elsewhere, then a Named Range is absolute, i.e. it always refers to the same range even if it’s moved.

Looking back at our first example, let’s create a Named Range for it.

functions4-43

First we select the whole table.

functions4-44

Then right-click and select “Define named range…”.

functions4-46

This will open the sidebar where you can name the range and edit it if necessary.

functions4-47

Change the default name to something meaningful, for example “BookList”. Note, you can’t have a name with spaces in it. Then click Done.

functions4-48

now when we set up the VLOOKUP formula, in the range we just type the name of the range, we will see the range get highlighted.

functions4-49

The rest is as before and we can see that it works just as first example.

functions4-50

Say we want to move the book number entry and teacher to a different part of the sheet, this doesn’t affect it at all, as it continues to refer to that particular range.

functions4-51

We could do the same by adding dollar signs to the range, to make it an absolute reference and not a relative one. See my post on absolute and relative referencing.

A couple of final points. The search keys used are case-insensitive, so it doesn’t matter if you type betty or Betty, it will find the same result. Here we’ve looked at Vertical Lookup, but there is also Horizontal Lookup, HLOOKUP, which works in a similar way, and will depend on how your data is set up. However, VLOOKUP is far more common.


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

Baz Roberts (Flipboard / Twitter / Google+)