Final Friday Fix: March 2020 Challenge
27 March 2020
On the final Friday of each month, we’re going to set an Excel / Power BI challenge for you to puzzle over so that you can get your “Excel / Power BI fix”. Challenge your office colleagues to see who can solve the puzzle quickest. There are no prizes at this stage: you’re playing for bragging rights only!
Not everything revolves around LET functions, dynamic arrays, XLOOKUP, VBA, Power BI, et al. Sometimes problems are more rudimentary and occasionally, in this series, we will highlight such challenges to encourage you to think outside the box or remember your studies from yesteryear.
This month’s Final Friday Fix is such an example.
In a recent financial model build, we needed to work out how much of a certain product was sold, which reduced each period, based on two multiplicative factors. Over the life of the project, the proportion that needed to be considered was:
In Excel, I just needed to sum this as efficiently as possible – returning the total as a fraction in its simplest form.
This month’s challenge is to create an Excel spreadsheet that can calculate the result and be sufficiently flexible to allow for alternative, similar sequences of varying length. No dynamic arrays, no VBA, no megaformulae. In fact, for those readers with children, you might find they do better at this than you do…
Sounds easy? Then why not have a go? We’ll publish one solution in Monday’s blog. Have a great weekend in the meantime!
