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.

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?”

students?

” What is the average for the 10th gradres

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 =average Finding the average of a range.e.g. Find the average for all of your students =average(range you want to average) =AVERAGE(G:G) =averageif Find 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) =averageifs Find 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.