CHOOSECOLS

I have been putting together a data dashboard for my school for two years, and you'd think I would have had this figured out by now, but I didn't - the grades 1-12 all work out fine and sort nicely, but when I add in our early years classes (KB, KA, PS) those aren't recognized as number so they are put at the end of the list, after Grade 12; not in front of grade 1 where they should naturally reside.

Not knowing how to solve this effectively, I turned to ChatGPT to help me out. 

It suggested I make a helper column, I called it "hidden", and label each grade with its own grade, since the numbers were not a problem, and we needed to convert the "KB", KA, and "PS" text to a numerical value to rank them against other numbers.

I gave them -3 for PS, -2 for KA, and -1 for KB. 

I then used this formula to make the grade for each grade. It reads column B and rewrites the grade in column D for me.

Then I wanted another table with everything sorted. I would have used QUERY, but ChatGPT suggested I use CHOOSECOLS. The reason it said was that QUERY is used to search a database, whereas CHOOSECOLS creates a new array from the selected columns in the existing range.

That was kind of cool - I'd never used that formula before. 

The documentation shows it being used to manipulate data in an already-made table.

Keep a Contact List in a Doc Updated Using Google Sheets

The other day, I was reading our Teacher Handbook. The Teacher Handbook includes names of teachers and what they teach. The problem is that one teacher had changed positions and so the handbook was out of date.

This video shows how to keep documents updated by maintaining a single spreadsheet.

ICE Arrests in May 2025

This table was made using the data from ICE and the Department of Homeland Security, so I think it is accurate.

I wish I had made this ICE chart on this spreadsheet collection, but I made it on its own file and copied what I made over here, so some of the graphs and data arrangement were unfortunately lost.

Spreadsheet

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.