Select Page

Last week we were in conversation with a training delegate who wanted to ‘bump’ or ‘slide’ data in a model to the right to accommodate project delay. Normally we’d think of Excel’s Offset formula for this job. Offset is perfectly designed to ‘slide’ data and incorporate project delay.

### Elements of the Offset solution

The Offset solution definitely works in this case, but it was starting to become a little too complicated for our liking. The Offset solution sees us:

• Adding some blue input cells and including data validation so we can vary them easily;
• Comparing the start date (the blue cells C5:C9) against the model date in row 14 – look at cell D16;
• Using Offset to ‘slide’ the start date by the delays in cells D5:D9;
• Evaluating that result (TRUE, FALSE) and multiplying by the contract amount in cells B5:B9;
• Adding an Iferror for the times Offset gets confused;
• Using some green conditional formatting to help us see what’s going on.

The fact that we’re taking so long to explain the Offset solution tells us that maybe there’s a better way!

### In Excel simple is good

Working with Excel we find ourselves on a never-ending quest to find the simplest most elegant solution to a financial modelling problem. So we put our thinking caps on and simplified things further. Employing a tiny bit of logic allows us to dispense with Offset if we want to. It’s possible to ‘slide’ data and incorporate project delay without having to touch Offset. Our Excel formulas instantly become shorter and neater. Surely that’s a wonderful thing!