Often I’ll be looking over a spreadsheet, and I’ll see a value that doesn’t make sense, but I won’t be able to figure out easily where that value is really coming from. I have a couple of tools available to me, and I’m going to walk through the process of finding the source data in the sheet below:
In this case I’m trying to figure out why the price in E13, which I know should be 15, is showing up as 17. This spreadsheet has several tables to the right, and I assume that’s where it gets the data from, but someone else set this up, so I’m not if that’s how it works or not.
I first attempted to use Excel’s built-in trace precedents tool to walk back to the source cell.
Once I do that things look like this:
Now I’m not really sure which of those arrows is the one I want, but if I click inside the formula, Excel puts these colored boxes around the different ranges I’m using in this formula. Based on that, I think I want the blue box with all the numbers in it:
So, based on that I can use trace precedents to go back another level by selecting cell D8 and hitting the trace precedents button again. I get a whole bunch of other arrows now, but they go into one of these tables on the right, so I think I’m making progress:
Unfortunately, now I’m lost. I’m in a table, I’ve got some values here, but none of these are 17. Also, now that I look at this table, I appear to be in the ’sweater’ table, which is probably wrong because I have ‘pants’ entered in D13.
This clearly isn’t going to work for me this way, so I’m going to try using Audit instead of Excel’s built-in tracing. So the first thing I need to do is startup Audit, using the Audit button, and then I’m going to place an X on the price cell. This paints a very different picture than Excel did:
I can see right away which number is getting pulled out of the tables on the right, and this time it is coming out of the pants table, not sweaters. My last step is to change the 17 to a 15 like I want it, and once I do that, my price changes to match:
After playing with it for a while, it seems like Excel’s trace precedents tool just points to the first cell in the range, rather than the cell that gets used out of a range. This is fine for very simple models, but this price calculator uses quite a bit of logic to find the right value in the tables.
Posted by: Matt McLaughlin, CTO, RedRover Software Inc
