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 Siracusa

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
Jul 1, 2023 at 8:19pmWeb
Show threadJohn SiracusaJul 1, 2023
Also, this Google Sheet is rapidly succumbing to The Tragedy of the Commons…
Show threadRob MillerJul 1, 2023
@siracusa seems a triumph to me. Several clean versions to avoid collaboration clash and at least two versions that work!
Show threadGeoff GreenJul 1, 2023
@siracusa
Show threadAaron GabbertJul 1, 2023
@siracusa whoops. Opened on my phone and my palm created a few blank sheets before I realized what I was doing 😅
Show threadGrievous AngelJul 3, 2023

@siracusa cue a large number of social science geeks explaining that the tragedy of the commons did not exist and its theorist repudiating it later. See @cstross’s blog passim.

(It’s been hijacked by the right, obvs.)

Show threadJohn SiracusaJul 1, 2023
One more approach that works: use COUNTIFS(…) instead of COUNTIF(IF(…))
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…
Show threadRyan MoreyJul 1, 2023
@siracusa I don't think it was working actually, I think your original formula for members was giving the sum for both members and non-members
Show threadgarrettJul 1, 2023
@siracusa scroll down on Sheet1 to the blue range - the key is using COUNTIFS() instead of COUNTIF()!
Show threadJohn SiracusaJul 1, 2023

@grt At ha! I didn't see the "S".

So, why does the COUNTIF(IF(…)) approach work for "Yes" but not for "No"?

Show threadgarrettJul 1, 2023
@siracusa ¯\_(ツ)_/¯