#,###

April 26, 2009 9:21 AM

I need a custom number format for Excel that only gives me a decimal when I need it.

What I have: a list of numerical data from 0 to, say, 9999. I want it to display with commas (ie "1,234"). Easy enough. But some of these numbers have a .5 after, as in, "1,234.5". I want to tell Excel to display the numbers, with comma, and display the .5 only when required. I do NOT want it to use a .0 for the integers.

I can get close to what I want with the format "#,##0.#" but then the period is on every number, which is also undesirable.

There must be an easy way of doing this, but I'm tired of trying to find it. Thanks in advance!

posted by lou to Computers & Internet (5 answers total)

Doesn't excel have conditional formatting? If so, you can use the MOD function to check if the number is evenly divisible by 1 or not.

So your formula might look something like =IF(MOD(A1,1)=0,*format1*,*format2*)

I don't have excel handy at the moment, so I can't check this out and provide a complete answer, but it might give you ideas on where to start searching.
posted by chrisamiller at 9:30 AM on April 26, 2009

Best answer: I couldn't see any way to do this with the custom formats in Excel 2003. If you don't need to use these in further calculations and just want to see them this way for display purposes, you can an IF() function to select one of two different TEXT() format strings depending on whether the number is an integer or has a decimal to display. Put this in the next column over and hide the original column and it will look like you want. If your numbers are in column A, put this in cell B1 and copy it as needed:

=IF(MOD(A1,1)=0,TEXT(A1,"#,##0"),TEXT(A1,"#,##0.0"))

If you *do* need to use these numbers in further calculations, just make sure other formulas refer to the hidden column A and not column B with this formula in it, since the numbers are converted to text by this.
posted by FishBike at 9:38 AM on April 26, 2009

Heh, great minds think alike, chrisamiller. Or is it fools seldom differ?

Anyway, yes Excel has conditional formatting but it only appears to be able to make things like fonts, colours, and cell patterns conditional--not the number formats. Custom number formats can also have conditions embedded in them but nothing complicated, only checking whether the number is less than, equal to, or greater than a constant. No way to check if it has a decimal or not.
posted by FishBike at 9:40 AM on April 26, 2009

You can evidently do it with a workbook macro, too.
posted by cerebus19 at 9:42 AM on April 26, 2009

Response by poster: Guess it's not so simple after all. But that did the trick! Thanks chrisamiller and FishBike.
posted by lou at 10:56 AM on April 26, 2009