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.

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.

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.