AMITIAE - Saturday 7 February 2015
A Problem When Using Numbers to Calculate a Simple Total
By Graham K. Rogers
I was out of the office when a student sent me a message on Facebook (their preferred medium of communication) suggesting politely that I had her grade wrong. Her friends did the same work and had a higher grade, she claimed. As there was no way for her to know this, I mentally brushed that off and suggested a meeting at 10:30 am the following day.
When she arrived, I was ready: computer, spreadsheet, notebooks. A quick look at Numbers suggested that the total marks were right for the grade; all the correct cells were shown as included in the Sum; and each of the modules was marked correctly. What could possible be wrong?
She outlined the reasons for believing the grade was too low, so I took the marks and wrote them down in the notebook and, by way of explanation, totalled them by hand. Numbers was wrong. With the student, I examined the entries and saw that the formula was correct (Sum of 5 cells - easy enough), but there was an error of 15 which matched one of those cells.
Part of the Problem Spreadsheet Showing the Formula
If one total was wrong, there may be others, so I went through each student's marks and totalled them using a calculator. One more student was affected: he was in the same writing project as the first student; and the difference was the same (15). I told the student that I would submit revised grades for both and began to look more carefully.
I found that those two cells were formatted differently: Automatic, as opposed to Automatic with Decimals (see below). In these two cases, although the data had been entered and was displayed in the respective cells, they were not included in the Sum. I checked also when I had entered the data.
Of particular note in that Discussion was the point that, with Text, the numbers may appear to be misaligned, which should now be an immediate warning for me.
In some circumstances, therefore, although the data is shown in a cell, it may not register as data and will not be included in a formula. I don't know how anyone would know that. Cells above and below may be unaffected and the user will tend to trust what is displayed. When applying a formula, especially if this is done at the end of the process, it may not be apparent that there is anything amiss with the total as shown.
OS X (left) and iOS Calculators
Graham K. Rogers teaches at the Faculty of Engineering, Mahidol University in Thailand where he is also Assistant Dean. He wrote in the Bangkok Post, Database supplement on IT subjects. For the last seven years of Database he wrote a column on Apple and Macs. He is now continuing that in the Bangkok Post supplement, Life.
For further information, e-mail to
Back to Home Page