Working with PE Data

In MYP, Math classes and the PE class worked together to make an IDU. They took data from some drills and health tests and put them in a table. 

-- we should start thinking more about how we plan these units and how we can get more teachers working together --

I am scheduled weekly to be with the math class, but when I was in the class, I was suddenly asked to help some students make a graph. 

I wish I had been able to be more a part of this unit. I could have shown teachers and students how to structure the data they were using to make it easier to make a graph. 

Chart Experiment - Retirement-Age Workers by U.S. State

I was pretty sure that I did this before, but looking back in my Google Drive, I don't see it. I thought that over the summer I had seen an article about states and their corresponding party affiliations and from that I made a chart. 

But I can't find it.

So, I grabbed data from 270towin for the political parties. 

Then I put it all into a Sheet and tried a few charts. I ended up deciding the bar chart showed it the best, although the radar chart was the most artistic.

So, then here's what 

This was still hard to gain evidence from, so I though maybe getting the sum would show me something. To me it looked like the blue states had a higher percentage. So, here are the SUM values.

Red 440.4

Purple 318.8

Blue 412.7

The count of each party are as follows:

D	16
P	14
R	20

The bar-thinckness couldn't be changed, and while I was trying different chart types, I tried the stacked bar chart. For some reason that made the bars wider.

Still, I am not thrilled with the lack of options for this type of data in Google Sheets.

The important things to remember with different series is how to structure the data. Each series needs to be in a different column.

Mapped: Retirement-Age Workers by U.S. State

Tracking Reading Growth with Line Graphs

In our PYP program, we use DIBELS to track student reading fluency. The first step is to create a table that includes the assessment dates and fluency scores, with a separate column for benchmark scores.

I conducted this experiment to see if I could create a graph that displays both fluency test results and benchmark scores on a single chart. By doing this, I aimed to visualize student progress more clearly.

In the table below, column A contains student names, and column B contains the assessment dates. I set it up this way so that I could easily use the FILTER function to sort or focus on specific students. The fluency scores and benchmark data are placed in separate columns.

To create a graph with two line charts, I ensured that the fluency and benchmark scores were in different columns. To make the lines continuous in the graph, I checked the "Plot null values" option. This step is crucial, as without it, the benchmark scores would only appear as isolated dots rather than a connected line.

AI used for editing.

Making an assessment accommodations schedule

At our school, like all schools, we have students with learning differences. When students qualify, the IB grants them testing accommodations. This could be someone to read the questions to them, the use of certain technology, but mostly it means these students are granted additional time. 

In order to be informed about upcoming assessments, we looked to using a Google Form. 

The initial version of the data from the Form was functional but hard to read. The results sheet was on a separate file and the data was hard to read. 

I did a few things that would help me if I were looking at it. I organized it by the date of the assessment to be first. Then I added the grade and class of the student. I needed to know when the assessment was and how long it was. I also removed tests that were over and ordered the tests in reverse chronological order - all to help me stay organized.

Here is what I came up with using a QUERY formula.

----------

=QUERY('IAA Requests'!A1:P, "SELECT D, B, C, G, E, A, I, J, K, L, F WHERE D >= DATE '"&TEXT(TODAY(),"yyyy-MM-dd")&"' ORDER BY D ASC")

----------

From here, after the Notes column is a column for Accommodations. Those are pulled from the student IEPs. It is just something that helps me.

My next step is to make one for the whole class. So the teacher says, "Grade 12 Physics is having an exam on Monday", give me the information of the assessment, and then the spreadsheet would show students and their testing accommodations using a FILTER formula.

Checking Historical Temperature with Google Sheets

I was back in Minnesota for the summer this year. The temperature really started to rise and I wondered if this was an anomaly, or if it was within the range of normal for July. 

Then I wondered if temperatures were increasing.

First I needed to get the data. A Google search for "Minnesota annual high temperature" led me to a site with the data I needed. <Example needed>

Here I could use the IMPORTHTML function to pull the data to the sheet.

There were actually two tables, so I needed to change the formula from 1 to 2. <Example needed>

After importing the data, I copied it and pasted the values only. Then I could change the dates to year only (file>format>number), and moved the date column so it was to the left.

From here, I was able to create a table. (file>insert>chart)

Then, I thought I wanted the annual minimum temperature, so I repeated the process.

My findings

The high temperature for the years has somewhat remained stable, while the low temperature is increasing. 

This isn't enough data to determine if temperatures are actually increasing, I'd need the complete daily temperature data for that ... it sounds like a cool project though!

Copy the file

Visualizing Student Support Data with Google Sheets

In the beginning

I joined the [formerly-called] SEN team last year. The team was using anecdotal records for their data collection. The issue was that it was kind of a black box -immediately I wondered how I would know if the strategies I was using with students in my care were helping them reach their goals.

Last year, I helped the team reimagine some ways to collect data on student behavior through the use of rubrics, checklists, and goal trackers. We also had teachers write anecdotal notes about students while observing their behavior.

This year, the school created a new position, inclusion support coordinator. The first thing she did when she came in was to ask how we could collect data from teachers more easily. I was elated to hear this and suggested we use a Google Form that could be easily bookmarked to the browser, so that's what we did. She pushed teachers to use it when they suspected academic or behavioral issues with students. We also did away with the former ways of collecting data to try to save teachers' time.

The form responses before visualization.

With the data collected from the Form, we started by filtering the responses into the different programs; PYP, MYP, and DP. This was good, but after a month, we could see that it was hard to read. I also started asking my coordinator and principals what kind of data they look at when viewing it, and I started thinking about how to take their feedback, what I knew about what I would want to see as a homeroom teacher, and I started thinking of how. I could make this all visual. 

In my head, we would see the current weeks' totals along with the top five students. I also had this idea of a student lookup in my head.

The idea I had brewing in my head was a Google Form that could be easily bookmarked. In my head, we would collate the responses, and these would become the updated form question responses.

I had this other idea that we would have a lookup of student with the goals and the support plan. 

My demo of a student lookup 

Over winter break, as I spent the days at my in-laws house, I got to work making a dashboard. 

For this dashboard, I wanted a few things, data visualization, simple, more data, and better visualizations - right now, there's a lot of text.

Going forward, this exercise should show that data needs to be consolidated and accessible. 

Here's what it looks like.

Then I broke it. I committed the cardinal sin of editing the response sheet. After editing the response sheet, it no longer connected with the dashboard I made. 

The good thing is that I have a better idea of what I want to do next year.

Using AI for Formula Heavy Lifting

This post was inspired by an article in Life Hacker.

At the beginning of this school year, I had an interest in spreadsheets. I was also considered one of the more knowledgeable people in my school about them. 

I was Googling how to do many of the more complicated formulas. I felt like I was moving too slowly and so I tried using AI to help me with those formulas I couldn't figure out.

I remember when the AI boom hit. After I "discovered" ChatGPT, I found a site that would help you with Excel formulas. That site started charging to use it, so I tried using AI.

ChatGPT is better at Google Sheets Formulas than Gemini

There are many AI tools to choose from. I when I first started making this dashboard I started using Google's AI tool, Bard. I liked it, so I tried it with Google Sheets. I thought that since it is also a Google product it would but it was shocking to me how poor it was at handling Google Sheets formulas and solving problems I had. 

I tried ChatGPT because Bard Gemini wasn't cutting it and I didn't look back. I think ChatGPT is simply better than Gemini. I have since tried it, but it's not there yet. 

I never did get around to trying that other AI spreadsheet tool.

Pro Tip - When using AI for spreadsheets, give it the actual range you are using so it can add it to the formula for you.

Know What You Should See

When I work with students in math classes, I often show them that if they can estimate the answer before they solve a problem, they can tell if the answer they got is "in the ballpark" or not based on their estimate.*  

As with anything you do with AI, you should have an idea of what you are looking for - or at least be able to recognize when the result is not what you want. At this point, AI is a thought partner. 

The best results come when I know what to look for in the answer. For example, I often question ChatGPT, or tell ChatGPT when the formula it gives me can be streamlined. Sometimes I even find redundancies in the formula or answer. 

When Caught in a Loop, Start Over

I said before, but I will say it again, when you use AI, you should check the results. AI can give some very inaccurate answers. It can also lead you into a feedback loop where the answer is the same again and again. When this happens, I start the query over and try to break the steps into the smallest chucks I can think of to give the AI some grounding when thinking how to solve the problem.

Over the last couple of months, I have learned a lot more about Google Sheets and visualizing data. I do think that AI helped me to learn faster than I would have learned had I simply been searching for answers one by one.

* I am well aware that sports analogies are an Americanism and I do try to not use them when explaining concepts to people. But, sometimes, it just fits.