John SiracusaJul 1, 2023

Google Sheets defeated me this morning. Can any Sheets experts hop into this document and tell me where I went wrong? Please look at "Sheet1" to see the problem. https://docs.google.com/spreadsheets/d/162axdyEkLjugxdTO23us07dytoDsASrdXNgG1gRbBDI/edit?usp=sharing

(After being spurned by the accursed spreadsheet, I retreated to good old-fashioned programming to resolve the situation, as usual. Yes, with Perl.)

Formula Test

Form Responses 1 Timestamp,Are you currently an ATP member?,Tier List 7/1/2023 9:20:10,Yes,2 7/1/2023 9:20:35,Yes,3 7/1/2023 9:20:45,Yes,2 7/1/2023 9:21:06,Yes,2 7/1/2023 9:21:10,Yes,3 7/1/2023 9:21:11,No,2 7/1/2023 9:21:23,Yes,3 7/1/2023 9:21:26,Yes,2 7/1/2023 9:21:34,Yes,1 7/1/2023 9:21:43,No,...

Google Docs
Show threadJohn SiracusaJul 1, 2023

Some approaches that work have been found. One uses QUERY() https://fosstodon.org/@jamesnvc/110640631675785694

Another adds ARRAYFORMULA() around my whole formula, which also works.

Still no explanation why the formula I'm using works for one row but not for another, though!

James Cash (@[email protected])

@[email protected] using `QUERY` instead of countif seems to work: =QUERY('Form Responses 1'!B:B, "select count(B) where B = 'No' label count(B) ''", 1)

Fosstodon
Show threadJohn Siracusa
One more approach that works: use COUNTIFS(…) instead of COUNTIF(IF(…))
Jul 1, 2023 at 8:27pmWeb
Show threadJohn SiracusaJul 1, 2023

GPT-4 came up with a good approach and explanation. I actually tried ChatGPT…but version 3, not 4. That's what I get for being behind the times!

Anyway, look at the comment on cell B3 in the GPT-4 sheet for the explanation. https://docs.google.com/spreadsheets/d/162axdyEkLjugxdTO23us07dytoDsASrdXNgG1gRbBDI/edit?usp=sharing

Show threadsayrerJul 1, 2023

@siracusa I kind of expect it, but ATP always does venture into art criticism. Here, you’ve arrived at Dada or Italian Futurism, and I can't tell if it's intentional (it might be totally intentional, I can't tell).

But here's the thing, and it’s not difficult to see the parallels: http://exhibitions.guggenheim.org/futurism/

Italian Futurism, 1909–1944: Reconstructing the Universe

The first comprehensive overview of Italian Futurism to be presented in the United States, this multidisciplinary exhibition examines the historical sweep of the movement from its inception with F. T. Marinetti's Futurist manifesto in 1909 through its demise at the end of World War II.

Show threadJosh MillerJul 1, 2023
@siracusa I’ll forever be curious how much spiraling conversations with GPT-3.5 that don’t solve the problem cost OpenAI, and how that cost compares to solving the problem with GPT-4…