ITBabble Virtual Learning Solution Webinar

I’ve developed a very flexible solution with iPads and some ergonomic tools/devices.   

The main goal was to have tech that was useful all the time, not just during quarantine, and tech that didn’t strain the network with video standards that can’t be handled by personal home networks. The investment would be useful for 3-7 years, or the duration of the equipment lifecycle. The tablet form factor I chose was the iPad, but this could be done with Android or Chromebook tablets.

This model eliminates document cameras, allows for hand writing on paper or real whiteboards, allows for digital whiteboards, and you can ergonomically adjust things so people feel like they are sitting next to someone. 

Teachers can freely move around the room to demonstrate labs and other experiences that are eliminated in most virtual scenarios. 

You can even do choir, band, and art. 

If teachers/hosts have laptops, this allows for  two cameras in every space. Students can flip between the iPad and the host device. 

The conferencing software doesn’t matter. You can use anything for your video conferencing. 

If people need to work from home they just take the iPad, and literally replicate their teaching environment.

This idea can be summed up in a single simple statement: The iPad is a Person in your Classroom.

If you would like to know more, please complete the form below.

https://forms.gle/5CwcQxSSd9vxmjiMA

cal.new = A new Google Calendar Event

Not too long ago, Google announced a bunch of really handy short URL’s that will let you create new types of files. I wrote about it here. For a quick refresher, here they are:

  • docs.new = New Google Doc
  • sheets.new = New Google Sheets
  • slides.new = New Google Slides

Now there is one more to add and if you read the title, this will be no surprise. If you type cal.new, it will create a new calendar event. It is super handy.

Of course you must be logged into a Google account for this to work. So, if you don’t use Google or your school uses Office 365 or some other system then you can forget you ever read this post.

For me this is a big convenience. Most of my calendar events are appointments or meetings with other people. When you create a new event the old fashioned way of actually going to the calendar and clicking on the day you want an event. Here is what you get.

 

 

 

 

 

 

 

 

I want more options than what is there. I just do. I like to add notes, link to other Google Docs that are necessary for the meeting or maybe just a joke to lighten the mood (my meetings can be unnecessarily serious).

So yeah, I like this feature.

Make your own flashcards in Google Sheets

OK – to be fair, you need Google Sheets and the Flippity add on, but still, this is pretty handy, easy to do and works really – really well. I’ve seen a couple of articles about this on the web but I’m going to go a little deeper and walk you through from start to finish.

In this example, we will be doing some language learning though I can think of this being used for math problems and vocabulary as well and much, much more. So let’s get started.

Google Translate in Sheets

The first thing we need to do is open Google Sheets and get ready for some magic! We will be translating words from English to Spanish. I will be using specific words and not phrases. As we all know translating longer pieces of text can sometimes lead to unexpected results 🙂

As you can see, I have 10 English words about the Spring season. I could look these up, but I will let Google Translate do it for me.

In cell B2 I will type this formula which will translate it from English to Spanish automatically.

=googletranslate(A2, “en”, “es”)

  • So let’s break this down. =googletranslate will let Google Sheets know that it needs to use Google Translate – pretty straight forward. Don’t forget the comma!
  • A2 tells Google Sheet where the word is that needs to translated.
  • “en” (yes you need the quotations) lets it know what language the original language is.
  • “es” (again don’t forget those quotes) let’s it know what it needs to translate it to. (ES = Spanish by the way)

So this is what it will look like.

Now all we need to is move your mouse to the bottom right hand corner of cell B2 (it should turn into a + sign) then click and drag down like in the GIF below.

BOOM!

Google Sheets (any spreadsheet program really) understands the pattern and automatically replicates the googletranslate formula all the way down properly changing the cells as needed.

Now we are ready to turn this into interactive flashcards

Flippity Add-on

The next thing we need to do is get the Flippity add-on. To do this open a blank Google Sheet and then click on Add-ons from the menu bar. Then select Get Add-ons.

A new window will pop up and from here search for and add Flippity to Google Sheets. You only need to do this once. After you’ve added it, every new Google Sheet you open will have the ability to utilize the Flippity add-on if you want it to.

To activate the Flippity add-on, click on Add-ons and select Flippity and select Pick a Template.

The template you will want to chose is, surprise – surprise, Flashcards. Go ahead and click Use.

Flippity will do some magic and then create a new worksheet with whole bunch of info that you do NOT want.

Do not be alarmed. This is merely an example of how and where to put your data. So go ahead delete all of their information and copy and paste your data. Your data will be on another sheet (probably called Sheet 1 like mine). You can find this at the bottom of the page.

You may also notice that you can customize the flashcard color and the text color. This is not necessary but a nice touch. Here is my finished flashcard spreadsheet.

The Flashcards!

To get to the flashcards, click on Add-ons, select Flippity and then select Flippity.net URL.

A pop up will appear with a web address. You can click that and it will take you to your very own custom made flashcards!

You can also check out my flashcards and get your Spanish learning on!

https://www.flippity.net/fc.asp?k=1OZQDpvkTNQYPHRGmplqIxI88WnGxgmHAC3kswYPPcL4

Helpful tips and parting thoughts

You can do this on Quizlet, but I find the translation part much faster with Google Sheets and with the URL you can easily share out your flashcards with multiple people and there is no need for an additional account (though you can sign into Quizlet with your Google ID) tp sign into.

All in all – this took me around 5 minutes to make and I think you can make this as large or as small as you would like – have fun with it! It could be a good activity for a class or a group of students to help study and quiz one another.

Flippity can also make some other cool things like a Jeopardy so be sure to explore and see what else you can do with Google Sheets and Flippity.

When it comes to Google Sheets, small mistakes in the formula can lead to big errors so it is always good to double check the translation and make sure that it is indeed correct.

Linking Google Slides

I had teacher walk into the IT office with a good question. She wanted to make a slideshow about a topic and have each student in her room create one slide to add to the slideshow. Here is what you need to know.

  • She is using Google Slides
  • This is 4th Grade
  • She didn’t want students modifying or messing around with other slides
  • 4th grades don’t always make “wise” choices

OK – now you are caught up and messing around here is what we stumbled upon. I don’t think this is a new feature but it was one that I was not aware of. Before we get to the solution here is what first sprung into our minds.

One Slideshow

She would have one slideshow on her Google account and she would share it with one or two at a time and then once their slide was done, she would take away their permissions and assign new students. Or she could just let them work on a computer that is signed into her account under her supervision as they directly added it with no sharing at all.

Obviously this is extremely cumbersome and not very manageable unless you were dealing with 4–6 students, so this would not work

Loads of slideshows and some manual labor

Each student would make their own and then they would share it with her. She would then cue up a bunch of different slide shows to show or she would re-create the slides on her own slideshow.

Again, lots of work and not very practical. I can hear you, the good reader, screaming at the screen now about copy and pasting.

The best solution

So the teacher make one slideshow to rule them all. Then each student makes their own and shares it with her. She will then copy and paste the slide(s) necessary from the student slideshow to the teacher slideshow. So check out the overly simplistic screenshots below.

Teacher Slideshow
Student #1 Slideshow
Student #2 Slideshow

So, what our teacher needs to do is copy the slide from the thumbnail view. Click the thumbnail and then copy it (ctrl+c or cmd+c or right click and select copy). Then once it is copied go to the teacher slideshow and paste it in the thumbnail area. When you do this, you will see an option to link the slide or not to link the slide.

I went ahead and chose to link the slides. When I do this this little icon shows up on that particular slide on the teacher slideshow.

Now here is why this is cool. Let’s say Student #1 decides to add a little more info. Maybe a picture in this case.

Student #1 Picture

Now let’s check back with the teacher’s slideshow and see what has changed.

As you can see – very little has changed, but when you look at Student #1’s slide you will see that it says UPDATE near the top right hand corner.

Update

So when the teacher clicks it the slide will update with the latest changes. Very, very cool.

Updated!

Now, let’s say a fourth grader shares this with their “best” friend who decides it would be hilarious to replace the computer picture with a funny picture and then the teacher hits update. All the teacher needs to do is hit Undo (ctrl+z or cmd+z or hit the Undo button) and the latest changes will be undone. Simple as that.

So, if you’re ever in this situation of making a single slideshow based and want the class to contribute then give this a go. It seems to work very well.

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.