We here in Information Services had a problem with one of the telephone charge back spreadsheets. The numbers in a column did not add up. The problem related to the nature of Excel and should be watched in doing Excel spread sheets.

Formatting a cell does not change the value of the numbers in the cell. Help in Excel states:

"You can use number formats to change the appearance of numbers, including dates and times, without changing the number behind the appearance." That is, formating is like putting a mask on a field in POISE. It only relates to how the numbers are presented, not to their value.

For example:

        Number Value    Format as currency      Round and display
                        
        0.124                   $0.12                   $0.12
        0.125                   $0.13                   $0.13
        0.135                   $0.14                   $0.14
        0.165                   $0.17                   $0.17

Sum     0.549                   $0.55                   $0.56

The sum in the second column is not correct. It should be $0.56 if you add up the numbers. However it is not adding up the numbers as displayed but the values in the cells equal to the first column. The sum is 0.549 which formats as $0.55. If we work with currency values equal to the rounded values in column one as in the third column, the sum is $0.56.

This still leaves a problem in getting a spreadsheet column to add correctly. In the telephone charge back problem the issue was round off after multiplying by 30% and 70%. Due to the round off of the values, the sum of the values did not total 100% as in the example above. My solution was to calculate the 30% amount and then subtracting the result for the total to assure that the total was correct. Other situations may be more difficult to solve.

Written by Bill Siders
Director, Information Services

2003-Dec-17 11:23am nancy@nmmi.edu