Month: November 2016

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. 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. If we look at the formula, it hasn’t changed despite there being an extra row. We can solve this by using the OFFSET function in the AVERAGE one. In cell E6, I’ve added the following formula: OK, so what’s happening? Well let’s look at the syntax of the function to understand it better. The OFFSET function has 3 main parts: cell reference:...

Read More

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: 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. 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. As we can see...

Read More

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. The GOOGLETRANSLATE function has 3 parts. First, the source text (here in cell A2), second, the source language...

Read More

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: This will add the current date and time in the cell. 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”. Under “Recalculation”, click the drop-down menu that says “On change”. Here you will have three options. Choose the one you want, then click “Save settings”. 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: This adds today’s date in the cell. 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: This just subtracts Christmas day from today’s date and returns the number of days, which at the time of writing, there are 41. We...

Read More

Google Forms (12) – Quizzes

In this post we’re going to look at the quiz option in Forms. Since its 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.   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. 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. 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. 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...

Read More
  • 1
  • 2

Archives

eBooks available

Pin It on Pinterest