Category Archives: Helpful Tips

These posts are just helpful, not really a review or a how-to, just some helpful tips

Picking an Interactive Whiteboard (IWB)

This is a pretty meaty topic so hang on. An interactive whiteboard (IWB), or interactive flat panel, is basically a large TV that ranges 55“ – 88” (139cm – 224cm) that has touch capabilities. They usually come loaded with an Android OS or Windows 10 (some can even swap between the two) and most are priced very similarly to each other. That last fact is what makes it a bit difficult to chose, because there are A LOT of companies who are in this space. I’ve looked at mu fair share of these and I’m not here to talk about specific brands but to examine the characteristics and features that we looked for.

For us we are looking to purchase one interactive whiteboard for our library. Our teachers do not want these to replace their traditional whiteboards and markers (in fact, when we were demoing different units this was a fear from some teachers).

Here we go!

Hardware

I heard this from more than one vendor. The actual screen and hardware that powers it is nearly the same across all brands. There are only 2–3 companies that make these screens and then they sell them to other companies who put their specific hardware, casing, speakers, cart on it. This is why most of them are priced so closely to one another. So if a vendor says, well our 4K display is much, much better than that 4K display just look past that fact and focus more on how it works and what it can do. However, I will say that all the 4K displays looked stunning – regardless of brand.

Whiteboarding and Annotation

This turned out to be one of the most important features for our teachers. All the interactive whiteboards (IWB) can … well, whiteboard. They all had their own proprietary app that basically brought up a white screen that you could draw or write on with your finger or a pen. They all had fairly accurate and pretty responsive and lines, objects and words all showed up where you expected them to show up as you drew them. This was pretty universal and a good sign that touch has come a long way in the last ten years.

Getting the workspace off the whiteboard and onto a computer or cloud service was a big question. It is great that you can illustrate, model or brainstorm on this beautiful screen but how can you then save that and then share it with your students? One we looked at had us save it to a folder on the device itself, then you would have to open up Google Drive (Edmodo, Google Classroom, Office 365 – whatever service you would like) and then add it and share it from that service. Not bad but not easy.

Another IWB we looked at actually logged you into your cloud service of choice and you could save it to a folder automatically. This was much better and less cumbersome.

Also, teachers wanted to be able to annotate on web pages. All the boards offered this in one way or another. One board let you activate the annotation tools and you could then annotate right on the webpage without leaving the browser. You could then save that annotation or discard it right from a floating toolbar. Once that was done the toolbar disappeared and you could continue browsing.

Another IWB actually took a screenshot, loaded the screenshot into the whiteboarding app and you could annotate from there. Clunky.

All of our options had the ability to also do screen recording but again, some where better than others and sometimes finding that recording was not as intuitive as it should be. All in all, from a teacher perspective the whiteboarding and annotating features turned out to be the most important feature.

Operating System and Apps

I mentioned that the boards we looked at had different operating systems but they all boiled down to Windows 10 and Android. Here is what we found. With Windows 10 – it is a fully functional computer. Whatever you can install on a regular PC you can most likely install on the IWB. This is good and certainly a characteristic that was important to consider even though our teachers use Macbook Airs. Having the ability to launch an app without having to log into a website was very appealing. The Android IWB we looked at had varying amounts of apps that we could install. None of them had the actual Google Play Store meaning we would have to sideload the app and then there was no guarantee that it would work.

A teacher pointed out to me that if they wanted a particular program to be running, they would hook up their computer or connect wirelessly via Apple TV (which our school would provide) so the importance of apps on the whiteboard was not a deal breaker for us and probably shouldn’t be for you either.

Many of the IWBs would allow you to swap out the computer for another. This may be very handy if you have a fleet (one in every classroom). You could have a warm spare ready to deploy incase something happened to a particular board. Once swapped out, the technology team could take the bad one back and try to repair it and get it ready for another swap. Of course, the data on that damaged computer would most likely be lost – so save in the cloud folks!

Connecting other devices

All the IWBs we looked at could connect multiple devices to it. Usually via an HDMI cable. They also contained a USB B port as well that you could connect to your computer. What this would do would show your computer’s screen on the IWB and allow you to interact it with via touching the whiteboard. This worked pretty well for all but one thing to keep in mind is that gestures do NOT work with this interface. I could not use two fingers to scroll a webpage for example or tap two fingers on a file to bring up options. This seemed to be true for all we tried.

We tried an Apple TV and it worked fine. No touch but this was to be expected. We also tried an AirTame and it worked fine as well but again no touch capabilites.

Sound

This is more for me than the teacher or others on the tech team. I expect that this device should be able to pump out enough sound that a class of 20–30 students should easily be able to hear. Each IWB had their own built in speakers but some were better than others. One IWB we tried sounded as though the speakers were inside a protective casing. The sound was tinny and a little muffled and definitely not very loud.

All of them did allow for external speakers and all could also the mounting of a soundbar or side speakers, but that is an extra cost and I’ve seen some “mounted” speakers onto IWBs that look like they could fall off with a bump.

Multiple users & Licenses

Some of the IWB were clearly made for a few users. Since this particular board will be living in our library for any teacher to use with her/his class, then we needed to make sure that multiple people could easily switch from one account to another, so this was an important factor for us. If every classroom is getting one, then maybe not so much for you or your school but it is something to keep in mind.

Also, do you need to purchase a license every year to continue to use the board. Some boards have their own proprietary software that requires a yearly license. Be sure to know what those costs are will it still function if you don’t want to use that service anymore. I’ve seen presentations and asked that question and sometimes have gotten conflicting answers from the vendor. Be sure to know.

Try them out and gather data!

I looked at quite a few and if we couldn’t get a demo unit into the school then we did not consider it. It was too important piece of equipment to base it upon a tech spec sheet and tutorial videos. If a vendor can’t provide a demo unit (or if you can’t go to them and try it out) stay away.

Also, when you try them out set up a formal demonstration either through the vendor or doing it yourself. Show off some highlights of each machine and allow teachers to give feedback. I know there will be times I will be use the IWB but teachers and students will be using it more than myself and hearing what they want is critical for getting buy in.

Lasting thoughts

Devices in this area are rapidly changing and developing. Do not worry about buyers remorse. If you find the best fit for your school at that time be comfortable with that decision and don’t kick yourself when the prices drop $500 the next year or when an 8K display becomes available for the same price, etc.

Also, stay away from IWBs that are aimed for corporate environments. I’m looking at your Surface Hub 2. This is a great device and a marvel of engineering but the sheer cost of it ($9000 for the 55″) is not realistic to most K–12 schools regardless of funding.

Finally, make it a team decision. Don’t unilaterally make this decision. Gather a group of educators (teachers are a must) and get feedback. If no one can agree on a path forward then that is not a failure or people being difficult. It is most likely a sign that your school may not be ready for this device yet or more likely, the devices out there cannot meet the needs of your school. I’ve been in a school with “Interactive Whiteboards” and they stunk! Few teachers used them, they weren’t enjoyable to use and basically sat unused.

When it does arrive, be sure to educate your staff and do it often throughout the years to come. Make sure people know how to use the basics and are somewhat comfortable with it.

Self Checkout with a Chromebook!

I know I’m not the first (not even close!) to do this but I figured it would be a good post anyway. We now have a self-checkout station in our library, so our students can check out books on their own. This post will show how we set it up and implemented it, which was easier than I originally thought it would be.

Hardware & Requirements

OK, let’s talk about what we used to make this happen.

Chromebook

We decided to use a Chromebook because it is pretty cheap, doesn’t take up a bunch of space and is portable. Also, it is very easy to manage.

Barcode Scanner

We also gave the Chromebook its own scanner. This was a little more expensive than I thought and we made sure it had a base that it could sit in so students didn’t have to pick it up. Also, we want one that is a little heavy duty so it will last. Think of this as an investment. You can certainly find bar code scanners for much, much less but I firmly believe you get what you pay for here. This one in the picture is $150 USD.

Labels

We also used a label printer to print off student barcodes. We used a Brother label maker with its P-Touch software. This software lets us point it to a spreadsheet so we can design a simple template with tags in it and then print merge out all the labels for a grade level or school in one go.

This is a newer model than what we use but the labels in the end should be the same.

Here is a sample of what one of our student labels looks like.

Library Sticks

We use these are sticks to affix a label to that will have a student’s name and patron number as a barcode. We use some plastic heavier duty sticks instead of paint sticks thought paint sticks would probably work just as well. I do know these are more expensive than paint sticks but they’re already colorful, don’t break easily and won’t give any kids splinters.

I’m not sure what ours are called but I’ll find out later and update the post with that info soon.

Destiny

So our library (like many others) uses Follett Destiny to manage our collection; however, I believe that if you used another database that this could still work for you providing that it is web based like Destiny is. Even if it is run locally on your own network – there may be a place for a local user to log in and checkout books through a web browser. If so, then you could possibly set this up provided you would be able to create or modify roles in that system.

Self-Checkout Access Levels and User

In order for this to work we had to create a user in Destiny that can “check out” books. What we don’t want though is for that user to be able to forgive fines, remove books from the collection and basically bring upon an apocalypse.

Lucky for us, Destiny has lots of options and I’ll show you how we made this user.

First, you need to log in as an administrator in Destiny. Then go to Back Office. From here select Access Levels. Here we can create the role and then we will create the user.

When you are at the Access Levels screen go ahead and click on Add Access Level.

From here make sure you do the following:

  • Give it a memorable name
  • Change the Automatic log out to 240 minutes (this is the max time before the system logs the user out)
  • Change the Reset Circulation time to 120 seconds (or whatever you feel is appropriate)
  • Make sure the Collection Role is Student
  • Make sure the only access is Check out library materials

Then click Save.

Now we will stay on the Back Office page and but switch from Access Levels to Manage Patrons. Click on the Add New Patron button.

This will create a new patron or user. You need to fill in the information circled in the picture:

  • Last name
  • First name
  • Username
  • Password
  • Barcode number
  • Access Level (make sure you select the one you just made)

Putting it together

On the Chromebook, we navigated to Destiny and had the librarian log in as our Sample Student. You should see that there are no options to search, check in or do anything else on Destiny except checkout books – perfect!

Now that all the hardware, access level and user are in in place here is what it looks like. I apologize for the mess, this was taken on a Friday afternoon before Spring Break – so a lot of students had returned books.

On Chrome, I zoomed into 150% so it is a little easier for anyone to see what has been checked out.

How it works

Here is how it all works together. A student has their library stick with their barcode printed on it. They pick out the book(s) they want and bring it to the self-checkout station. They scan their library stick which will bring up their account. Then they scan their books one at a time.

When they finish, they then use the trackpad on the Chromebook to click the Reset button. This resets the system and gets it ready for the next student to checkout.

If the system is inactive for 120 seconds it will automatically reset itself and after 240 minutes it will need to be re-logged in.

Details to know

We just set this up but have already had first graders use it and it went pretty well. If a student checks out too many books the system will require for an administrator to put in their username and password to accept it. If not, it will not check out that final book(s) they want.

Putting the self-checkout screen in full screen mode is also highly recommended. This will hide the address bar, bookmarks and any other tabs that may be open.

That reset button is the only real sticky point I can see. Signage is one time tutorial with the students is highly recommended but I figure after one (maybe two visits) students will be good to go. We tried it yesterday with first grade students and didn’t have too many problems!

There should be a librarian, teacher or aid nearby most times just in case.

New Google Docs, Sheets or Presentation? Just type “New”

Well, sort of.

You see if you wanted to create a new document you could that out of Google Drive or go to Google’s respective Docs, Slides or Sheets website.

Those individual websites seem a little silly to me but hey, I just chose to only use Drive.

Now if you want to create a new document, Google has made it a bit easier. First, make sure you are signed into Google and then type one of the following URLs.
docs.new
sheets.new
presentation.net

BAM – you are ready to start creating and sharing.

Start of the year – What’s on your to-do list?

It’s been a spell since I’ve sat down and wrote a post, but now that the school year is officially going, I thought it was time to brush the dust off the keyboard and jump back in.

Oh yeah – you can expect some podcasts flying your way in September so stay tuned and subscribe to us 🙂

Nuts and bolts

This goes without saying and is pretty common in all schools I’ve ever worked at or spoke to. I refer to this as fine tuning the school year. Schedules are all set up, report card templates have been carried over from the previous school year and student and email groups are all created. Now it is time to fine tune all of this. Whose schedule has changed, updating email groups, helping teachers with their tech and their classroom pages and websites. It is one of those aspects that is done when it is done, but it makes all the difference moving forward.

New stuff

The beginning of the year is not a bad time to try out some new ideas. The staff is pretty focused on setting up their procedures and classrooms. This goes for divisions as well. More often then not a teacher or principal will have a strong idea of what they want; however if you can throw in something new that either enhances or replaces an older process you will find people very receptive.

I tried this with middle school students requesting electives. Normally it was done through a Google Form and then the students were manually placed in a class. This year, we tried it through our school information system. There was some fine tuning and some small obstacles to overcome, but the administration was willing to give it a go.

They would know really quickly if it was going to work or not. If it didn’t, no big deal. It’s the start of the school year, electives don’t start right away with our schedule so if it didn’t work we still had time to do it the Google way. Just for the record, it did work.

Reflecting

The start of the school year is not a two-three week process for the IT department. It often starts in early July for us and ramps up week by week until school starts and then carries on a good two weeks after that. For us at least, that accounts for six weeks of work.

I am a big believer of looking back at big projects and figuring out what could we have done better, what really worked and what was a grind. These discussions usually bring everyone in on the same page and allow people to share successes and failures in a safe way.

Looking to the future

The big one here is budgeting for the next school year. Don’t wait on this one and start thinking and discussing it now. If you know you have a big project then start getting quotes, prices, time tables and contingencies in place. I’ve learned this from experience. I’ve wanted to do a project but only did some basic web searches and then budget time came due and I just put up a number. Well, the work needed more time and more money in order to do it correctly. That was bad news. Luckily we were able to make it work, but it is not very professional and if it is a trend.

Most budgets seem to be due in fall or early winter so don’t wait and be sure to talk to your admin team in the building to make sure they don’t have anything they need to add. Communicate now so you don’t have to explain why someone cannot have something in the future.

Wiggle room

This is very important and something I learned even before being in education. Make sure you have some wiggle room. Don’t plan projects back to back with no time in between. Make sure you have space and some give to your planning for the inevitable, unforeseen disaster or issue. It happens and make sure you and your team are able to put certain projects on hold and re-prioritize when the need arises. If not you will find unhappy people all around you.

Google Forms and Sheets – Olympic Scoring – Reflection

Alright! The day of the big event has happened and here is how it shook out. Overall the day was great due to the awesome planning from the PE department. So the day went well but how did the Google Form and Google Sheet hold up? In short it did fine but there are still some issues so let’s get into it.

The set up

We do not have WiFi outside at my school. Originally I envisioned that teachers who were manning the station would open up the form on their phone and enter the results. The organizers weren’t thrilled with this idea. First, we are asking teachers to use their personal phone and their own data which is admirable but maybe not the fairest thing to do. Then there is the possibility that their phone could be damaged and well … that is not a fun scenario. Then there is the possibility that the teacher will enter the information incorrectly causing problems.

So we have the organizers putting inputting all the results. The people running the event scored everything on a score sheet and then turned it in at the end. The problem is that the form was meant for individuals to input the results for a single team per event. For one person inputting all these at once is a problem. It just takes too long. It got done but it was a bit of a rush.

In the future I will make the Google Form so the organizer can input all results for an event at one time. The spreadsheet part will not change too much. The math will still work the same way.

Damn ties

I mentioned before that ties are a problem with the spreadsheet. The vlookup function does handle ties well. Check out the image below to see what I mean.

Now the problem here isn’t that Denmark got all of those points because they did not. Check out the image below for proof.

The problem is the other teams get the shaft a little. Even though the four other teams did just as well as Denmark they receive progressively less points.

I need to figure this out and if you have any suggestions please leave them in the comments below.

Overall

This is a fun event and really the winner(s) is not the most important thing. We have two weeks until school is out and everyone is stressed and this is a great way to help get some of that energy out and forget about your worries for a day.

The Google Form and the Google Sheet did hold up very well. It seemed to have no issues recording and then handling the data. Overall, it worked and worked fairly well. I just need to tweak it and try to make it better.

The good news is I have 12 months to work on it 🙂

Google Forms & Sheets – Olympic Scoring – Part 5

It has been a long road and we are finally at the finish line. This post will talk about how to total everything so you can see who wins without calculating anything! We will also talk about limitations to this form and how it is not perfect. If you are new to this series please check the first 4 parts.

Now open up your worksheet and let’s get into the fun!

Make a new worksheet

We are going to make a new worksheet. To do this click the )+ button in the bottom left hand corner of your Google Sheet.

It will make a new tab and a blank worksheet. If the tab is not where you would like it to be simply click and drag it to the desired location. I am naming this worksheet totals

The first thing I will do is set up the worksheet. So there will be no functions (yet) just typing some text into some cells.

Now I left Column D empty for aesthetic reasons – no other reasons. The other columns are set up for a specific reasons which we will soon see.

Referencing the points

In column E we will be referencing the total points. You could just type them in, but I like referencing them back to their original cell. Here is how we do that.

  • Type in the = sign
  • Click to the Team 1 worksheet
  • Click on the link with their total points
  • Then hit the Enter key

Check out the video below.

Named range

Now that we have all the teams and their total points we need to name a range. I will highlight from E2 to F5. Then I will select Data from the menu bar and click on Named ranges…

I will call this data totalpts. Remember when naming data you cannot use spaces.

Large function

Now that we have that done it is time to start using some functions to rank the teams. In cell B2 we will be using the large function. This does the opposite of the small function we used in Part 2. So basically this will show us the largest value in a set of data. Here is what we type in cell B2:

=large(totalpts,1)

  • The totalpts is the set of data we just named
  • The 1 means to show the largest of the set of data

Now we will repeat this function for cell B3 and type this:

=large(totalpts,2)

And so on all the way dow to cell B5. When you’re done here is what you should have.

vlookup

We are nearly done. Now we will utilize the vlookup function again and we will be using this in column C. What this will do is to look at the number in column B and then match that number up with the team name.

In cell C2 here is what we will type:
=vlookup(B2,totalpts,2,0)

  • B2 is the value vlookup is looking for
  • totalpts is the set of data where vlookup is looking
  • 2 refers to the second column of the data, in this case column F
  • 0 Means we want an exact match.

In cell C3 here is what I need to type:

=vlookup(B3,totalpts,2,0)

Here is what mine looks like.

Uh? Patrick? What happened to second and third place?

OK, here is the time we will talk about imperfections. Obviously ties are not handled very well with the vlookup function. I have no idea how to solve this simply (or even complexly). If you are well versed with spreadsheets please leave solutions below! 🙂

Ties are a problem with this set up that is obvious.

Another issue here is testing the form. You could build this entire spreadsheet, all the worksheets without any data. It is possible, but I prefer to add a bunch of bogus data, build the form so I can see that it is all working properly and then wipe the data out. Not ideal but you want to make sure your hardwork has been done correctly and that once real data gets in there you know it will be handled properly.

Well that is the scoreboard in all its glory. If you have ideas on where it can be improved or what can be added let me know by adding in the comments below!

Google Forms & Sheets – Olympic Scoring – Part 4

Please check out Part 1, Part 2 and Part 3 to get caught up. If you are just jumping in now and have little spreadsheet experience you may feel a little lost.

Last post we made all the worksheets for all the eventa. In this post we are going to make worksheets for each country or team. Lucky for us, there is a lot less “moving parts” here. These sheets will simply compile all the results for all the events for each team and then total those points up.

Named Ranges

Before we start making worksheets for each team or country we need to name some ranges and we will do that back in the event worksheets. So I will open up my spreadsheet and go to the Event 1 worksheet.

I am not going to add or change anything. I am simply going to highlight some data and then give it a name. So on the Event 1 worksheet I will highlight G2 to I5.

Now I will click on Data from the top menu and select Named ranges….

Now I will call these range event1fnl which stands for Event 1 final but you can call it whatever you want just remember to have no spaces.

Now I will do it again for the Event 2, Event 3 and Event 4 worksheets.

Make a new worksheet

Just like in Part 3, we will make one worksheet and then duplicate it for the other teams making the amount of work we have to do a lot less.

To make a new worksheet click on the + icon in the bottom left hand corner.

This will create a new sheet and to rename it just double click the tab.

Now we are going to simply type in a bunch of information. No functions yet. So here is how I set up my sheet.

Again, I just typed this information directly into each cell. Now we are ready for some functions. We are only going to use two today: vlookup and sum.

In cell B3 of the Team 1 worksheet we will type this function:

  • =vlookup(A1,event1fnl,3,0)
  • Here is what is happening in this function.
  • vlookup(A1 is looking into cell A1 (which is the team name)
  • event1fnl is where it will look for the team name
  • 3 means it will look in the third column of the event1fnl data which is how many points were awarded and display that in the cell.
  • 0 means that we want an exact match.

So now in cell B4 we will write this formula:
=vlookup(A1,event2fnl,3,0)

We use event2fnl to show that we want data from Event 2

Then we go on down the list and this is what it should look like.

Believe it or not we are almost done with this post!

sum

No we want to add all those points up. We only have 4 teams and 4 events but imagine you have 7 teams and 14 events! Calculating the totals can take a lot of time. So we will let Google Sheets do the heavy lifting.

In cell E1 I am going to write this formula – it’s very simple.
=sum(B3+B4+B5+B6)

Now there is another, shorter way to type this formula which is:
=sum(B3:B6)

It does the exact same thing. Some people like the longer way that way they can see exactly what it looks like, but it is totally up to you.

Guess what my friendly reader, we are done with this worksheet!

Duplicate

Now we just need to duplicate for each team and then change data in one cell per worksheet. To do this go down to the Team 1 tab at the bottom of the worksheet. Right click the Team 1 tab and then select Duplicate.

Now rename that worksheet to Team 2.

Now you have to make one change, just one and it is easy. In cell A1 on the Team 2 worksheet is the name of the team. Change it from Team 1 to Team 2. That’s it! That’s all that needs to happen and the rest of the worksheet should update.

Now go ahead and duplicate that sheet for teams 3 and 4 and make that one change and you’re done!

In Part 5 we will work with the Totals Worksheet where it will collect the total points for each team and rank them.

Google Forms & Sheets – Olympic Scoring – Part 3

*NOTE: I ORIGINALLY PUBLISHED THIS ON MAY3 BUT THERE WAS A MISTAKE WITH BASIC ORGANIZATION OF THE WORKSHEET/ I HAVE CORRECTED THIS AND SINCE UPDATED IT TO WORK AS PLANNED.

The journey continues and this marks the hump point of this series. Just a quick recap. In Part 1 I wrote about how I was using Google Forms and Sheets to create an auto calculating scorebook and the services and skills I used. In Part 2 I talked about how I created the Google Form and got it to do what I needed it to do. We also had Google set up a spreadsheet with our data from the form as well.

Here we are in Part 3. Here we are going to set up specific worksheets for each event. Before we start adding functions to a worksheet let’s take a look at the data we have. I went ahead and filled out the form. I have 4 teams and 4 events.

That may be a little small so click this link to get a view only peak at the data. Again, this was all organized by Google automatically. The data is nice, it is organized and it makes perfect sense. What we are going to do now is create additional worksheets in this file to help us filter the data and allow us to organize it for each specific event.

Just a note. If you get stuck and frustrated don’t worry – especially if you have never done any work in a spreadsheet. Stick with it, be patient with yourself and reach out if you need any assistance. Learning something new isn’t always easy.

OK, now we’re ready.

Naming that data

From the Form Responses 1 worksheet we are going to name all this data. This makes it easier to reference it back when using functions in other worksheets. To do this click the empty box below the fx symbol near the top left hand side of the screen. This will select all the data.

Now we need to name it. To do this select Data from the top most menu bar. Then select Named ranges…

Now give it a very simple name with no spaces. Spaces apparently will break it. I also like to keep it lower case. I don’t know why, maybe it is just me but I always make my named ranges lower case.

I called this data olympic. It is simple, easy to remember and hard to misspell.

Create a new worksheet

This might sound like we are going to create a whole new file but no. Spreadsheets have the ability to add different worksheets. Not being a spreadsheet master I feel this is a way to reference other data from other worksheets while at the same time filtering or showing specific data in a particular context.

Doing this is super easy. Open up your spreadsheet. You can get to it through the Google Form Responses page by clicking on the Google Sheets icon (see Part 2 of this series for a screenshot). Then click on the plus button down in the bottom left hand corner of your screen.

If all goes well you should see this! You can rename any sheet by double clicking on its name in the tab. I am calling mine Event 1.

Now to go to that sheet (if it didn’t do it automatically) by clicking on the tab, like a browser tab. You will see a blank worksheet but that will change shortly. You will notice that the columns are labeled as letters and the rows are all labeled as numbers. This is used so we can reference specific cells known as cell reference.

We will start our journey in cell B1. Here we will add a Query function.

Query

The Query function will allow you to pull specific data from the From Responses 1 worksheet. There is a reason we are putting it in cell B1 and not A1 but that will become clear later on. So we only want the Event 1 data in this worksheet.

Here is what you will need to type in cell B1:

=query(olympic,"SELECT B,C,D,E WHERE B='Event 1'",0)

Let’s break down this formula so you can understand what is happening.

  • The = sign is what is needed to start all functions. This lets the spreadsheet know that it is performing an action and not just holding data.
  • The word olympic refers back to all the data on the Form Responses 1 sheet.
  • The comma breaks the function into a different part.
  • Select B,C,D,E tells us that we are only going to display the data in these columns.
  • WHERE B=’Event 1 tells us that column B must have those exact words and based on that will show the information in those rows corresponding to columns B,C,D,E

When all is done here are the results!

Total seconds

I am sure there is a function that will sort based on two columns but I do not know it. However, I do know how to sort values in one column. So what we are going to do is convert the minutes and seconds to seconds in Column A.

To do this I typed in this function in A1:

=D6*60+E6

What this does it take the value in cell D6 (the number of minutes) and multiplies it to 60 (converting minutes to seconds) and then adds the value in E6 (the number of seconds) to that total.

Now copy that formula for the other teams. Be sure to change D6 and E6 to D7 and E7 and so on and so on.

Here is what you should have now.

Ranking with the small function

Now we need to rank them or put them in order from fastest to slowest. I am sure there is a way to do this more efficiently but as I have mentioned earlier, I just don’t know how and so I am working with what I know and what I can do.

First I want to highlight the total seconds column and name that range, I don’t want all the data in this worksheet just the total seconds in Column A so that is all I highlight. I called this data event1sec.

Then in Column G (I want to leave column F alone just for aesthetics) I will type this formula:

I’ve made a mistake – we need to put this in column H1 not G1. I’ve updated the post and pictures to reflect this change. Everything up to this point is correct.

=small(event1sec,1)

  • The small function returns the smallest value in a set of data.
  • event1sec is the data it is looking a.
  • 1 this means I want the smallest set of data

So for the next row down here is what you would type:
=small(event1sec,2)

The 2 means it wants the second smallest number in that data set.

So this is what your spreadsheet should look like now.

 

vlookup

We are nearly done!

In column I, I will simply add the scores. I just type in the numbers into the cell manually. There is a way to write an if statement to get this automatically but this seems a little simpler for me.
H1 = 4
H2 = 3
H3 = 2
H4 = 1

Now that we have the numbers ranked you could look back and match up the numbers mentally but it would be best if the country is next to its score. We will be using the vlookup function here and we will be putting that function in cell G1.

Before we add the vlookup we need to name another range. Here I will be highlighting A1 to C4.

I will name this range event1pts. The process is the same as before.

Now let’s add the vlookup function in cell G1 (as I mentioned before).

=vlookup(H1,event1pts,3,0)

Now here is what is happening with this formula.

  • H1 is the number it is looking up.
  • event1pts is the data that vlookup is looking at.
  • 3 is looking at the third column (so in this case column C of that data set) and that is what is will be shown depending on the value in G1
  • 0 means that it wants an exact match

Here is what that should look like.

Now for the next row (G2) you will want to type this function:

=vlookup(H2,event1pts,3,0)

Then use G3 and finally G4.

Now I went ahead and inserted a row at the top and here is the final worksheet.

Duplicate and update

This is the last step and it is pretty easy. We can duplicate this worksheet and only change one function to make it all work for the different events!

Here we go, in the worksheet tab at the very bottom left hand corner of your screen right click the tab for Event 1.

You should see a menu appear. From here select Duplicate.

A new tab will appear and it will contain exactly the same data as the Event 1 worksheet.

From here I will rename the Copy of Event 1 tab to Event 2.

Now I will go to the Event 2 worksheet and find my Query function. It should be in cell B2.

In this function I will change B,C,D,E to B,G,H,I. B stays the same because that column holds the which event it is in the Form Responses 1 worksheet.

Then I will change WHERE B=’Event 1’ to WHERE B=’Event 2’.

Here is an image of the new formula.

=query(olympics,"SELECT B,F,G,H WHERE B='Event 2'",0)

And magically the data should update! Awesome.

Now let’s do it for Event 3 and 4.

Duplicate Event 2
Rename it to Event 3
Change the query function to this:
=query(olympics,"SELECT B,I,J,K WHERE B='Event 3'",0)

Duplicate Event 3
Rename it to Event 4
Change the query function to this:
=query(olympics,"SELECT B,L,M,N WHERE B='Event 4'",0)

Parting notes

If you’ve been able to follow along and all of your sheets look great well done. If not keep at it. This is a lot to take in and again, if you’ve never dabbled into spreadsheets this could take a while to wrap your head around. I’ve tried to write this post in a way that I feel most people can follow but again, it is almost 2000 words and there are a lot of steps. If there is something you don’t quite get please email me at: patrickcauley@gmail.com and I’ll do my best to help.

Part 4 – Setting up the team worksheets coming soon!

Google Forms & Sheets – Olympic Scoring – Part 2

In Part 1 we talked about the broad strokes of creating a scoring system for my school’s Olympics games. We talked about the services to utilize, how it was basically set up and what skills I used to make everything talk together to accomplish our goal.

This post is all about Google Forms. This is the easiest part of the whole shebang. We willl make a form that lets you pick an event and based on that choice will take you to the event to enter their score or time.

You just need to think about who needs access to the form, how easy will it be for them to fill it out and all that good stuff. When I talked to the organizer of the event he said that he was going to be the only one using the form. This makes a lot of sense for a couple of reasons.

  1. All the events are outside and our campus does not have outdoor WiFi set up. Staff may not want to use their personal data for a school event.
  2. Since he will be the only one entering the form, this is smart as it will cut down on mistakes or duplicate entries.
  3. Some of our events deal with water and some staff may feel a little uncomfortable using their smartphone where there is a chance it could get doused.

So he and the IT team will be the only people with access to the form and the spreadsheet. Again, the fewer people who have access the better. Less chance of any screw ups or unwanted changes to the form or accidental deletion of formulas.

Making the Form

Obviously having a Google account is necessary here. Your school does not need to be a G Suite school but it does help when it comes to sharing it with other staff members.

To start a new form head on over to https://forms.google.com and sign in. Once there click on the plus symbol in the bottom right hand corner to create a new Google Form.

Once you get in, you will find it is pretty straightforward.

I suggest making all your questions either multiple choice or from a dropdown menu.

DO NOT LET THEM TYPE IN THE EVENT OR COUNTRY

Allowing people to do this will break your form GUARANTEED! People will accidentally misspell the country, abbreviate it or not type the whole name. All of these variations will make tracking the data impossible.

By making all questions multiple choices you eliminate this nightmare situation. They must select a choice provided. This will make all the Google Sheet work possible.

Sections

Rather than have all the questions on one page I decided to make a section for each event. A section is probably what you would imagine. It is a separate section that holds only certain questions. That way the organizer doesn’t have to scroll and scroll and scroll to find the event he needs to enter.

Instead, he clicks on the event (which is question 1) then the form goes to that section for that question. This is known as branching.

So here is what my form looks like right now.

Now I want to make a new section. This is very easy. Just click the new section icon on the toolbar next to the question editor.

Now that the section has been created here is what it will look like.

Response validation

Now I want the person entering the form to enter a number for the event. I want that to be an integer (not words) and I want some limitations. I don’t want someone to enter in a negative number or say 3 minutes and 62 seconds. 😦 That just doesn’t work for me.

Luckily Google Forms has something called Response Validation. This can let you put some restrictions on what is inputted into the fields.

Google Forms is pretty smart. For me it automatically knew I wanted a number and went ahead and added the data validation, but if it doesn’t here is how you enable it. Click the three buttons icon in the bottom right hand corner of the question.

Then select Response validation.

A new area will be added to the bottom of your question.

Now that it is there I can set some restrictions. For minutes I will make it a number between 1–30 (I know that is the limit for each event).

Where it says Number and Between – those are drop down menus and can be changed.

Now I will add another question to ask for how many seconds it took.

OK that section is done. If you have only races (most of our events are races) then you can just duplicate each the section to how many events you have. Doing this is very easy just go to the top of that particular section, click the three dots and select Duplicate section option

Branching

This is very easy. Now that the sections have been duplicated it is time to do the branching. Basically in section one, depending on what event the person filling out the form answers, we want them to go to that section and skip all the other sections. It sounds complicated but is super simple.

Let’s go to the very top in section 1. Now to enable this feature click the three dots in bottom right hand corner of that section. Then select Go to section based on answer.

Now the multiple choice question will change a little. It will add drop down next to each question. This is a drop down menu and you simply select the section you want it to go to.

Now go to each section and at the very bottom change it to Submit form. This will make sure that the form submits after that section is complete and you can start the process over again.

Make a spreadsheet

Last step. Click on Responses tab at the very top of the Google Form. Then click on the Google Sheet icon. This will create a Google Sheet where all the data is stored.

That’s it. It seems like a lot of work but it is really not too difficult especially if you have experience with Google Forms.

Next post will be Part 3 – Google Sheets – Setting up the event

Google Forms & Sheets – Olympic Scoring – Part 1

What is with me and “multiple part” posts?

OK and just a fair warning this series is going to get a little technical with formulas and linking multiple worksheets together to get a scoreboard of sorts for an “Olympic” event our school is putting on. I’ll do my best to break this down and try to help you connect the dots but if you need some help reach out to me and I will be happy to assist

This first post is just the background info and some basics that you should know or need to learn about Google Forms & Sheets before going forward. so let’s dig in.

Olympics

A lot of schools do this and call it different names. I’ve heard it called field day, activity day, royal rumble and so on. Basically it is a day where the school breaks into teams and performs events, either head to head or individually and then at the end of the day a winner is awarded based on their performance.

So this part in itself is busy enough and has a lot of moving pieces and it takes a certain person with good organizational and leadership skills to pull this off. My hat is off to those people.

At the beginning of the year those people came to the IT department and asked for a way to automatically calculate points for each event. What was happening was that calculating and organizing the results was taking too long at the end of the day, and, well, when you have an event like this, 30 minutes of downtime can spiral out of control real quick, so they needed a better solution and we have created that.

Our set up is pretty simple. We have 15 countries (teams) and 15 events. That means that each team will do each event individually. So we need to be able to capture those results and have a real time calculation of these events. The team that performs best at an event is awarded 15 points. Likewise the team that performs the worst is awarded 1 point.

Google Forms

The solution is Google Forms. I’ve made a very simple Google Form. The first page asks what event is being scored.

Then the second page asks for the country (team) name and how they performed.

 

We will get into making the form itself on the next post but even if you have never used Google Forms before then you should have no problem figuring this out. It is pretty simple.

For those who do not know, the results of a Google Form can be saved onto a Google Sheet. Google Sheets is a spreadsheet program (like Excel). Here is my example I worked on to give you an idea of what a spreadsheet looks like.

Google Sheets

This is where the real magic happens. To put it simply all the results from the Google Form are saved on a Google Sheet. All the heavy lifting is done here through a variety of different Google Sheets functions.

So I have four different types of worksheets. A worksheet is a different spreadsheet within the same file. So in the picture below you can see seven of the worksheets in this file.

Here are the four different worksheet types I use.
1. Responses – This one worksheet is where all the form submissions are saved. They are sorted by time but really you will not be touching this data much at all.
2. Event types – I made a worksheet for each event. This data is fed from the Responses worksheet. So in total I made 15 total events but really it is a lot of copying so not a bunch of work. Each event will house all the results for each country in that event
3. Country totals – I made a worksheet for each team. This will house (you probably guessed it) all the results for that particular country. Again, I made 15 worksheets of this type but again, it was a lot of duplication so not a bunch of work
4. Totals – This last type is just one sheet. It shows all the point totals for each team and then ranks them.

Skills

  • When researching this I saw a number of examples but many had people inputting their own scores directly into a spreadsheet which helps a little but was not enough of a time saver for us. I saw others that wrote their script for Google Sheets. This is a bit beyond me at the moment so I decided to try and build my own (which I did) and here are the skills I used to build it.
  • Google Forms
    • How to create a form that “branches.” This is very basic and took very little time to learn (15 minutes)
  • Google Sheets Functions – Functions are commands that tell the sheet to do various tasks like add a column of numbers, look up other cells and report them back into another cell and so on.
    • Named ranges – This is very simple. You select a range of cells and then you can name that range. So when you want the data from cell A1 to cell D30, you can highlight all that data and name it. Then you no longer need to type out that range over and over again. This is especially helpful when working with multiple sheets (15–30 minutes)
    • Query – This pulls and filters data from the Responses worksheet onto a new worksheet. It is one function per sheet and updates automatically when a form is submitted. This is a little tricky but definitely learnable to someone with little spreadsheet experience (1–2 hours).
    • Vlookup – I learned Vlookup when teaching Excel. It basically looks for a reference number in one column and then reports back a value in a different cell. Confused – so was I. This takes a good a little while to learn if you’ve never done it before (1–1.5 hours)
    • Small and Large – These functions lets you “order” by smallest or the largest. It is very easy to learn (30 minutes).
    • Sum – Super duper simple. This will add the numbers up in a variety of cells automatically. It is usually one of the easiest and first functions most people learn.

Now take these times with a grain of salt. You may be pretty good with Sheets and these times may be 0 minutes for you. Also understand I am not an expert here. I had some basic knowledge, a goal and determination and I was able to learn how to create this. I am sure there are more efficient and better ways to do this and I look forward to learning those.

Part 2 of this post will be setting up the Google Form. It’ll be out tomorrow or Wednesday. So look out for that.