Dynamic ranges in Excel are just cool!  They allow us to highlight parts of datasets; for example - the performance of particular months of a football betting system across a whole season.  Putting them together, however, is challenging.  Two weeks ago, we looked at using the INDIRECT formula to do this – did you manage to get that working?  This week, we’ll do a similar thing using OFFSET, and go a step further by charting the data to create a dynamic visualisation.  There’s lots to do so let’s get started!

Do you need to know both OFFSET and INDIRECT?  It’s not absolutely necessary, but it’s helpful to have both in your toolkit.  I love the versatility of OFFSET, and think it is important to understand the concept because it is so important in Excel VBA.  In the video, I explain step-by-step how to apply this powerful formula, including its last two components – you’ve probably never touched those before.

This is certainly the trickiest formula-building task we’ve undertaken to date on Excel For Football Traders.  It’s a real challenge, even for the experienced developer!  Kudos if you managed to get it working.  Don’t worry if you did not; simply understanding the concept of how OFFSET works together with COUNTIF and MATCH to get the dynamic range is a good outcome for most.  Come back to the formula once your formula building skills have improved further.

As always, put your comments below this video and I will get back to you.

Tools And Techniques