In a recent post we were looking at how failing on some of the modelling basics can end up being a bit of a giveaway. In that case we were looking at how a minor formatting inconsistency (and inattention to detail) in the outputs sheet, something that we quickly rectified using custom formats, was a sign of potential underlying problems in the rest of a model. See “Swiss wood piles“.

Adding across columns

What we didn’t tell you in that original post was that there was another basic error in the outputs section, indicative of modelling inexperience and possible problems deeper in the spreadsheet. The model was semi-annual but the modeller had chosen to create the annual totals in the outputs section manually. So every time they worked from left to right in the outputs section they manually wired the sum functions to the semi-annual results. It must have taken them ages to create the outputs sheet. What we would have preferred to have seen is a function in the outputs section that could quickly be filled from left to right. In the outputs section as you filled across one column your function would need to adapt and pick up the next two semi-annual results.

It’s Sumproduct that has the superpower

If you’ve been following our blog posts you’ll know that we’re massive fans of Sumproduct and its superpowers. It’s a well-kept Excel secret but you can use Sumproduct for pretty much all your data picking (as long as its numbers your picking out of a table rather than text) and data amalgamation challenges. We think Sumproduct is the superhero of Excel functions.

Other solutions: Sumifs and Offset

But Excel has maze of other solutions that would help us solve the problem too. Sumifs will add up data that meets certain criteria (in this case we are looking for a particular period that belongs to a particular year) and Offset could be used to ‘jump’ a Sum formula across the semi-annual source data two columns at a time. In the spreadsheet below you can see the potential solutions compared side by side. You can click on the Excel template to download it:

Sumproduct for adding columns

The best Excel formulas

There always seems to be multiple potential answers to a problem in Excel. Sumproduct ends up being our ‘go to’ for anything that seems like it might involve data picking or amalgamation. But, if you can think of a few alternatives, it might be worth experimenting to try and find the shortest neatest solution. That process will help you find the best Excel formulas for your financial modelling.

Read more

You’re reading stories from Financial Training Associates Ltd, the company that brings you the best formulas for Excel modelling.

Read more