Category: Google Sheets for Administrators

GOOGLE SHEETS FOR ADMINISTRATORS – =concatenate

GOOGLE SHEETS FOR ADMINISTRATORS – =concatenate

Today I wanted to share the first Google Sheet formula I learned when I first started out as an administrator. During my first summer as an administrator, I was tasked with creating student emails. After getting over the initial horror of having to use a spreadsheet, I opened up the template file and looked at the column headers. Most of the information I needed could be retrieved with a simple export from our SIS. The column that presented a challenge was the email address column.

At my organization, we use an email naming convention that combines the student’s initials and student id number. Like I said that information was simple to get. What wasn’t so simple was combining it all into the proper naming convention. That is when my coworker introduced me to the concatenate formula. This one simple formula speed up a task that would have probably taken me several hours and allowed me to finish it in about 10 mins.

In the video below I’ll show you how to use the concatenate formula. This is the formula you want to use any time you need to combine the data in two cells.

Google Sheets for Administrators – =Average, =Averageif, and = Averageifs

Google Sheets for Administrators – =Average, =Averageif, and = Averageifs

At this year’s CUE Spring Conference I will be presenting on how administrators can use Google Sheets to collect, organize, and share data with their school community. You can learn more about my presentation by clicking here. Over the next few weeks, I am going to be posting some of the resources and ideas that I will be sharing at the conference.

“What is your school’s average score on the SBAC?”

“What is the average score for your second grade
students?

” What is the average for the 10th gradres
in your second period?”

If you have ever been asked these questions than this blog post is for you. Using the =average formula and two variations of it you can easily find the data you are looking for.

The table below breaks down the different average formulas you can use to find averages, what each one is used for, shares the syntax for writing the formula and gives an example formula.

Formula
What is it used for?
Syntax
Example Formula
=averageFinding the average of a range.

e.g. Find the average for all of your students
=average(range you want to average)=AVERAGE(G:G)
=averageifFind the average of a range based on a certain criteria.

E.g. Find the average for your Period 1 students
=averageif(the range where your criteria is, “the criteria you are looking for”, the range you want to average)=AVERAGEIF(F:F, “2”,G:G)
=averageifsFind the average of a range based on multiple criteria.

E.g. Find the average for your 11th Grade Period 1 students
=averageifs(The range you want to average, the range where your first criteria is, “your first criteria”, the range where your second criteria is, “your second criteria”)

You can add more criteria to the formula by continuing the range where the criteria is and “criteria” pattern.
=AVERAGEIFS(G:G,E:E,”11″,F:F,”1″)

In the videos below I’ll show you how to write each of the three formulas and explain how you can use them as a school administrator or teacher.

=Average

=Averageif

=Averageifs

Google Sheets for Administrators – Copy To

Google Sheets for Administrators – Copy To

At this year’s CUE Spring Conference I will be presenting on how administrators can use Google Sheets to collect, organize, and share data with their school community. You can learn more about my presentation by clicking here. Over the next few weeks I am going to be posting some of the resources and ideas that I will be sharing at the conference.

Have you ever wished that you could quickly transfer data from one Google Sheet to another? Well in today’s resource I’ll show you how you can use a feature called Copy To to quickly copy the data from one sheet to another. This is a huge timesaver since you do not have to copy and past anything. Using Copy To also helps ensure that your data and formatting is not lost when you insert it into your new sheet.