2180: Spreadsheets

|< < Prev Comic #2180 (July 24, 2019) Next > >|

Spreadsheets

Title text: My brother once asked me if there was a function to produce a calendar grid from a list of dates in Google Sheets. I replied with a single-cell formula that took in a list of dates and outputted a calendar. It used SEQUENCE(), REGEXMATCH(), and a double-nested ARRAYFORMULA(), and it locked up the browser for 15 seconds every time it ran. I think he learned a lot about asking me things.

Explanation

This explanation may be incomplete or incorrect: Created by a REGEXREPLACE(). Need to explain the Google Sheets fucntions. Do NOT delete this tag too soon. If you can address this issue, please edit the page! Thanks.

Cueball is doing some task on his computer, with an angel and devil on both sides of him, trying to influence his work. The angel is telling him to do things the "right" way, while the devil is telling him to do his work using a spreadsheet, which is considered by professional software engineers to be a shortcut or a hack.

Spreadsheets are the best and most well-known end-user development tool [1], allowing non-developers to easily create code with the right specifications fast and with low effort, but they are often mocked by developers as a wrong approach to programming, for its lack of maintainability. Although it is not clear from the cartoon that this is meant, the "right" alternative to using a spreadsheet for some task usually involves a database and a general programming language.

The punch line comes when the angel becomes so intrigued by the functions that can be accomplished by spreadsheets, Google Sheets in particular, that it gives up trying to dissuade Cueball, and asks for more information from the devil.

In the title text, Randall mentions a time when he created a calendar grid in Google Sheets using a list of dates. This is described as being done in a "single-cell formula", and taking a long time to run. This shows the power and complexity of spreadsheets. The procedure taking a long time to run, and freezing up the computer for 15 seconds every time it ran, was probably not what Randall's brother had in mind when he requested help. His brother learned he might need to be wary about what he gets back when asking Randall for assistance.

All functions mentioned in this comic can be found in Google Sheets, but similar functions to some of them can be found in most modern spreadsheet applications.

REGEXREPLACE(text, regular_expression, replacement) Replaces part of a text string with a different text string using regular expressions.

ARRAYFORMULA(array_formula) Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.

QUERY(data, query, [headers]) Runs a Google Visualization API Query Language query across data.

IMPORTHTML(url, query, index) Imports data from a table or list within an HTML page.

SEQUENCE(rows, columns, start, step) returns an array of sequential numbers, such as 1, 2, 3, 4.

REGEXMATCH(text, regular_expression) Whether a piece of text matches a regular expression.

Transcript

This transcript is incomplete. Please help editing it! Thanks.

[Cueball is at his computer. In the air on either side of him are an angel version of Cueball, with a halo and wings, and a devil version of Cueball, with horns and a pitchfork.] [The angel's dialogue appears in regular print, while the devil's dialogue appears in white print in black speech balloons.]

Angel: Don't use a spreadsheet! Do it right.

Devil: But a spreadsheet would be so easy.

Angel: In the long run you'll regret it!

[Closeup on Cueball, the angel, and the devil.]

Angel: Take the time to write real code.

Devil: Just paste the data! Tinker until it works!

Devil: Build a labyrinth of REGEXREPLACE() and ARRAYFORMULA()!

Devil: Feel the power!

[Closeup on the devil.]

Angel (off-panel): Fight the temptation!

Devil: Ever tried QUERY() in Google Sheets? It lets you treat a block of cells like a database and run SQL queries on them.

[Another shot of Cueball at his computer with the angel and devil at either side.]

Angel: Don't listen to--

Angel: ... wait, really?

Devil: Yes, and let me tell you about IMPORTHTML() ...

Angel: Oooh...

Trivia

Spreadheets used for complex tasks, evolving into complex algorithms was also the punchline of 1667: Algorithms.

 add a comment! ⋅  add a topic (use sparingly)! ⋅  refresh comments!

Discussion

I really hope Randall shares this formula he made. It sounds incredible. 162.158.126.34 21:15, 24 July 2019 (UTC)

Seems to me to perhaps be a bit of nerdsniping (a la XKCD 356) bait...

My unofficial job title is the Head of the Department for Extreme Spreadsheeting. There are multiple reasons for this, including that we need to share info easily across different offices, I'm very familiar with spreadsheet formulas, and I have no f***ing clue how to get a SQL database functioning properly. Misterblu28 (talk) 21:48, 24 July 2019 (UTC)misterblue28

It involves chickens, black candles, a full moon, and one of those fancy space-age pens that can write on any surface.

I think that traditionally, one only needs the blood of the chicken unless you're making a chicken salad sandwich for afterwards. Mootstrap (talk) 12:05, 26 July 2019 (UTC)

The "devil" is clearly a reference to the FreeBSD daemon mascot. 172.68.38.64 04:42, 25 July 2019 (UTC)

The devil is clearly a devil.141.101.98.148 08:48, 25 July 2019 (UTC)

The devil is clearly the strong one for everyone working in my company. A list used by ~50 people, 3 times a day? lets just put an excel sheet on sharepoint.... A complex design tool? Give me 2 days and a lot of hidden sheets in the back of the file and do it in excel - everything is excel. --Lupo (talk) 06:04, 25 July 2019 (UTC)

Anybody remember Oracle’s SQL*Calc spreadsheet application? Individual cells could not only contain select queries but also insert, update, and delete, all using variable data from the spreadsheet. So you could select data from tables into a multidimensional array of cells in s spreadsheet, manipulate the data in the cells, then insert in back into the same (or different!) tables. Powerful but dangerous!172.68.46.167 06:15, 25 July 2019 (UTC)

Remember all the TV Tropes links? We could totally put “Good Angel, Bad Angel” here. 172.68.141.148 10:41, 25 July 2019 (UTC)

I refuse to accept this as complete until somebody can get the original script out of Randall or somebody here manages to make something that does the same job, or better.

Here's a start to the formula; it doesn't use REGEXMATCH() however. Uses a named range 'DatesList' which has the list of dates. Generates a calendar with Sunday in the first column where the only dates showing are those in the list -- wasn't sure what sort of output to target. =ARRAYFORMULA(IF(ISERROR(MATCH(ARRAYFORMULA(SEQUENCE((MAX(DatesList)-MIN(DatesList)-WEEKDAY(MIN(DatesList))+1)/7+1,7,MIN(DatesList)-WEEKDAY(MIN(DatesList)),1)),DatesList,0)),"",ARRAYFORMULA(SEQUENCE((MAX(DatesList)-MIN(DatesList)-WEEKDAY(MIN(DatesList))+1)/7+1,7,MIN(DatesList)-WEEKDAY(MIN(DatesList)),1))))

Relevant other comics about Excel (from a developer's perspective): http://www.commitstrip.com/en/2014/07/23/excel-the-poor-mans-ideexcel-lide-du-pauvre/ and /2014/12/19/the-coder-and-the-beast/ 108.162.229.136 19:57, 25 July 2019 (UTC)

My goto functions tend to be LOOKUP() (or HLOOOKUP() and VLOOKUP() for various subtelties like matching unsorted arrays), LEFT(), RIGHT(), MIDDLE(), COUNTA(), COUNTIF() (especially for range A$1:Acurrent_or_preceding_row_number_for_this_cell to establish "this is the nth example of some quality in this column", usually for firstness), FIND(), ISERR(), ISNUMBER(), SUBSTITUTE(), and of course loads and loads of IF()s, ...usually heavily nested. And at least these days Open/LibreOffice accept comma seperation of function params, 'cos forgwtting I'm not in Excel and not using semicolons in such a scratch-built monstrosity leading to an error that wasn't bad logic or nesting was a bummer, until I realised... 141.101.98.148 20:56, 25 July 2019 (UTC)

I was hoping the explanation would say what those named functions do, LOL! Never used Google Sheets. But I've used the hell out of Excel. Can't imagine how a single cell could display a calendar, but I've often done a quick on-the-fly calendar where each cell is a day... Usually to check what weekdays certain days are. Like "What weekday was I born?". Often I designate one cell as a starting date, the first row uses the WEEKDAY function to check "Is that this weekday, an earlier weekday or a later weekday?". A later weekday, leave it blank. An earlier weekday, take yesterday and add one. This weekday, show it. Then Week 2, Sunday is Saturday + 1, Monday is Sunday + 1, etc. Copy / Paste to finish the week, Copy / Paste the week to finish the calendar. I change the cell formatting to show only the Day of the date, but each cell stores an entire date. And the 8th column shows the month every time the month changes (usually a VLOOKUP on a reference table I make with Fill Series). I can then just change the master cell to change what date range the calendar shows. I might use Excel the most for mass renaming files though. :) NiceGuy1 (talk) 04:24, 26 July 2019 (UTC)

excel to rename files? How very Rube Goldberg. Windows still includes shell batch commands (.bat files) for that kind of functionality.162.158.246.82 14:44, 27 July 2019 (UTC)

Yes, I use Excel to make the contents of the batch file, a list of REN commands instead of typing them individually, then copy and paste to a Notepad file, save it as "Go.bat", then type Go in a DOS command window. :) I use Excel to construct all the individual filenames, then compile the old names with the new names into "ren [old name] [new name]". If there's more than like 5 files, programming the Excel is faster and less work than naming each file individually. NiceGuy1 (talk) 06:43, 12 November 2022 (UTC)

"Go.bat"? How uninformative! – I, on the other hand tend to use "do.bat" for all my "preautomate a single task" needs... Much more descriptive, I'm sure everyone will agree. ;)

Something about the display is bugged out. 162.158.107.67 05:43, 26 July 2019 (UTC)

QUERY() is overrated. FILTER() is more advanced and usable in other formulas.

I find this comic to be an accurate reflection of part of my job. I am the IT guy for our company. Which means that I'm also in charge of taking the basic reports that came with our point of sale program and editing the data into reports that are custom designed to answer the question that the manager asks. And the question is often different every day. So dropping the 10 page report into a spreadsheet to massage the data into 5 lines of 'boss answer' makes sense. But sometimes the question is a repeat. And after the 10th time the manager asked the question, I added a procedure into the database. So now every time I get a 'boss question', I have to decide if I'm going to make a procedure for it or a spreadsheet. The comic gets amusingly ridiculous for me on the 3rd panel, because running a query in the spreadsheet has pushed the complexity of my work up to the point where it's just as easy to make the procedure in the database. 172.68.90.76 19:45, 29 July 2019 (UTC)SiliconWolf

For days after this comic was uploaded I was afraid to visit explain-xkcd as I was certain that the formula mentioned in the title text would be recreated already. Call me nerd-sniped, but I could not resist giving it a try. So here it is: https://docs.google.com/spreadsheets/d/1Q5GOo6x49HkyDsDGmr61za4S5GWCuXQkd-tX9zN4Zwo/edit?usp=sharing The calendar generates entirely from cell D4. Only input is start date in A1 and end date in A2. I don't know if Randall seriously developed the formula he described, but mine differs somewhat from his description. It does make heavy use of SEQUENCE, but uses only one ARRAYFORMULA (why would you double-nest it anyway?) and does not use REGEXMATCH as I could not find any use for it. Of course, there's also a hodgepodge of functions SWITCH, IF, some operators and a bunch of functions related to date calculations. It freezes the browser for some 15 seconds just as Randall promised. Output is a calendar displaying all the months from start to end date, containing name of the month, year, weekdays and every individual day. Feel free to copy the formula from D4 into your own Google Sheets spreadsheet and add any dates you like in cells A1 and A2 (they must be in those two cells in your sheet, too) using the function =DATE(yyyy,mm,dd). For me it works easily even for dates decades apart. If you see a random N/A error in one or two cells (though I haven't spotted any), let me know, it's easily correctable. If you see a REF error, you probably inserted dates decades apart, in which case you just need to hold on and Google will add more rows automatically. Be patient with it. Ipez (talk) 15:03, 3 August 2019 (UTC)

if that also works in excel, i'll definitely use that An user who has no account yet (talk) 23:48, 13 October 2023 (UTC)