Lev Lazinskiy

R1D24 Everyone Has Written a Function

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.

=SUMIFS($C3:$C7, D3:D7, "Yes")/SUM($C3:$C7)

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.