This note describes a problem with sorting that has a deceptively simple cause yet it can be tricky to locate. This is when the formula is correct except that it is referring to the incorrect row.
Recently we were shown a spreadsheet like the one shown here from the Scores sheet of Bad Marks example spreadsheet (XLS 49 KB). It had a sorting problem. If you tried to sort the student data, so the averages were in descending (or ascending) order, then the result was clearly wrong. Yet the formula appeared to be right. The 6 scores were averaged and then rounded to 1 decimal place so that an accurate grade could then be assigned.
You may by now have spotted what the problem is but we certainly didn’t for a while! Working out some of the averages by hand might have helped but we didn’t see what was wrong. However, when we used the Trace Precedents tool highlighted below on the Formula Auditing toolbar, then the problem immediately becomes very obvious. (In Excel 2007 the auditing tools are found on the ribbon under Formulas | Formula Auditing).
In retrospect double-clicking to edit the formula should also have shown it was referencing the wrong cells. You may by now have spotted what the problem is but we certainly didn’t for a while! Working out some of the averages by hand might have helped but we didn’t see what was wrong. However, when we used the Trace Precedents tool highlighted below on the Formula Auditing toolbar, then the problem immediately becomes very obvious.
The trace above shows that the formula computes the averages of the scores on the next rather than the current row. It is rather easy to make mistakes like this when entering formulas and in this case another factor made it harder than usual to detect that something was amiss.
The last row of the data is followed by a row of averages of each score. Thus the last average was really the average of the averages. If there had been an empty row before the statistical calculations, then the last average would have been #DIV/0!, which would have highlighted that there was a problem. Good spreadsheeting practice would suggest that summaries and totals shouldn’t in general be placed in the row immediately below the data. Useful spreadsheet features, such as pivot tables, don’t work properly without empty rows and columns surrounding the list.
This example highlights the usefulness of the tracing precedent tool in debugging a spreadsheet where a formula isn’t actually referencing the cells you think it is referencing.