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.

SEQUENCE

The SEQUENCE formula will do what is says - it will make a sequence of numbers in many different ways. Here is a video I made that demonstrates it.

TOCOL and TOROW

TOCOL and TOROW

There are two functions to look at when changing a dataset into a column or row: TOCOL and TOROW. Both of these formulas take an entire dataset and reformat them to either a column or a row. 

In the example below, I have a dataset surrounded by a black line. To make it into a column, in the cell A2 I typed the formula =TOCOL(C8:O70). C8:O70 is the range of the data. 

When I tried TOROW, I had to add another 500 columns, so I just adjusted the range so it would fit.

 

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.

Recreating a Chart from a Visual Capitalist Infographic

Over the summer, I had some time, so I tried a few experiments. I found some interesting YouTube videos from people remaking the graphics using Excel.

I don't have Excel - I have Google Sheets - so I got to work. They are almost the same anyway. 

And here's the final result. Nothing too fancy, but easier to read than a list of numbers. I think the heat map looks pretty good.