First, thank you to our community member Neil for sending in this question.

If you have a tabulated analysis (for example, a heat map,) you might wish to find the highest value in the table.  In other words, the most profitable system!  If your table is small, this might be simple enough to do without a special mechanism.  

But, if the table has lots of rows and columns, you might want something to tell you quickly what the corresponding row and column headers are.  For example, the most profitable odds bracket.  So, how can you do it?

It’s a great example of the power of Excel modelling.  There’s no single Excel formula that can do this easily.  But, combined together, Excel modelling formulae get the job done every time!  Such is the power of simple Excel formulae working together …

I argue that problems in Excel are often more conceptual than technical.  In other words, formula-building can be done once somebody explains it to you, or after a few ‘trial-and-error’ attempts.  The actual difficulty is understanding what formulae are needed, and why.  Some clear conceptual thinking is required first.

In the video, I break the task down into 5 steps.  Each of the 5 steps is fairly straightforward and achievable with a single Excel formula.  The steps provide a blueprint for working through the task.  Then, it’s just a case of putting the formulae in!  But, the technical stuff is so much easier once the concept is clear – make sure you download the Excel file and work along with me.

Tools And Techniques

-          Planning An Excel Model

-          MAX

-          COUNTIF

-          MATCH

-          OFFSET

-          ADDRESS

-          Conditional Formatting