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.

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.


Leave a Reply