Rounding problem #1: Why is my Lookup returning the wrong answer?

• How can the problem be fixed?

Using a lookup to give grades to unrounded marks may cause problems. This is particularly bad when it goes unnoticed and incorrect grades are actually assigned!  Our example, taken from the Grading Problem sheet (Bad Marks example spreadsheet (XLS 49 KB)) has the simple solution, shown below.

Suppose the average of 6 scores out of 10 is given a grade. The grades are to be assigned as follows:

This is best handled by setting up a lookup table as shown here. Remember that the looked up column (Average Score) contains the first value in he required range. Thus the 7 in row 5 actually matches 7 <= Average Score < 7.5.  The range A4:B7 has been range named gradetable.

The formula shown below for I3, =VLOOKUP(H3,gradetable,2), correctly assigns a Pass grade to 7.4.  However, the next grade of Pass for an average score of 7.5 is wrong.  It should of course be Credit. What is wrong here?

Going back a step and checking the formulas in column H shows a straight forward average formula.

The Average column has been formatted to 1 decimal places but what values have actually been calculated?

Column J contains formulas copied down from = H3 in cell J3. This time we've formatted to  show 3 decimal places. The problem is now apparent.  In row 4 the value actually looked up is 7.483… not 7.5 as displayed  in H4. Hence the lookup function in I4 returns Pass not Credit. Similarly the value looked up in row 9 is 6.967… not 7.0.  Both of the looked up values are (just) below the thresholds at which the grades change so it is no wonder the grades assigned in these rows are “wrong”. This problem doesn’t occur elsewhere because the looking up of the actual calculated values gives the same results as would result from looking up the displayed values in column H.

How can the problem be fixed?

Before the average scores are assigned a grade they need to be rounded to 1 decimal place. It is not sufficient just to format them to 1 d.p. as the actual stored value is the full calculated value as shown in column J. The rounding could be done in column I by changing the formula for I3 to =VLOOKUP(ROUND(H3,1),gradetable,2) and copying this formula down the column but there is a better solution.

We prefer to do the rounding first as shown here. The stored values in column H will now be exactly the same as displayed values because the formatting and rounding are both to 1 decimal place. Because the rounding has already been done then the formula =VLOOKUP(H4,gradetable,2) now returns the correct grade of Credit because H4 contains the value 7.5. Similarly, the grades in the rest of column I are now correct.

The problem discussed here is an example of what we like to refer to as WYSINWYG  “What you see is not what you get!”  A common problem in spreadsheets is that there appear to be errors, usually small, in some calculations. Very often this is because of the differences between the actual values stored in cells and their displayed values.

Note: For those of you who are too young, or too new to computing, WYSWYG – “What you see is what you get” was the catch-cry in the 1990s when computing moved from text based DOS formats to the graphical user interface Windows environment.

There is a feature called Precision as Displayed that automatically rounds cells to match their formats. In general we don’t recommend its use as it is not obvious when it is turned on.  If you want to know more read “Change the precisions of calculations in a workbook” help topic in Excel 2003 or “Change formula recalculation, iteration, or precision” in Excel 2007.