Share this content
0
8
6594

Excel - inconsistent conversion of values

I am a recently appointed school governor. The LEA last year provided an Excel file for schools submitting budgets. There has been a problem because Excel has converted text values when using the plus sign (+), but has not converted text values when using @sum.

The school have asked me if they can take any further action as a result of this which has left them £30,000 short of funds. I do not have particular forensic skills to know how the values in two cells were entered as right aligned text values rather than as numbers.

However in the column two to the right where there was a formula using the value which used the plus sign there was no indication of the problem. The total of the column containing the cells used the @sum formula. Sadly it was this lower total which rippled through to the budget submission.

I can see that Microsoft have documented the conversion "If you enter a different type of value than is expected, Microsoft Excel sometimes is able to convert the value." Why isn't there consistency?
Peter Wolstenholme

Replies

Please login or register to join the discussion.

How about a check column?
If my staff have the habit of entering figures as text then I would, in a separate column check the numbers by entering the formula:

=IF(ISNUMBER(A1),"OK","ERROR")

Where A1 is supposed to be the number or text. This formula can be amended to exclude "Error" as simply "". This way one can browse through quickly figures which are entered as text.

The formula can be copied down as far as needed.

Hope this helps.

JAy T

Thanks (0)

Thanks to Paul and Neil
Thank you very much for your contributions.

And thank you Andrew and Jan for getting the ball rolling.

It appears that some of the top of my original question has gone missing. I hope this thread still makes sense to those reading it.

Of course when I check things I discover that my grounding in Lotus was so solid that I say @sum regularly (and in my original question) when I mean =sum when I am talking about Excel.

I have written a letter from the governors to the LEA about this at the governors request, and the school is hoping for some recompense. If only that loan applications that they make up to the value of the error are "nodded through".

I find these cells with what look like values but are treated as text values quite often in my own work because when I extract transaction data by ODBC from my accounting system into Excel it does this with account numbers and cheque numbers/transaction reference numbers.

The most obvious deficiency I could see was the lack of cross-checks, and we are looking forward to seeing whether this years version of the spreadsheets contain any.

Thanks again

Peter W.

Thanks (0)

Responding to Neil's comments
If you had ever worked in a commercial business, you would know that exactly the same thing occurs: spreadsheets are used from one year to the next, modified, messed-with and generally mangled. Users change formats, links, calculations, etc., with no concept of any problems they may be causing. I agree you on that front.

However, I still feel that in a case like this, somebody was responsible for issuing a budget spreadsheet which worked properly, as they certainly would be in a commercial organisation. Presumably Local Authorities have finance departments of some description, and the employees of those departments ought to be reasonably competent professionals (correct me if I am wrong). If not, perhaps the authority's training or recruitment practices should be called into question.

My point is this: the spreadsheet will only do what it is programmed to do. I think it is too easy to blame the software for a mistake that was made by a person.

Thanks (0)

Paul, again
Yup, worked in commerce, industry, practice and now LA, and in reality no one sector seems any worse than any other. Documentation in spreadsheet design is appalling where ever you look.

I would hope that LAs do recruit professionals, otherwise that's me finished!

Still, don't take my comments too seriously. We can have a smile, now and again, even if we are accountants!

Thanks (0)

I don't think your problem lies with any file conversion or data
...rather, the spreadsheet must have been created with the two rogue cells formatted as text. Excel will convert an "@sum" to "=sum" without any difficulty. If you try to sum a range which is all formatted as text (or where a leading apostrophe has been entered before the number - this tells Excel to treat the number as text), the formula will return a zero, as it cannot calculate based on text. Unfortunately, simply re-formatting the text as a number does not work - Excel continues to see the entry as text until you actually edit the cell (press F2 and enter to do this).

As you have realised, your problem has occurred because only part of the range was wrongly formatted and the text entries have been ignored by the total formula. Excel is consistent in as much as if the total formula had read "=A1+A2+A3", the correct total would have been returned (as noted by yourself). In this formula, Excel has the opportunity to look at each cell individually; regardless of the fact that, say, A2 is formatted as text, it knows it should be adding up the cells where they contain numbers. On the other hand, if A2 contains a word, it will return "#VALUE". This is what Microsoft mean by "sometimes converting the value". However, when you use "=SUM", it just ignores the entries it considers to be text. This is so you can type words in a range but still add up the numbers in that range successfully – useful in many ways when buliding spreadsheets, but it carries the risk of the error you have discovered (to test this, create the SUM formula from the Formula Wizard: it explains what is and is not included in the formula total).

I would try to get hold of a copy of the original blank budget spreadsheet – check the formatting of the rogue cells – if they are indeed formatted as text, the blame for the error lies with whoever created it at your LEA.

Thanks (0)

Responding to Paul Mitchell
If you'd ever worked in a local authority, you would know that spreadsheets have a life that is independant of anything that happens in the world as we know it.
By the time the sheet has been modified, linked and cross linked several dozen times, the author of any of it (and sometimes the original purpose) is lost in the shifting sands of time.

Thanks (0)

Spite by Microsoft?
I think you are probably using Excel 97/2000 and trying to convert a Lotus 123 file?

Sadly, although Lotus 123 and Wordpro can read Excel and Word files, the functionality is often lost going the other way- indeed, I've never managed to convert a Wordpro file to Word.

One possible solution to the problem is to use Excel 95 or an earlier version to do the initial conversion, as these seemed to work fine. Alternatively, you might try saving the spreadsheet in 123 as an Excel file.

Thanks (0)

An assumption of correctness
People have often asked why I cross check calculations in computer generated documents. Perhaps the audit habit dies hard, perhaps it coming from the slide rule generation where a reasonablness check is always required.

There are a number of tricks you can use to see how numbers have been imported, but these involve having a supicious mind from the outset. With your specific problems you can:
1) Remove the column alignment to show up rogue text entries
2) Search for @ in the spreadsheet

In general, you just have to be careful in importing/exporting data into Excel. As you have found, it can and will go wrong.

Thanks (0)