Uncategorized

Google Sheets: Sum All Rows Above “Me” 150 150 craig

Google Sheets: Sum All Rows Above “Me”

Tracking my aviation journey via a handwritten logbook and a spreadsheet, I got annoyed by having to update the Totals row, every time I inserted another 20 rows above it, so each column’s formula would include those 20 new rows in the =SUM(…) formula

I did some online searching and learned about the very helpful =INDIRECT function, as well as simple string concatenation, including a formula that was almost copy-and-paste, except that it hard-coded the Column designator (e.g. A, B, etc), though it did handle specifying rows from 2 (just below my header row) through the row just above the Total cell in question.

Not wishing to spend 45 seconds editing those column designators for the entire spreadsheet, I instead spent probably about 20 minutes (yes, I’m a programmer, haha) coming up with this more-flexible formula that derives the current column as well, in the hopes that I’d be able to use this in other situations:

=sum(indirect(regexreplace(address(row()-1,column(),3),”[$]([A-Z]*)([0-9]*)”,”$12:$1$2″)))

In short, what this formula does is

  • compute the previous row’s number: row()-1
  • compute the current column number (NOT name!): column()
  • uses address(r, c, 3) to turn that into a column-letter-plus-row-number string, such as $H231
  • uses regexpreplace() to extract that alphabetic component (into group $1), which is the column letter, and the row number (into group $2)…
  • …then has that function return a replacement string (e.g. H2:H231) suitable for use in =SUM()
  • wraps that replacement string in an =INDIRECT(), which means it’s an evaluated (not constant/static) expression
  • passes that to =SUM()

The above expression can this be pasted into any cell and will compute the sum of all the cells above it (except the first one, the header row, aka Row 1).

Hope this helps someone out! (I tried adding it as an answer to a circa-2019 question on a Google discussion forum, but that question was closed to comments.)

New Role at Namely! 150 150 craig

New Role at Namely!

As of November 1, 2021, I’m employed as a Senior Software Engineer at Namely, a NYC-based tech company providing online Human Resources (HR) software for mid-sized companies. We have positions open, so please reach out to me and I’ll put you in touch with the appropriate individual; similarly, if your company is looking to “level up” its HR offerings, let me know!

Flying to New Heights! 150 150 craig

Flying to New Heights!

Achievement unlocked: I am now a Private Pilot, per the Federal Aviation Administration (FAA)!

Thursday, Ron, the FAA Examiner (who is an Airline Captain for Delta), performed the “ground”, also known as “oral”, portion of the check ride: over an hour of questions, reviewing of my sample cross-country route, and so on.

As weather didn’t cooperate Thursday, Friday (the 16th) we did the actual “practical test”, widely called the “check ride”.

Having passed both (though with slightly muted colors!), I’m now a Private Pilot and can fly single-engine airplanes (land-based, not sea-based) that aren’t considered “complex” nor “high-performance” for fun (not profit!) during so-called VFR conditions (basically that means no flying in clouds nor rain).

Learning to fly has been a challenging, yet fun, hobby for the past 10 months. I plan to enjoy these new privileges and further my studies, perhaps pursuing an Instrument rating (which opens up many doors, including being allowed to fly in clouds and rain, use autopilot, and so on, if supported by the aircraft), while taking in a variety of mini-vacations hither and yon with my wife in the co-pilot’s seat.

Ron, the FAA Examiner (who is an Airline Captain for Delta), congratulates me after our check ride together
Check Ride Success!
GitHub Discussion Board, Parler, and Gab 150 150 craig

GitHub Discussion Board, Parler, and Gab

Awhile back, I created personal accounts on Parler and Gab, to augment (and ultimately replace) my personal Twitter account.

More recently, I enabled the new GitHub Discussions feature on my fork of Joker, and posted the “Welcome” message link on Clojure’s Joker channel on Slack, plus this content on both my Parler and Gab accounts:

If you’re interested in the #Clojure programming language and want to try out a version that is compact, interpreted, and not based Java[*] nor Javascript, consider #Joker, perhaps even my “gostd” fork of it.

Joker, written in #Go, builds down to a single, fairly compact and quick-starting, executable, with numerous core and widely used namespaces already “built in” to the executable (therefore not needing deployment alongside it).

My “gostd” fork adds an experimental capability in which many Go standard-library packages are automatically added to the core libraries during the Joker build. There’s lots of work still to do on it, but I’m making progress in my spare time. (Yesterday I got it working with Go 1.16beta1.)

I just enabled GitHub Discussions on my fork’s page; feel free to join in: https://github.com/jcburley/joker/discussions/20

And Merry Christmas!

[*] If you’re looking for a compact, interpretive Joker based on a quick-startup JVM engine, try #Babashka; last I checked, it was competitive with Joker (in terms of startup time and overhead), and it might run faster to the extent any JIT works effectively (as Joker, being built on top of Go, has no JIT).

Twitter 150 150 craig

Twitter

Recently I deactivated my (consulting) Twitter account.

For posterity, I made an archive of its contents available.

Why Discord is switching from Go to Rust 150 150 craig

Why Discord is switching from Go to Rust

I just came across this article via the 2020-02-08 O’Reilly Programming Newsletter:

Why Discord is switching from Go to Rust

While I’ve greatly enjoyed — and become increasingly productive in — Go, using it mainly to enhance Joker to automatically include Go’s standard library and (more recently) start up more quickly (about which I plan to write another blog post soon) — I’ve been planning to look into Rust as a possible new low-level systems-programming language to replace C in my arsenal.

Would Joker (or a similar fast-startup, low-overhead, Clojure interpreter) be worth considering re-implementing in Rust?

I plan to look into startup-time performance for Rust programs soon!

Startup Time of Various Languages 150 150 craig

Startup Time of Various Languages

I did my own research on this a couple of years ago, but just now discovered this helpful GitHub repo that has some interesting data:

https://github.com/bdrung/startup-time

The repo goes back as far as 9 years, and it isn’t obvious how recent the data is (though the version info provided with each language implementation is helpful).

But since the repo provides the ability to reproduce results on one’s own, it seems like a valuable resource.

Joker: a Clojure Linter and Interpreter 150 150 craig

Joker: a Clojure Linter and Interpreter

In last November’s post, I discussed what was then called gostd2joker, and have since renamed gostd, which is a fork of Joker. Joker itself is a small, single-threaded linter and interpreter of a subset of Clojure. My fork seeks to automate creation of wrappers around much (if not all) of the Go standard library, and perhaps other arbitrary packages in the future, so they are accessible (even if via fairly primitive, low-level mechanisms that aren’t idiomatic Clojure) via running Joker code.

Though I put that project aside for several months earlier this year, while pursuing other fields of endeavor, I returned to it a few months ago and have made substantial progress. Compare the namespaces provided by canonical Joker to those provided (though incompletely, in most cases) by my fork.

Besides that work, the canonical version of Joker continues to improve at a reasonable pace, and is increasingly useful as a scripting language.

In a recent podcast, Joker’s author, Roman Bataev, discusses Joker’s history, capabilities, and potential futures, including a brief shout-out to my fork at around the 15:00 mark:

https://podcasts.apple.com/us/podcast/s2-e9-joker-with-roman-bataev/id1461500416?i=1000455478703

I continue to enjoy working on this (currently unfunded) project, due to its heady mix of Clojure (a well-designed Lisp variant), Go (a well-designed imperative language), and automated code generation.

Bulk Code Generation 150 150 craig

Bulk Code Generation

As much fun as coding can be, I prefer to write code that generates code.

My most recent effort is gostd2joker, which collects information on Go’s standard library (the packages provided in its source tree) and generates the Go and Clojure code to provide access to some of those APIs within Joker, a Clojure interpreter that is written in Go. While there remain substantial limitations in terms of which APIs are eligible for transformation, the resulting list of converted packages is noticeably larger than that for “vanilla” Joker.

Having successfully deployed a few Joker scripts on my email server, mainly to test the waters (but also to replace fairly ugly Bash scripts with more-elegant Clojure code), I’m looking to further my use of Clojure. I’d like to achieve “Clojure Everywhere”, in which all my full-stack code (UI, backend, and quick-running scripts) is written in Clojure on top of various hosts (JVM, JavaScript, and the Go runtime).

Much work remains to be done, mainly to instill, in Joker, a proper concept of the Go runtime as a “host” to parallel Clojure’s JVM and ClojureScript’s JavaScript engines, thus introducing Go types (native and those provided via packages), field and method access to them, instantiation of instances of them, and so on.

The result should be quite useful, not only in my own research and development, but potentially to others who might find a quick-starting, low-overhead scripting language, with full access to the Go runtime, to be a formidable tool in their scripting arsenal.

Can you envision using such an enhanced version of Joker? If so, how?

Housekeeping Note 150 150 craig

Housekeeping Note

I’ve started deleting users who haven’t commented on, or posted, anything, to get rid of the many likely-spam/bot users.

The bulk-delete tool I’m using doesn’t let me filter on whether a user has a profile image, and it appears to have already deleted at least two legit-looking subscribers. So I’m holding off deleting more for now.

Please login and comment/post on occasion, at least for awhile, as I clear out inactive users. (I’ve added a plugin to reduce new-user registrations from known spammers as well.)

Thanks for your cooperation!

  • 1
  • 2