Structure Data

In our newsletter last week, there was a blurb on leaving teachers. It said their name, role, and how long they had worked at the school.

I am interested in odd things, so I wondered what the average number of years teachers stayed at this school.

I think aside from the amount of leaving staff for the size of the school, this is pretty usual. 

Then I started to add staff who have left in the past. Then I was wondering about current staff and now this is turning into an interesting personal project about how to structure data. 

I wanted to see what the average tenure of the teaching staff was, but then I could start asking questions about the max and minimum stay of teachers. 

I don't have the data for the reasons why teachers came or left, but that would be good to collect. 

Currently, I have teachers who have left and current teachers on different sheets, but I think they should all go on a single sheet with a dropdown for those who have left and stayed.

Filter Multiple Google Forms to a Single Sheet - Update Main Sheet as "Done"

Part of my goal in this Geometry unit was to collect more data on the students and ease the teacher load at the same time by automating the grading. 

I naturally thought of Google Forms.

We are two weeks into this unit, and (among other issues) I have a progress tracker that should show the teacher where students are and show the students where to pick up from.

I had this sheet with dropdowns that I could select to see where students were at. The problem was that:

• It doesn't show the score,
• It wasn't automatic.

With a little ChatGPT work, I got a formula using the FLATTEN formula. 

=IF(COUNTIF(FLATTEN('Lesson 4 Lesson Snap'!A:AC), A3), "Done", "")

This now gives me something that can automatically mark when students complete a lesson. I still have to go in to see what issues students have and where they need help.

Chat prompt and response

Cross-posted to my main blog.

SUBTOTAL

While looking at my YouTube feed, I saw a video with an interesting title - The Excel Function You Didn't Know You Needed. Then the subtitle said that there were 11 or so functions embedded within it. 

The most useful parts of the function that I think will be more useful are the 7 listed below - 

1 AVERAGE

2 COUNT

3 COUNTA

4 MAX

5 MIN

6 PRODUCT

7 SUM

Spreadsheet

Functions and Formulas Fun with Sheets - SUBTOTAL

YouTube Video

SUBTOTAL: The Excel Function You Didn’t Know You Needed!

Most Voters Have Heard Nothing at All About Carbon Removal

This was an interesting graph I found on Data for Progress, and when I find an interesting graph, I try to remake it in Google Sheets.

This was a hard one, and the closest I could get was the graph below. Looking at it again, though, I also forgot to remove some of the lines.

A little refining 

(Made with Datawrapper) -- And I still couldn't match it.

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. (Actually, I wasn't even aware this was happening until I was asked during a lesson to help a student.)

Creating Vertical Lists with Checkboxes

 =TEXTJOIN(CHAR(10), TRUE, FILTER(B4:B5, C4:C5=TRUE))

Today, I realized you can use a formula to make a list inside a cell in Google Sheets. 

Why would you want to do this?

In my case,  I am trying to make the IEP/ILP process smoother. It doesn't make sense to keep rewriting information each time you write a report about a student. I think using a Google Sheet to make a document will be easier. 

I was able to put text together using TEXTJOIN and separated by a space or by comma before, but ChatGPT showed me that I could add the CHAR(10) -- line break -- to a formula to make the list.

This formula puts checkmarks in a list, in order of day, combined with the labels from the matrix.

Save this formula for later. 

=TEXTJOIN(CHAR(10), TRUE, ARRAYFORMULA(IF(B81:F86, A81:A86 & " " & B80:F80,)))

I ended up separating them out into the columns to the right of the checkboxes.

Challenge Accepted

A few weeks ago, maybe a few months now, I created a schedule using ClaudeAI to make a testing clock. 

It's functional. It works. 

Today I noticed that my colleague, who also does testing accommodations, had a spreadsheet projected on the whiteboard with the times.

When he projects it on the whiteboard, it looks like this:

I asked about the formulas in it. At the time, he didn't have any. 

My mind immediately went to work about how to make it work in Google Sheets. 

I had the basic structure in my mind. During my break I went to work building it. 

When we give accommodations, we write the start time, 30 minutes remaining, 5 minutes remaining, and the end time. Calculating this is always a chore. 

To the spreadsheet! 

I knew I wanted a drop-down of student names. This would eventually be for all students in inclusion support, so it would be quick and easy to use.

I found online that I could use a formula called =TIME. Using ChatGPT, I was able to do a VLOOKUP with all the names that looks like this - =B4+TIME(0,B3,0)+TIME(0,B8,0)

Here are all my formulas for this one.

=B7-TIME(0,5,0)

=B7-TIME(0,30,0)

=B4+TIME(0,B3,0)+TIME(0,B8,0)

=B3 + (B3 * IFERROR(VLOOKUP(B1, E4:F7, 2, FALSE), 0))

My tool

Here's a link to the working example.

https://docs.google.com/spreadsheets/d/15FjeNAyHnTran4ejfxk4M477AJrGNJFjxo6P_m2VpZI/edit?gid=1058096702#gid=1058096702

I'll start making it look better tomorrow. I also have to check my formulas.