99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Correcting Numerical Glitches In Excel
Thu 7th January 2010
The italicized words above obviously translate as happy new-year, but you may not recognise the language as it is not one of the common European ones nor, indeed, is it a national language at all; it is Esperanto, that international auxiliary language that was developed at the end of the 19th century by Dr. Ludovic Lazar Zamenhof (1859-1917). Esperanto, which itself translates as 'the hoping one', was created in an attempt to alleviate the problems that were caused by the many different languages being spoken by the polyglot population of the Polish city of Bialystok where Dr. Zamenhof lived. These language barriers also bred suspicion and distrust among the multiethnic collection of Poles, Russians, Jews, Lithuanians and Germans, so the development of a common language that could be used by all was seen as a positive step forward and some success was achieved.
The commonly held belief that Esperanto was intended as a worldwide language to be used in place of native tongues is wrong and, indeed, such a development would be universally unwelcome and would come with no guarantee of instilling harmony among nations. The playwright George Bernard Shaw's description of the relationship between Britain and the USA as "two nations divided by a common language" suggests that a single global language may not be the answer to all of the world's problems.
Numbers, on the other hand, are universal, and seven makes seven in any language, so you would think that the need for numerical translations would never arise, but sometimes it does. For although numbers are definite amounts the way in which they are represented is not, and this can sometimes cause problems in Excel. For example, sometimes a comma is used to separate thousands and sometimes, especially if the file has been created on a system that uses a metric standard, a blank space is used instead. If you are using the comma to separate your thousands and you import a file that uses the blank space, then Excel will treat the imported information as text rather than numerical values. A quick way to demonstrate this is to enter 1,000 into a column four times and throw in a comma-less 1 000 once. When you autosum the column you will see that the total comes to only 4,000 as the entry with the space is recognised as text and omitted from the calculation.
There are several ways to deal with this type of scenario including altering your regional settings, using a formula or creating a macro, but a neat trick to get around this obstacle quickly is to select all of the imported data where the space has been inserted and select find and replace. In the Find box enter a single space and in the Replace With box enter nothing at all. This will remove the spaces and Excel will now recognise the data as numerical values.
As the above shows many of the problems encountered in Excel have several different solutions. Knowing which option to choose under particular circumstances can save time and effort, while demonstrating that the user has a good understanding of the program. Being well trained in the use of Excel is a plus point on any CV so I would encourage you to embark on a journey into the workings of this versatile application.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Tutor was extremely knowledgeable, enthusiastic and patient. He managed to make me feel very comfortable asking questions but still completed the full syllabus for the day. Very pleased! :)
Goldsmiths University of London
I enjoyed the course very much. Jens was very knowledgeable, patient and enthusiastic. I feel I have a better understanding of Excel and the way I can use it, and the different ways in which it can help save time and improve accuracy in many tasks at work and at home. I look forward to completing the tasks, which I think is very important in consolidating what I have learnt.
The course today has been highly informative. I have come on this course to refresh my Excel skills, and didn't realise that there was still so much to be learnt. The course was delivered very enthusiastically which made the day very pleasant.