How did a Scientific American article help me create a much simpler solution in Excel? And what connection does it have to the Einstellung Effect? Read on and I’ll tell you 🙂
What is the Einstellung Effect?
In January I was looking at magazines in a drug store. Flipping through the Scientific American I saw a picture of two old men playing chess.
I love chess and the article had a chess puzzle!
I solved the puzzle using the Smother Mate technique. To my surprise the article said that I was a victim of the Einstellung Effect! WHAT?!?
The Einstellung Effect is the brain’s tendency to stick with the most familiar solution to a problem and stubbornly ignore alternatives.
I was intrigued. I bought the magazine and read the entire article to learn more.
The Einstellung Effect: the human brain’s dogged tendency to stick with a familiar solution to a problem-the one that first comes to mind-and to ignore alternatives…the trouble with this cognitive shortcut, however, is that it sometimes blinds people to more efficient or appropriate solutions than the ones they already know.
the Einstellung Effect and Excel
Recently, I was building part of a dashboard in Excel. The data was like this:
…and the goal was to build this:
- if integer or percent meets/exceeds target arrow is green else it’s red
- if both meet/exceed targets then green background
- numbers & percents in the middle but aligned exactly as seen above
- easy way to change arrow symbol to a different symbol
The person I was helping had a long complex solution (many nested IF functions). I convinced her to use a solution based on the REPT function.
Here is the formula:
=REPT(” “,3-LEN(B7))&B7&” | “&REPT(” “,4-LEN(C7*100&”%”))&C7*100&”%”
This formula isn’t simple but it was about 1/4 the length of her formula.
For some reason I remembered the Einstellung Effect. Could there be an even easier solution? Yes there was! Ridiculously easy!
Download my Excel file. You’ll see two solutions.
The ‘EASY‘ solution takes only a minute to create and you can use any font!
The ‘DIFFICULT‘ solution has a complex formula and only works with monospaced fonts.
This is just a small example. Imagine building a complex model and failing to see EASY solutions. The file could become slower to calculate, larger than necessary and difficult to audit.
When building an Excel model we don’t want to sit back and waste time but if we jump into a solution too quickly we might miss a very simple solution!
The Scientific American
Thanks to Scientific American for an interesting article! Two related articles from their website:
Illustration by Danny Schwartz
A special thanks to Danny Schwartz for allowing me to use his chess illustration. If his illustration hadn’t caught my eye I never would have discovered the Einstellung Effect.
His illustration style is amazing! Follow Danny here:
Power Query Academy
I’m taking this amazing course from Ken Puls & Miguel Escobar! Currently in chapter ‘Query Settings’.
Disclaimer: I’m a student and an affiliate.
While my wife and I watch Netflix she often knits and I doddle. Occasionally I draw something that makes her laugh.
Every person I try to draw ends up looking like a nerd. Since I started doodling (I can’t call it drawing) I have learned to appreciate professional artists and illustrators. The amount of work and training that goes into their profession is amazing. Similar to the thousands of hours that I have dedicated to learning Microsoft Excel!