Imagine you had data in one part of your Excel spreadsheet and needed to amalgamate that same data somewhere else. It’s a common Excel modelling challenge. Maybe the source data is unevenly spaced across your spreadsheet. Then, in the summary, you’d like to make use of a formula that you can happily fill across from left to right without a lot of manual rewiring.

Sumproduct vs. Offset

Ordinarily we’d gravitate to Sumproduct for all of our Excel data picking and amalgamation challenges. Sumproduct really is the super-hero of Excel functions. You can use it to pick data, amalgamate data, combine data across multiple dimensions (horiziontally and vertically) and across multiple criteria.

But Offset also seems tailor-made for situations when you want to jump across Excel data.

In the example below we show how you could use Sumproduct or Offset to amalgamate source data from irregularly-spaced Excel columns.

Jumping columns in Excel

If you click on the image above you can download the example.

Which is the better solution: Offset vs. Sumproduct?

So you’ve got at least two solutions that will help you jump columns and amalgamate data in Excel, but how to discriminate between them? Maybe it helps to start chanting (what should be) your Excel-modelling mantra right now: “shorter neater better shorter neater better”.

If you click on cell B4 Offset seems custom-made for the jumping challenge. We’re summing data but the last term of the offset formula serves to ‘jump’ columns across. We need to develop that particular input (the column number) somewhere and that’s what cell B3 is doing. The whole solution works but Sumproduct in B5 is definitely shorter, neater and therefore probably better in this application today.

Sumproduct is the super-hero

So often in Excel we have multiple potential solutions for the same problem. If there’s an artistic flair needed on top of our Excel science it’s probably the ability to gravitate towards the shortest neatest solution amongst the maze of potential solutions. Although Offset seems tailor-made for jumping in Excel, Sumproduct will often beat it on the “shorter neater better” test.

We did say that we think Sumproduct is the superhero of Excel formulas!

Read more

You’re reading stories from Financial Training Associates Ltd, the company that will have you going around mumbling “shorter neater better” to yourself once you’ve come back off your Excel modelling course.

Read more