Exponential Problems in Excel

Let's call this installment, "The Mysterious Case of the Vanishing Parts." (Read carefully -- that's paRts, not paNts.)

And before I begin this sad story, remember to add your Excel question in the comments for Friday's Excelerators Quiz post, so you have the chance to win a shiny new monitor.

Strange Formatting

Last Friday, I was working on a client's Excel file, revising some VBA code that splits a list of manufacturing parts into multiple columns, strips a couple of characters off the front of the part name, and copies the results to another column.

It seemed to be going well, until I got an email from my client, saying that some of the part numbers looked funny. He included a screenshot, and indeed, those part numbers did look odd. Here's an example, using some dummy data.

ScientificNotation01

Scientific Notation Formatting

"Aha!" I thought. (Yes, I actually talk to myself like that. ;-) ) Those parts were all numbers, so Excel just formatted them as Scientific Notation. I could simply format the column as General at the end of the macro, to make them look right.

Unfortunately, it wasn't that simple. When I clicked on one of the affected cells, the formula bar showed 220 as the actual part number. So, if I changed the formatting to General, 220 is the part number that would be copied to other cells, later in the macro.

ScientificNotation02

However, after a bit more investigation, I found that the original part number wasn't 220, it was 22E1. Close, but manufacturing might be adversely affected if Excel starts making up new part numbers!

Why the Part Number Was Changed

Because the original part number (22E1) started with numbers, followed by the letter E, then another number, Excel interpreted it as a number in Scientific Notation. It converted that number to Excel's style of Scientific Notation (exponential) formatting – 2.20E+02.

I'm sure Excel was trying to help, but that creates problems, just as it does when Excel changes 6-10 to a date for you, without asking.

The workaround to this unsolicited help is to force the data to be recognized as text, as Microsoft explains in its article: Text or number converted to unintended number format in Excel.

Fixing the Problem

In my client's macro, instead of formatting the parts column after copying the part names, I added an apostrophe at the start of each part name.  That left the "E" parts in their original format, and the problem was solved.

Here's the formula that is added in the VBA code:

.Range("D2?).Formula = "=IF($A2<>$A1,""'"" & $U2,"""")"

ScientificNotation03

Scientific Notation Explained

If you'd like to know how scientific notation works, in fairly simple terms, you can read this article: Scientific Notation

And for an even shorter and simpler description, here's a short video in which a math teacher explains scientific notation. And remember to do your homework!


_______________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

4 comments to Exponential Problems in Excel

  • What a studly Math teacher. I know I'm getting old when the Math teacher looks too young to be one.

  • Really? I didn't notice, because I was concentrating on the chalk board. ;-)

  • Jenna

    I am trying to turn a data set that includes one set of very long numbers (transaction numbers for a retailer) into a pivot table. I format the column with the long numbers as text in the raw data sheet, then when I create the pivot table, those long numbers automatically turn to scientific notation and numbers that are unique on the raw data sheet are now grouped together. How can I avoid this?

  • Hugo

    Changing numeric cells to text format is not enough.
    You must re-apply the input addionally.
    You could copy the whole column and paste it again, specifying "text only".

    This problem is not linked to pivots, but overall to referencing auch cells.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>