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|
|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.