You might also be interested in
Replies (35)
Please login or register to join the discussion.
Software is the way to go
The problem with Excel is that it's too easy to make mistakes, then with complex spreadsheets it's nightmare to find them (assuming you even know they are there)
I agree with a previous poster, that software is the way to go, although the Sage product isn't called Winforcast any more, it's now Financial Forecasting
http://www.ledgerservices.co.uk/financial_forecasting.htm
Excel - has it good and bad points
The initial point about receiving a printed and signed budget that contains an error must mean the responsibility lies with somewhere around the people that signed it.
On all the other comments: I work with Excel every day and quite often with Sage Winforecasting. The good thing about using an accounting package is that it enforces double entry and so numbers can't be missed out of a calculation.
However it does not have the flexibility that Excel has for charting, presentation and the usage of macros/VB.
There is no one single perfect solution, but if the user is competent and diligent then Excel should be adequate.
regards
Richard
www.rsrconsultants.com
Avoiding £30,000 spreadsheet construction errors
The problem with cross-checks is that the cross check may also ignore text which looks like a value.
I always advise people never to construct a formula without knowing what the result should be!
How can you ensure this? Simple, always test every formula with simple 'dummy data' which will ease the problem of spotting an incorrect result.
Incidentally, in later versions of Excel '=A1+A2+A3+....etc' should indicate an error if a 'value' is actually text (or a Label in Excel terminology).
Sage Winforecast
As a Chartered Management Accountant I have in the last 25 years prepared Budgets,Projections and Cash Flows using at first,manual, and then Lotus and Excel spread sheets.
Since I became a Sage dealer I have used and sold Sage Winforecast.Although I have a vested interest in selling the product, I highly recommend this Sage software product to lessen the possssibility of this sort of mistake happening when Budgets are prepared.
I know from experience as a Management Accountant that panic that sets in when you get a phone call from some anaylist pointing out a mistake in the spread sheet forwarded by you in a Glossy bound presentation folder.
Kind Regards
Dennis Mc Court
Spreadsheet Errors - The wrong tool for Budgeting
Just imagine how many more errors there are in budget spreadsheets all over the UK! KPMG's work in this area showed over 90% of speadsheet systems had an undetected error. It is no wonder there is great interest in commercially developed budgeting systems with a central database and web data entry and reporting.
No more errors....
...just use Sage Winforecast for a few hundred pounds from http://www.accountingsystems.co.uk
EXCEL FLAW OR EXCEL FEATURE
SOME WOULD SAY IT’S AN EXCEL FLAW, THEN AGAIN SOME (THE MORE IT KEEN) WOULD SAY IT’S EXCEL FEATURE!
YOU DECIDE!
Nuff Said!
If these planks have a shortfall in their budget why not sack the pratt who caused the shortfall in the first place because he/she didn't understand a basic Excel function.
Surely the most sensible solution offered so far? Presumably Excel Idiot has alraedy resigned thus avoiding my radical, but nevertheless brilliant budget balancing solution?
Only complete bottoms will respond to this . The thread is closed.
Big Sean Canary
VBA Alternative
Just to add to the variety of work arounds, here is a VBA alternative:
Sub NumericText()
Dim Consts As Range
Dim Forms As Range
On Error Resume Next
Application.ScreenUpdating = False
Set Consts = Selection.SpecialCells(xlConstants, xlTextValues)
Set Forms = Selection.SpecialCells(xlFormulas, xlTextValues)
For Each cell In Consts
If IsNumeric(cell.Value) Then cell.Value = Val(cell.Value)
Next cell
For Each cell In Forms
If IsNumeric(cell.Value) Then cell.Value = Val(cell.Value)
Next cell
End Sub
This procedure changes the 'numeric text' cells in a selection to numbers. If you just want to highlight the offending cells by making their background colour red, replace the two instances of 'If IsNumeric(cell.Value) Then cell.Value = Val(cell.Value)' with 'If IsNumeric(cell.Value) Then cell.Interior.ColorIndex = 3'
David Wagstaffe
PAYExcel - The Office Payroll Solution
http://www.payexcel.co.uk
Had to comment...
As a public critic of accountants' obsession with Excel, I just had to come back on this one.
It's interesting to see the twin strand of these comments. One is from the ardent Excel fans, one from lay users.
To blame either the user, or the software is to completely miss the point. Those of us who use Excel every day to manipulate data are well aware of the flaws in the process, and the techniques you can use to trap these flaws.
To suggest, as some have, that Excel never gets the import process wrong, is at best naive. To believe that a spreadsheet provided by someone else will always give the right answer is equally naive.
Excel, and its rival spreadsheets, are personal productivity tools. Get out of this arena, and the problems are inevitable.
Importing text data into excel
I had a similar problem importing text data from a time recording system into excel. The figures remain text data even if you re-format the cells.
The solution is to enter the number 1 into a blank cell, then Edit Copy that cell and highlight the imported text cells, Paste Special and click multiply under the Operation option.
Whose Spreadsheet?
Mmmmmm..difficult one. Who's got control? If I'm providing a model for someone else to use, I protect all the cells except the "entry" cells, and I add a password so that unauthorised changes can't be made. I then take responsibility. If my client asks me for the password so that he can amend the model, I ask him to release me from that responsibility in writing.
Basically, although not in any way suggesting a legal position, I'd like to say that if the user has access to the formulae and the formatting, then the writer of the model has no control over its end use, and therefore could not be held liable. If this workbook had (a) been formatted correctly, (b) had the entry cells ONLY unlocked, (c) been subject to password protection, the end user couldn't have changed the cell format - the unlocked cells would accept data, but the Format Cell option would be disabled. If the thing were delivered with this kind of fault, it might be easier to determine where the responsibility would rest. Deliver it wide open, and anyone could change it in theory...
So much of this is down to procedure - what we have here is a classic case of uncontrolled use of "programmable" technology. Inside an organisation it's OK - the worst that can happen is that someone gets a good kicking. Once it becomes inter-organisational, it has the potential to be very weak, and likely to cause a dispute. But this is my business - so I'm bound to say that, I suppose!
Data validation
To go back to the technical issues of highlighting unexpected content in an Excel spreadsheet, I believe the data validation feature in more recent versions of Excel has already been mentioned. Just applying appropriate Data Validation criteria doesn't highlight existing data that contravenes the criteria. However if you select the area concerned and bring up the Auditing Toolbar, you should find a button that will circle data that doesn't comply with any data validation you have set up for the selected area.
Simon Hurst
The Knowledge Base
www.tkb.co.uk
Set validation in Excel to prevent numbers being treated as text
If you go to Data, Validation, you can set allowed ranges of data for selected cells. If you only allow whole numbers or decimals, any text entered in your range will throw an error.
While I agree with the "blaming the hammer" argument, I think Excel should indicate if it is excluding any cells from a summation because they have text values as, obviously in this case, it's something users should be aware of.
EXCEL Error warning Misleading
Your headline was totally mis-leading. It gave the impression that there was an inherent fault in EXCEL. That was NOT the case. If data is entered as TEXT it will not (and SHOULD NOT)be added-up in the total. What next, I suppose the school governor will expect a figure entered in alpha (eg One) to be added in the total! Don't blame EXCEL and send accountants into a panic, blame it on STUPIDITY of the user!
Do LEAs provide "Best Value"
It has been interesting to see the ripples caused by my first posting to the site.
The question posed that I would still like an answer for is the one I have quoted below.
David Cornes , 07 March 2001 @ 12:35 AM
Whose Spreadsheet?
As chair of governors finance sub-committe of a local primary school ...
However the LEA also provide an annual Spreadsheet with which to calculate our annual delegated budget. This I have to accept as gospel as I have not got access to all the underlying regulations. If this spreadsheet is wrong who bears the responsibility?
The impression that I had gained was that because it is a printed out version of the spreadsheet that is signed by the Governors then it is their responsibility.
However the LEA provides the spreadsheet, and also the requirement to apply "Best Value" practices. I would like to know if the fault could be proved to lie with them where the responsibility lay.
Change the ODBC options
When using the ODBC facility to bring data into Excel from an external database, there is an option to "Auto Format" the data (Data-Get External Data-Data Range Properties). It is a good idea to switch this option off so that all formats that you apply to the spreadsheet are preserved. This will avoid a repeat of this problem.
Coloured text - reply to Will Heard
Will Heard's solution, to "automatically" display text in a different colour, can be achieved by highlighting an appropriate range, possibly the whole worksheet, and using conditional formatting from the Format menu. A condition such as "not between
-1000000000000 and 1000000000000" should capture all the text.
Don't be too quick to criticise users
As anyone who has tried to import data from an external accounting system into Excel will know, it frequently, inexplicably and inconsistently expresses numbers as text. The likely cause in this instance is that parts of the spreadsheet have been imported and parts input directly. (I would strongly advise a careful checking of any other spreadsheets tha may have been cut and pasted into this one).
The reality is that Excel, in common with many other packages, is not entirely reliable, or bug free - and, as it continues to evolve, probably never will be. Users should always be diligent about auditing their spreadsheets and recognise that the larger it grows, the older it is and the more people access it, the more likely it is to contain errors - human or otherwise.
blame it on lack of training
I regularly use Excel, and have yet to come across errors ion reports for which I (or another user) am not directly responsible. Although I regularly import data from a variety of different applications I always make it a point to verify that numeric values are input where required. This is easily done by going to DATA and then selecting TEXT TO COLUMNS.
Oh Dear, Oh Dear
Whilst most of us are aware that Excel is somewhat flimsy (Isn't Excel a brand of loo roll too?!), even I, an ardent Lotus user, fail to see the problem here.
The software did it's job, someone did not.
Any possibility that a business studies teacher designed the spreadsheet? (lol)
Good to know that tax payers money is being well accounted for.
Learning the hard way
The thing about most "out of the box" applications is that the user knows just that little bit less than he needs to know at any point in time. If you're using a tool like Excel to calculate an answer that you're going to rely on, you must at least include a reasonableness check, if not a comparative, i.e. the good old across and down cast. The thing will only do as it's told!
If a numeric cell is formatted as text (Format Cell Number) with a numeric value already in it, then the Sum function will include it UNTIL the cell contents are changed. The use of + is not affected by this. That will add in a text value as long as it's a number expression. If on the other hand, you replace the "text" number with an alpha character, then a totalling cell that uses + to include this alpha value will return #VALUE!. I'd suggest it's a case of Microsoft trying to be helpful, but not keeping everyone happy all of the time. SUM and + are not related.
Formula example =IF(ISTEXT(A1)=TRUE,VALUE(A1),A1), will guarantee to return value for cell A1 whether it's numeric, text or label (i.e. preceded by a single quote).
Also - if you're extracting data from a database via a query or ODBC link, or a Microsoft Enterprise tool like Data Transfer Services, then the format of the destination cell in the spreadsheet will depend on the column type in the database. So if it's, say, a VARCHAR column, you'll get back a text string. If it's a SMALLINT you'll get a number. If it's DATETIME you might get the date number or the actual date.....
Motto - Excel's great for presentation of data. The minute you enter a formula, you're at risk. First question - what's the impact if I get this wrong? If you get a model from someone else....think it through!
Work around solution
I do a dump from database to excel as part of my monthly accounts consolidation.
I import 2000 rows and one particular column of numbers always shows as as text. I use =a1+1-1 and copy down the formula to return the number value for each cell. My first attempt was =a1 but of course excel treated the new value as text too.
Excel problem at School
It's the old adage; Garbage in, Garbage Out.
Since Excel is a numeric based piece of software, why would anyone enter numbers as text. It just isn't logical!
That's why I always check formulae on spreadsheets I get from other departments.
It's not Excel's fault. It's just dumb users.
Allow us a bit of journalistic licence
David - we could argue semantics here, since the flaw was contained in an Excel spreadsheet - and there is no suggestion that the program is at fault, as the story confirms - I felt justified in using the phrase "Excel flaw". Headlines are meant to be short and snappy summaries of the story that catch readers' attention. It seemed to work in this case, since you made the effort to read the item.
2. Malcolm, I'm sure you've run across all the other MS Office Wizards - the things that insist you use a document designed by Microsoft rather than just give you a blank page to start from. My guess is that the formula Wiz probably steps you through creating a formula... but perhaps one of our human Excel wizards can tell how to activate it in a spreadsheet.
Headline on Story is misleading
Your headline implies it was a problem with Excel.
It is not an Excel flaw that caused this problem, but a user lacking excel knowledge.
Risks of software
This problem, namely 'system behaves differently to users expectations' is a common theme in software development.
Did Excel (in this case) deviate from the specified and documented behaviour ? it seems not, in which case the cause of the 'error' lies somewhere else.
In fact it lies in the lack of formal acceptance testing of the system, this problme isnt confined to Excel or Office, all software systems carry this risk.
If you skip formal design and/or testing then this is what can happen.
Whose Spreadsheet?
As chair of governors finance sub-committe of a local primary school I prepare monthly budget reports using my own Excel spreadsheets. These are self-balancing as they have month and year-to-date Budget, Actual and Variance columns and check to the monthly data keyed in from the reports provided in .pdf form by e-mail from the LEA.
However the LEA also provide an annual Spreadsheet with which to calculate our annual delegated budget. This I have to accept as gospel as I have not got access to all the underlying regulations. If this spreadsheet is wrong who bears the responsibility?
Will Heard
I think Excel is a superb tool but then I only use about 1% of its capabilities and have not come across this problem before.
Is it not possible to command excel (automatically) to display entries it considers to be text in a designated colour on the screen (but print out in black and white) so at least you have the chance to spot the problem even if you are not so assiduous in cross checking? Would this help to get over the problem
If so please would someone tell me how. If not then could the excel people do this? Or am I such a novice at excel thet this question just displays my lack of understanding?
Accounting Web flaw blows 5 minute hole in days work!
I read this piece to gain valuable information on a newly discovered software glitch, but only found a report on someones poor attention to detail. The content of the story is similar to "faulty pen writes innacurate figures on tax form" This one certainly deserves to be "..lost in the sands of timek .." (sic)
ATD in Excel
ATD - Attention to Detail!!!
Suggest checks/cross-checks are incorporated to remove the chance of user error & users are sent on Excel courses. This is not an Excel problem but a user problem!(I also viewed this article in the hope of gaining some valuable knowledge)
Excel Data Management
I work with enormous amounts of data coming from varying sources, and going into Excel. If you are ever unsure about the validity or constancy of the data, i.e. a mixture of text lines and value lines, there are a couple of easy actions you can take to avoid such errors.
The first action shows you where to find the text line. When you press the autosum icon to sum a line of figures, the formula will only sum values. It will stop when it hits a text line, therefore you would have to override the autosum and highlight the figures you require summed. E.g. you wish to sum A1 to A8 but A3 is a text value, if you press autosum it will only highlight A8 to A4.
The next is to use the '=Value()' formula. If you run this formula alongside the data it will automatically change all text value into true values. Then you can overwrite the original data with true values using the 'copy' and 'paste special' functions.
A tip I learned the hard way: Always check your data before continuing to manipulate it - it will save you hours later!
Re Excel Errors
As the cowman's expression goes both Peter Wolstenholme and Paul Mitchel have got the wrong end of the stick. The Sum Formula calculates correctly in that it adds up numerics. A 10 in text form is just that - a bit of text. All spreadsheets, Lotus 123,Supercalc etc., do likewise. The real error, which thanks to Paul I am now aware of, is the use of =a1+a2+a3 which gives an incorrect numeric answer when a text "number is in the cell(s).There are worse problems in Excel than this - has anyone ever used the File Combining procedures which are utter rubbish and can introduce numbers/formulae to other formulae in spreadsheets.Thus destroying them.
EXCEL "IS EASY" BUT GET TRAINED
The story goes to underline the dangers of recruiting or applying personnel on the basis that they "know Excel".
I thought I was an expert until recently when I attended a course and learned more about the many tools that are available within Excel. I'm confident that, had the school made use of the Auditing and Data Validation tools, they would have prevented the errors happening or found them out much earlier.