Correct In, Garbage Out

GIGO, or Garbage In, Garbage Out, is a term that was devised way back in the early days of computing to remind us that if we put incorrect information into our machine, then we will get incorrect information back out. This may seem rather obvious, but you'd be surprised at the expectations some people have of computers. The man generally acknowledged as the inventor of the computer, Charles Babbage, was more than once asked of his new machine, if the operator puts the wrong figures in, will the machine still provide the correct answer? Babbage gave a splendid summing-up of such people's train of thought when he said

"I am not able rightly to apprehend the kind of conclusion of ideas that could provoke such a question."

In other words, are you nuts?

So the day of the computer that can automatically detect when we are entering incorrect data is still some way off. However, there are occasions when using Microsoft Excel, that the information you type in is not what comes up on the screen; in short, Correct In, Garbage Out.

What I am referring to is when you need to enter a fraction, say five-eighths (5/8), into a cell, but what comes up when you hit enter is a date, in this case 5 August. The reason for this is that Excel assumes that, because of the characteristics of the data typed in, you wish to enter a date. It can be rather frustrating, as the problem won't go away without taking appropriate action, and you need to see five-eighths in that cell, not a reminder of your mother's birthday.

To eliminate the problem you must apply a fraction format to the cells. Let us say, for example, that you need to paste a column of fractions into your worksheet, and that these fractions go up to hundredths (e.g., 25/100). In order to see these fractions as they should appear, do the following:

Select the column that will take the fractions, and right click.
Select Format Cells and in the Number menu, then select Fractions in the Category box.
In the Type box, select Up To Three Digits (this is because some of our fractions are in hundredths. If all of the fractions were below hundredths (e.g., 50/80) then you would select Up To Two Digits, and so on.
Click OK

If the fractions are to be displayed for information only, and are not intended to be used in calculations, then you need only select Text from the Category box in the Numbers menu, and this will ensure that your fractions are not converted into dates.

Of course, you may go through your entire life without ever encountering this problem, but bringing it to your attention does demonstrate that, as proficient as Excel is, it does have the odd foible that you may come across by accident, and be lost as to how to proceed. Enrolling on a training course is one way of gaining as much knowledge on the workings of Excel as is possible, and learning how to deal with problems such as the one above, where a few simple steps stopped Excel turning your fractions into dates, and giving you Garbage Out.