I am often shocked and amazed at the prices people are paying for subscriptions/services. Believe it or not, schools pay different prices for the same products. There is usually a deal to be made, and here are a few simple tips to make certain you are getting the best deals out there.
Make some online connections who also work at schools. Setup a shared spreadsheet. Have people add the products they use. Do not ask them to list prices, many terms of service prohibit publishing that type of data. When you see people using the same solutions that your school uses, privately confirm what pricing they are getting.
Dealing with sales people is easier if you already know the answer to the questions.
Ask for MultiYear Deals
Any service or subscription that your institution considers a core solution should not be on an annual renewal. Not only are you wasting time and paperwork, but you are wasting money.
Ask for pricing for one year, three years, and five years. Look at the terms of payment and cancellation. It is often very surprising what the final cost is compared to the simple annual renewal. I usually look at three year deals as they are usually more flexible.
Find Competition, and Make it Known
Regardless of how much adoration there is for a service, remember, business is business. Services close down and sell-out all the time. When a company sells-out, they do not call your school to ask what you think. Companies are in business to stay in business.
Schools should always look for competition for products and services they are using. Schools should always have someone doing research and demos before renewals. Schools should not pay invoices because of an emotional connection.
It is an excellent idea to inform companies that you are looking for other solutions and doing due diligence.
Sales people know the game, and know who they are playing against. Most good sales people tend to know their competition’s pricing models and margins. Sales people will make better offers, package additional features, and push for better terms from their bosses when they know a competitor is involved.
Avoid the Shopping Cart
The listed website price is rarely the best deal. In fact, many good products require a quick form/survey to be completed before they issue a quote. These companies want to have the chance to offer not only the best price, but the best options; options that someone buying from an online shopping cart may skip.
I am not saying this is always the case, but I always contact the sales team to reconfirm the pricing, and deals.
Skip the cart, send a message first.
Need help or more information dealing with a vendor/service? Have a service you need to move away from, but you feel locked in?
Send me a personal email and we can review some additional strategies. (email@example.com)
*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.
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!
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:
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.
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.
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).
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:
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)
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: firstname.lastname@example.org and I’ll do my best to help.
Part 4 – Setting up the team worksheets coming soon!
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.
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.
Since he will be the only one entering the form, this is smart as it will cut down on mistakes or duplicate entries.
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.
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.
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
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
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.
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.
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.
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.
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.
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.
Earlier this week I wrote about viewpure.com and I am genuinely pretty impressed with what this service does. Above is a quick video showing it in action and how you can customize YouTube videos of your own choice to safely show to your students.
Let me lay out a scenario and see if you can relate to it. You have found a great YouTube video (like the one below :)). You want to show it to your class. It has a great explanation, it simplifies it and since it is on YouTube you can reference it over and over again so you can link to it in your LMS of choice or share it out another way.
The big day comes and man you are stoked. You bring up the link and … OMG! There are rude and inappropriate comments in the link below.
Note – these comments are not rude in anyway. I am just making a point.
Your students are snickering. You’re freaking out because you are afraid that these students will go home and tell their parents. Then you’re afraid those parents will tell your principal and before you know it you’ll be unemployed.
Well there is a solution out there friendly visitor. There is viewpure. This very simple website (no sign up needed or even AVAILABLE) will take the YouTube video you want and “Purify” it.
Just copy the URL (or web address) of the YouTube video.
Then paste it into viewpure’s box for the YouTube video like shown below.
Finally click the Purify button and whamo! The video with no salty or inappropriate comments.
Now that the video has been Purified you can actually take that link and share it out and that is the version of the video that students or parents will see.
Now this is pretty useful but there is much more you can do. If you click on the little gear at the bottom of the video you will see a number of other options.
You can give it a custom URL add a password to it and even trim it down. Let’s say you find an interview that is an hour long and you only need 5 minutes. viewpure will can do this as well! Very handy.
There is still one more trick that viewpure has to offer. You can actually search for other videos that have been Purified. Instead of putting in a YouTube URL just type the topic in the box instead and hit the Purify button.
You will get a bunch of results but please watch them before showing them to your class.
Recently I posted about Google Sheets and how awesome the query function is. I thought it was so cool that I would make a little video about how to do it, in case my instructions were not super clear. Watch it below and leave comments about how you would use the query function in your classroom or school.
A few weeks ago I stumbled upon something that has got my a little excited. I mean did you see the exclamation mark in the title? That is not to be trifled with people.
At any rate, I have stumbled upon the QUERY function from Google Sheets and man this is awesome! To be simply, this will allow you to have database like functionality to a Google Sheet. That may not mean too much to you, but it will.
At my school we have a list of approved parental chaperones by grade level. Basically these are family members. This list is made up of interested family members who have had a background check. No background check, means parents cannot attend. Simple as that.
Field trips are organized by the teachers in the classroom. Therefore, they are responsible for not just the students attending but making sure that parents who have been “approved” by the school can go. The teachers should be referring to a list and that is where the problem enters.
It was a mess (organizationally speaking). It was a Google spreadsheet but everything was entered manually. There were tabs for each grade level, but man it was a pain to update.
Here is what the IT team came up with. We will put all the data on one sheet. Then make a tab for each grade level. Finally on each tab we would use the QUERY function to auto populate the tab with the appropriate information for that grade level.
That way the original data didn’t need to be “organized.”
Make sense? It will. I’ve created a new project to show as an example. I have students in different grade levels attending workshops on different platforms. Worry not folks, these names came from a website that generates random names. Any coincidences that may have occurred are just that.
So here is my data.
Now what I want to do is separate it by workshop. That way I know exactly who is going to attend the Apple, the Google, the Linux and the Microsoft workshops. There are plenty of ways to do this using the vlookup function and so on, but this way is much faster and much simpler.
First, I will make tabs for each workshop.
Before I start the QUERY magic I will want to name the range of the data. On the tab that is called All Data I will select all of it by hitting CTRL+A (Windows) or by hitting CMD+A (Mac).
Now I will select Data –> Named Ranges… from the Google Sheets menu.
A new side window will appear asking you to name that information. I named mine workshop. You can name yours whatever you want, but when referencing back to this data the name must be perfect. If you capitalize the first letter, you have to capitalize the first letter in your QUERY.
Now in the Apple tab in cell A1 I will type this formula.
=query(workshop,“SELECT A,B,C WHERE C=’APPLE’”,1)
This is what should happen in that tab.
Now let’s take a look at that formula and break it down.
=query – This is you telling Google Sheets you will be using the Query function
(workshop – Remember when we selected all that data and named it workshop,? Well this is telling Google Sheets that we want to look at that pool of data. The comma is there to signify that we are ready to move onto the action.
Select A,B,C – This is here to say that of all that data we want to look at these three columns. If you only wanted a list of names you could but Select A and call it a day.
WHERE C=’APPLE’” – This tells the query to display only the information associated with the word Apple is found in column to C.
1 – The one at the end corresponds to how many header rows there are (I think – I’m still learning here too 🙂
It’s awesome! One function in one cell is populating the worksheet! It also updates dynamically so when you add another student to the worksheet it will automatically update the correct page with that information.
If you want to see my example click the Google Image below. It is only view only but you can make a copy and look at the formulas which are all in cell A1 of each respective cell.
Now, I did not just stumble upon this and figure it out all on my lonesome. Oh no, I had a little help from two very good websites that helped break this down for me.
One is from Ben Collins, a spreadsheet Developer and much more. Check out his excellent introduction to the query function on his website.
The other website is Coding is for Losers. This site has a tremendous treasure trove of resources and will take your spreadsheet to the next level! You can find the information on query right here. They also have great YouTube videos. Check out their channel here.
If you have any tips or tricks feel free to leave them in the comments!