R1D24 Everyone Has Written a Function

| programming | dotnet |

I am not 100% sure if it counts, but I am going to write about spreadsheet software in this post.

To be clear, I suck at using spreadsheets. I spend way more time trying to get stuff to look pretty than I do thinking about how to solve problems with the tool. I am certain that I know about 5% of all the things that are possible.

I was motivated by Joel Spolsky’s Excel training video a few years ago and have tried to get better at using it.

I spent a little bit of time with my sales enginering hat on making a kick ass spreadsheet when I realized pretty much everyone in the world has written a function ast least once in their life thanks to the magic of spreadsheets.

Especially since I’ve spent the last couple of days diving back into functional programming, I can’t think of a better example of what functional programming looks like than what you put into a random cell starting with the equal sign in Excel.

The specific problem that I was trying to solve was to calculate a weighted percentage match based on the priority and sum of one column compared to a “Yes” or “No” in a seprate column. This is where the SUMIFS function comes into play.

Component Priortity Vendor A Vendor B
Ability to do X 4 Yes No
Ability to do Y 2 Yes No
Feature A 3 Yes Yes
Feature B 1 No Yes
Feature C 5 Yes Yes
Weighted Percentage Match: 93% 60%
The specific calculation for the weighted percentage match comes out to be
=SUMIFS($C3:$C7, D3:D7, "Yes")/SUM($C3:$C7)
Which translates to "sum the priority values all the yes's and divide by total priority sum". This means that at least based on the priorities that I have defined Vendor A is a 93% match compared to Vendor B.

Spreadsheets are amazing because they give you half the power of a database with half the power of a REPL. You can slice, dice, transform, and visualize data in ways that would be very difficult to accomplish with a pure programming langauge solution. Use them.

 

Thank you for reading! Share your thoughts with me on bluesky, mastodon, or via email.

Check out some more stuff to read down below.

Most popular posts this month

Recent Favorite Blog Posts

This is a collection of the last 8 posts that I bookmarked.

Articles from blogs I follow around the net

House Bill 1774: Black Lives No Longer Matter

Mayor Muriel Bowser, Profiles in Courage: Crews started work Monday to remove the large yellow "Black Lives Matter" painted on the street one block from the White House. D.C. Mayor Muriel Bowser announced the change last week in response to pressur…

via jwz March 10, 2025

Pluralistic: Eggflation is excuseflation (10 Mar 2025)

Today's links Eggflation is excuseflation: We don’t have egg companies in the business of making eggs anymore, we have egg companies in the business of exploiting egg shortages. Hey look at this: Delights to delectate. Object permanence: 2005, 2010, 2…

via Pluralistic: Daily links from Cory Doctorow March 10, 2025

Part 1: The Shitball

This is the first part of a series that attempts to capture my current thinking about The Situation. TL;DR: Our attention has been hijacked and weaponized, and its imperative for us to to direct our attention to healthier and more […]

via Blog – Brad Frost March 10, 2025

Generated by openring