Episode 155 – Gamechanger

Tony and Patrick are back in another awesome episode of IT Babble. The big news here is Amazon Workspaces and the game changing opportunities it offers education. Check out all the talking points below and be sure to subscribe to us on iTunes or your favorite podcasting app.

  1. In 2018, Windows died at home and nobody cared by Jason Perlow of ZDNET
    1. https://www.zdnet.com/article/in-2018-windows-died-at-home-and-nobody-cared/
      1. K-12 and Higher education still uses laptops/desktops
  2. Your Smartphone is the Best Computer You Own by David Pierce NO 🙂
      1. https://www.wsj.com/articles/your-phone-is-the-best-computer-you-ownso-use-it-more-1527084001
    1. Schools typically follow business
    2. Should schools ditch laptops for “mobile” devices
  3. iPads vs. Chromebooks: Part 2 by Patrick Cauley
    1. https://itbabble.com/2018/06/04/ipads-vs-chromebooks-part-2-2018/
    2. Chromebooks it is!
    3. Interesting results of the student survey
  4. The accidental BYOD solution by Tony DePrato
    1. https://itbabble.com/2018/06/06/the-accidental-byod-solution/
    2. Amazon Workspaces
    3. Amazon Education – https://www.amazon.com/gp/feature.html/?&docId=1000412651
    4. WINE for Ubuntu – https://wiki.winehq.org/Ubuntu

As always you can download the episode here

Or listen below.

 

 

Posted in Podcast | Tagged , , , | Leave a comment

The Accidental BYOD Solution

byod2

By: Tony DePrato | Follow me on Twitter @tdeprato

After reading Patrick’s recent post about iPads and Chromebooks, I decided to wrap-up an article that follows along those same lines.

The problem is, right now, (and how do I put this) our options for EdTech SUCK!

In 2008, I would have said Apple is the best solution for any school or family that could afford the platform. Then Apple started to change. I think it could be argued, they quietly have abandoned the education market.
rings

iPads are awful devices. Aside from oddly developed apps like Swift Playgrounds, iPad learning falls into two categories:

  1. Consumer Consumption
  2. Make it the way the App Says

There is no ability for students to go beyond the rules of the iPad, to change the rules of the iPad, or to create anything that was not predicted.

Microsoft has made amazing strides recently, and I do like their products. Not laptops running Windows. Specifically, I like Microsoft products such as the Surface.  However, the Surface products are too expensive, and there is still massive security issues involved in running Microsoft products. The Microsoft hardware does not reflect the actual cost of ownership, when much of that cost is used for defending the organizational ecosystem.

The rest of the market is too fragmented to build a stable platform. Unless a school directs students to only by a specific make a model every year (and every year it will change) there is no hope to establish a level playing field with BYOD students.

But. Maybe there is hope. An unplanned, and possibly accidental partnership. Chromebook + Amazon.

Google has been a big education player for some time. Overall, their services and branded hardware are dependable and flexible. The hardware changes often, but the Chrome OS is consistent.

Chrome OS is a solution for any school that has reliable internet access. Therefore, Chromebooks make a great hardware platform for such schools. Chromebooks have some reasonable opposition among many EdTech leaders:

  1. The platform cannot run powerful applications like Photoshop, Video Editing Packages, Etc.
  2. The platform is slow when working outside the core Google products
  3. Chromebooks have one official browser, and are not fully compatible with all websites/applications
  4. Although it is possible to code and create software on a Chromebook, the development options are lacking those of a traditional laptop (This is important for schools developing computer science and/or app development curricula.)

What if these four issues, were eliminated? Would the Chromebook be a better choice for most BYOD families or for schools buying hardware for students?

Enter Amazon Workspaces.

I tested Amazon Windows 10 Workspaces last year. I liked the experience, but had no reason to use the service. However, it occurred to me if Amazon Workspaces supported Chrome OS, then I could create a flexible platform for BYOD that used Chromebooks.

Guess what? There is a Workspaces Client and App for Chrome OS.

512nmkgumll

I have tested this platform for 6 weeks now using the new Samsung Chromebook and an Apple Laptop. I wanted to compare the performance of the Workspace’s Client service on two hardware platforms. Here is what I have found:

All four issues above were resolved. I even installed Photoshop and used it at the office.

Google + Amazon is a great concept for BYOD for education. The problem is, no one at Google or Amazon has realized it yet. This means the concept is not easy to implement at scale.

Although Chrome OS is free, Workspaces is not free. They do have a very affordable educational package. However, the entire process of getting signed-up, and calculating the price, is very convoluted. Amazon for Business is mature. Amazon for education seems like a discount coupon, not a well directed initiative.

The next issue is setting up management for the Workspaces. The cost of doing this at scale is currently not clear. The cost is clear online, but the actual bills do not match the flat rates. I constantly ask for my costs to be explained. I send scenarios to people at Amazon to get pricing, and then I wait for the bill. The bill never matches the predictions.

This is only part one of this research and possible new BYOD model. I am close to having what I would consider an affordable and reasonable deployment model for Workspaces with Chromebooks.

Keep in mind with Amazon you pay for what you use. Imagine having the ability to enable 60 Workspaces for one semester for students doing an Introduction to Graphic Design. Then paying only for a limited number of licenses for all the software. After the semester, students who are keen to grow and develop their skills retain access, those who want to move onto a new topic lose their access.

How many schools pay for a campus level license for Adobe Creative Cloud, yet only use a fraction of the licenses in any concurrent period?

How many schools give all students a license for Windows 10, just in case they take one or two courses where Windows is required for the curriculum?

If this concept can become reasonable and predictable, then we get much closer to the goal of being able to create equal access and opportunity without over burdening families and budgets.

Part two of this topic is pending until July, when I receive my next bill.

Posted in Uncategorized | 3 Comments

iPads vs Chromebooks: Part 2 – 2018

In the Part 1 I surveyed teacher to see how they feel about the 1:1 program. overall the teachers are still very enthusiastic about it, but when asked if the iPad was the best device based on what and how they teach many felt that it wasn’t. They thought that Chromebooks may be a better option.

So I sat down with the division heads (or principals if you like) and let them take a peek at the results. We discussed them and I got some of their opinions and then we talked about what the next step should be. I wanted to survey the parents and gather how they feel about this possible switch. The middle school division head spoke up and she thought that surveying the students may yield better results. I believe she is right. These students are the ones using the devices each and every day. The parents on the other hand most likely rarely know the devices as well as their children and probably (not in all cases of course) are unaware of the differences between an iPad, Chromebook, Windows S and so on.

Instead of surveying the entire middle school (5th – 8th grade). We decided on just the 8th graders. They are leaving us this year and had used their iPads for four years. Since they were leaving us we figured they would be far more honest and be able to draw upon their experiences. So here we go!

The survey

The survey itself has four sections of questions:
1) Section 1 – 1:1 program and the use of the iPad
2) Section 2 – Technology offerings
3) Section 3 – Student printing
4) Section 4 – In general

The results

We had nearly 80% of our 8th graders respond to the survey which I was very pleased with and their results were pretty enlightening as well. I will not go through each question and dissect the results but hit the highlights. For your information 1 = Yes and a 4 = No

The first question was about having the device.

As you can see just over half feel that a device to take home is important. Perhaps the indifference is due to teachers not leveraging the iPad or maybe that students wanted a different type of device.

Another highlight was about how well students felt they could research on an iPad. This was surprising. I did not expect this answer to be so positive. I really thought that a mobile browser would hinder or slow this down, but students did not seem to mind.

I did ask about typing on the iPad. As you might guess more than 70% of the students felt that the iPad was not easy to type on.

However, the next question did surprise. In fact of all the questions about the iPad this one made me really pause. I asked if they felt the iPad is good at taking photos and/or videos. Check out the results (remember 1 is very good and 4 is very bad).

Now here comes the million dollar question. If you were an incoming 5th grader which device would you prefer?

As you can see, Chromebooks and (WOW) Windows S machines make up 88% of the responses! Three people typed in MacBooks and one person (2.9%) voted for a new iPad.

That last bit of information coupled with the teacher results tell us that iPads aren’t really working for us. Who knows, iPads may be the best choice in a few years or even Windows S but right now it seems or teachers and students are of a similar mind and we will be exploring Chromebooks starting in the fall for our fifth graders.

Posted in chromebook, iPad | 2 Comments

Episode 154 – Poop on the Desk

Tony and Patrick are back and what an episode! Check out the talking points below and be sure to subscribe to us on iTunes or on your favorite podcasting app.

  1. The best gadgets are the ones you know how to use by Dieter Bohn
    1. https://www.theverge.com/2018/5/2/17302680/best-gadgets-tech-gear-liveblog-bag
    2. This applies to schools: hardware, software, SIS, gradebooks, etc.
    3. When is it good to make those changes?
  2. Access Denied: Controlling What Students Can Access by Tony DePrato
    1. https://itbabble.com/2018/05/05/controlling-what-students-can-access/
  3. iPads vs. Chromebooks update
    1. Student survey
  4. Tony’s testing with Amazon Work Spaces
    1. Tony tested Windows 10 with Amazon Work Spaces on a Chromebook
    2. It works
  5. End OF YEAR Process

You can download the episode here.

You can listen to it below.

Posted in Podcast | Tagged , , , , , | Leave a comment

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 🙂

Posted in Google Apps, Helpful Tips, Patrick Cauley | Tagged , , | Leave a comment

Episode 153 – Go for Gold

Tony and Patrick are back for another great post. We all wish Tony a quick recovery with his knee surgery. Check out the talking points below. As always please subscribe to us on iTunes or your favorite podcasting app.

  1. Tony’s knee surgery
    1. http://www.davincisurgery.com/da-vinci-surgery/da-vinci-surgical-system/
  2. Apple’s MacBook Pro recall
    1. Battery Recall
      1. https://www.apple.com/support/13inch-macbookpro-battery-replacement/
    2. Keyboard
      1. https://apple.slashdot.org/story/17/10/18/1530202/maybe-its-a-piece-of-dust
  3. Google Forms & Sheets – Olympic Scoring series – by Patrick Cauley
    1. https://itbabble.com/olympic-scoring-with-google-forms-sheets/
    2. Schools and spreadsheets – is this more important than coding?
      1. A good starting point for programming
      2. Spreadsheets have limits
      3. Office 365 – Sharepoint – https://products.office.com/en-us/sharepoint/sharepoint-online-collaboration-software
  4. How to Increase Pay – No More Summers Off! By Ronnie Burt
    1. https://www.theedublogger.com/2018/04/30/how-to-increase-teacher-pay-no-more-summers-off/
    2. Comments are good in the post
    3. Pay vs Working Conditions
    4. Is pay really the talking point or a distraction

Download this episode here!

Posted in Podcast | Tagged , , , , | Leave a comment

Controlling What Students Can Access

By: Tony DePrato | Follow me on Twitter @tdeprato

Recently I have been discussing multiple new security measures for academic networks. From these discussions with other schools, engineers, and suppliers, I have created set of goals to help keep the development of network security on track and within budget.

Physical Access

Physical access can be managed without a great deal of expense. The goals to reach for are:

  • We allow only the devices we have confirmed and labeled
  • We can control the number of concurrent devices a user is using on the network
  • We can identify by IP, Serial Number, or MAC Address (or a combination of the three) the owner of a device
  • We can remove a user from network access, and restrict their devices, with minimal effort
  • We have processes and procedures to register devices; users can switch devices through these processes
  • Users can only circumvent the processes by giving their login IDs, passwords, and hardware to another person

These goals do not imply the direct management of equipment; nor do they capture user data. These goals ensure that devices on the network are approved, registered, and can be clearly identified.

Achieving these goals is the first step towards the concept that accessing the network is a privilege not a right. Privileges can be revoked. If revocation is not possible, then the concept/policy cannot be enforced.

 

Read More @ The International Educator

Posted in Educational Technology, Instructional Technology, TIEONLINE, Tony DePrato | Tagged , , , , , , | 1 Comment

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!

Posted in Google Apps, Helpful Tips, Patrick Cauley | Tagged , , | Leave a comment

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.

Posted in Google Apps, Helpful Tips, Patrick Cauley | Tagged , , | 1 Comment

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!

Posted in Google Apps, Helpful Tips, Patrick Cauley, tutorial | Tagged , , | 2 Comments