Finding the AVERAGE, MIN and MAX errors

Free Preview

When reviewing an Excel spreadsheet it is important to understand the typical errors people make. Below are ideas on easy ways of finding the AVERAGE, MIN and MAX errors, in order of riskiness, when auditing an Excel Spreadsheet.

It is useful to have a spreadsheet error add in to find all the risky formulas but if you don’t have one you can do a manual search using the FIND tool.

Treatment of Zero and Blank with AVERAGE, MIN and MAX

When considering the riskiness of the AVERAGE, MIN and MAX functions in MS Excel, the biggest risk is actually the cells that it looks at. These functions treat a zero and a blank cell differently, whereas humans tend to think they are the same thing. So a user might enter a zero meaning that it was not there but Excel will treat it as being there and affecting the AVERAGE, MIN and MAX results.

With this one, it is useful to be able to list them but you will only see how risky they are when you look at the spreadsheet.

Finding the AVERAGE, MIN and MAX errors

As shown below (1) is the way it should be, there is no score from judge 2. (2) shows that if a 0 is entered in Score 2 instead of a blank or a word, then the Average and Minimum are wrong. This is because in (1) Excel is only looking at 3 cells to get the average and minimum, but the moment there is a 0, it is looking at all 4 cells. So a quick review of the input cells used will tell you if there is a risk with these functions.

Finding the AVERAGE, MIN and MAX errors

Treatment of numbers mistaken/ formatted as text

Similarly in (3) above, Score 1 seems to have a number but it is formatted as text. In this case both the Average and the Maximum will ignore it, so instead of a maximum of 9.5 it shows a maximum of 9.

Back to: Auditing an Excel spreadsheet > Prioritise which formula to look in for errors