For the last couple of weeks, I have been intermittently working on a fiendishly challenging Excel problem. This was something I’d not encountered before, there was no simple formula or method for it. Searching online yielded results that worked in specific circumstances, but not mine.
And the only reason I finally succeeded, was because I knew when to give up.
I like writing about taking breaks and looking after yourself, even though I’m not always the best at it myself. As an example, a few weeks ago I spent almost 6 straight hours working on a single problem that just seemed to grow and grow the more I hammered away at it. But this problem was the perfect demonstration to myself of how essential it is to take breaks.
Over and over I went away from the problem and came back to it. Sometimes for a few minutes, sometimes for hours, overnight or even leaving it for a whole day. During those breaks, I played games, went for walks, relaxed and watched NetFlix. During quiet moments, ideas about how to solve the problem would come into my head. Most of them failed, but each failure brought me closer to my target. And so I went away again to let new ideas form.
Several times I really thought I had the answer, only to come back later after realising I’d missed something or made a miscalculation. At a couple of points, even my checking methods were wrong, forcing me to start over.
A Hero Emerges
The hero of the challenge was the Goal Seek feature in Excel. It basically increments an input repeatedly little by little until the end result is the one you want.
I knew the inputs and I knew how they related to the final output, but I didn’t know how they got there. Goal Seek helped me solve that by giving me outputs based on my inputs and what I wanted my starting value to be, so I could work backwards from there and find the solution.
As long as it took me to find the answer, it would have taken much longer without Goal Seek. Every time I made a change and a new pattern emerged, I had to find a way that it linked to the inputs. Using Goal Seek and comparing the percentage changes against the inputs to identify recurring patterns got me all the way to the answer in the end.
Using Goal Seek is pretty straightforward, but hard to explain. Since any explanation I would give would be pretty unpolished, I’d recommend a guide like this one:
Or if you want to pick up a wider range of Excel knowledge, head on over to my course recommendations here: