By Graham K. Rogers

It is a moment that every teacher must hate: despite clear outlines for how marks are allocated, when the grades are published, some students disagree with what they were given. There are a number of reasons for grades below what students feel they deserve, revolving around attendance, effort - particularly effort - as well as output and standards.

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

I made a copy of the spreadsheet, to preserve the original. With the expected regrade, I would have to produce that: the Faculty does not simply change grades without documented reasons. I would also have to face a committee.

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.

As the students were in the same group, I would have entered their marks at the same time. I wondered if they had been the first marks I had entered; or the last. Neither was the case: they were the fourth group in the list I had used to calculate the marks in my notebook. I submitted Feedback to Apple, but have heard nothing.

Although I could find no information about this specific problem in the current version of Numbers (v 3.5.2), there had been a problem with an earlier version, discussed on the Apple forums a year ago. The user with the query was running OS X, Mavericks, and his problem was because the data was entered as text. This was shown on the format panel: the button clearly showing the word, Text.

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.

I have experimented with the problem cells, using the Enter (Return) key to move down to the next cell (no change); but if I remove the data and re-enter, even just moving the cursor to another cell and clicking on that will cause it to be recorded correctly: the Sum is also then correct.

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

I will have to watch the Total cell to ensure that as data is entered, there is a change; or use old-fashioned methods: paper and pencil, the calculator, or adding the numbers in Spotlight.

As a note, a search on Google for bugs+Excel will show that there are a number of problems with the Microsoft product that may also need to be watched for.

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.