John Siracusa

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
Jul 1, 2023 at 7:54pmIvory for Mac
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 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 ¯\_(ツ)_/¯
Show threadraviJul 1, 2023
@siracusa 😆😆😆
Show threadRevJeberJul 1, 2023
@siracusa
I bet there’s a PowerPoint presentation that explains all that.
Show threadDrChrisJul 1, 2023
@siracusa have you written much in Python? It’s like Perl but it makes sense.
Show threadJohn SiracusaJul 1, 2023
@DrChris 👎
Show threadDrChrisJul 1, 2023
@siracusa I will admit Python does commit some terrible sins with white spaces.
Show threadRob MillerJul 1, 2023
@siracusa I think the pivot table does actually work.
Show threadklassobanierasJul 1, 2023
@siracusa seems like a job for COUNTIFS
Show threadJohn SiracusaJul 1, 2023
@klassobanieras Yes, that's what I'm using. It works for one row but not for the other.
Show threadklassobanierasJul 1, 2023
@siracusa nope you were using countif + if (unless someone edited the formula)
Show threadklassobanierasJul 1, 2023
@siracusa anyway check the tab ‘COUNTIFS ftw’ , it’s working fine there
Show threadgarrettJul 1, 2023
@siracusa Use =COUNTIFS(MemberRange, “Yes”, TierRange, 1). Then change “Yes” to “No” or 1 to 2 or 3 as appropriate.
Show threadJohn SiracusaJul 1, 2023
@grt That's exactly what I did! But changing "Yes" to "No" made it stop working. Try it!
Show threadgarrettJul 1, 2023
@siracusa At this point the spreadsheet is a disaster, but I thought you had COUNTIF() with an embedded IF(), not COUNTIFS(). In any case, check out Sheet1 A16:E20 for my solution
Show threadRyan JonesJul 2, 2023

@siracusa SUMPRODUCT is an array formula, which means it does wierd shit on multiple cells unless you (off the top of my head…) press modifier keys when Entering the formula, add a leading “.” to the command in excel, enable some Array mode, include ArrayFormula equation, and/or certain conditions are met, etc etc.

Basically… ya, sneaky shit.

Use CountIfs for this.