What does this Spreadsheet Do?

Chris asked me to help him with a Microsoft Excel spreadsheet the other day. It was a “Profit and Loss” calculator which had been created by someone else.  It didn’t quite to do what Chris wanted and he needed to know how it worked.

Now, the thing about complex spreadsheets is that the author tends to build it up piece by piece.  Working out what the whole thing does needs a good deal of detective work.  Chris was going to have to act like Sherlock Holmes (which I guess made me Joan Watson).

To start with, the Workbook appeared to have four Worksheets in it.  However, we soon found references to sheets that we couldn’t see.  That one wasn’t too tricky.  The author had hidden some of the sheets.  Right-clicking on any of the Sheet Tabs leads to a menu with an Unhide… choice.  There were actually eleven Worksheets in the file and we were able make them all visible in no time.

Where do the numbers come from?

The spreadsheet was showing us Profit and Loss figures for each month of the year.  There were cells to type in “Assumptions” such as Selling Price, Sales in First Month and Expected Growth.  How was it calculating the answers?

Years ago I would have sat down with a a notepad and laboriously followed the chain of formulas backwards to make sense of what the author was trying to do.  In modern versions of Microsoft Excel we have the Evaulate Formula command.

Here is a simpler example taken from our Advanced Microsoft Excel training course:

FormulaEvaluatorA

It is based on a “Car Expenses Claim Form”.  Attendees are asked to calculate the Expenses due using the following rules:

  • The Rate paid depends on the CC of the car
  • A higher rate is paid for the first 500 miles of each claim
  • A different rate is paid for only those miles over and above 500

They are also told “Do not attempt to construct one huge formula in cell C7. Use the cells in column G for intermediate calculations.”

As you can see from this completed solution, the answer in cell C7 is the formula =G10+G11.  What do those cells contain?
The command [Formulas / Formula Auditing] – Evaluate Formula produces this dialog box:

FormulaEvaluatorB

Notice how the G10 term is underlined?  Clicking the Step In button leads to this:

FormulaEvaluatorC

So we now know that G10 contains =G4*G7/100.  What does G4 contain?  As it is currently underlined, clicking Step In will tell us:

FormulaEvaluatorD

That cell has an =IF function in it.  It is checking whether the Mileage Claim (in C4) is over 500.  You see this cell is designed to calculate how much of the Mileage Claim should be paid at the “Rate over 500 Miles“.  Pressing the Evaluate button now leads to:

FormulaEvaluatorE

Can you see that with these particular figures, G4 will work out to be 143?  If you can’t then Evaluate Formula will prove it to you.  This is what the dialog box looks like after pressing the Evaluate button three more times:

FormulaEvaluatorF

Now I’m not trying to teach you how an =IF function works (although I could – see me afterwards).  The point is that the Evaluate Formula command will lead us through the steps that Microsoft Excel has actually taken to calculate an answer.

This is an enormous help when trying to decode a spreadsheet you have inherited.  It has also got me out a jam a few times when a spreadsheet I had created wasn’t doing what I thought it would !

Brown Field or Green Field?

Of course, my Training Course example has only seven formulas in it.  [Just in case you are interested, we use =HLOOKUP functions based on the CC figure in cell C5 to work out the correct rates to use]

The “Profit and Loss” spreadsheet Chris was looking at had dozens of them.  When last we spoke, he was considering whether it was worth the time and effort of trying to understand the original author’s intentions, or start again and create a new spreadsheet from scratch.

In his case, the original file didn’t really do what he wanted it to do, so a “Green Field” solution was a possibility.  You might not be so lucky.  You may have no alternative than plough through the “Brown Field”.  At least Evaluate Formula can do some of the heavy lifting for you.