# Thread: numeric formatting without decimal point (XL2000)

1. ## numeric formatting without decimal point (XL2000)

Is it possible to format "floating point" numbers with up to two decimal places, but omit the decimal point when no decimal digits are present? The closest approach that I have found is format code 0.##;0 which gives me

3.83 as 3.83
0.00 as 0
72.00 as 72. [but what I want is 72]
0.60 as 0.6

2. ## Re: numeric formatting without decimal point (XL2000)

I can't see how to do this as a cell format but if you want it for a calculated field you may be able to use the text() function to format it:

=IF(MOD(D19,1),TEXT(D19,"0.##"),D19)

This does have the effect of rounding out the decimal places if used for further calculations though!

HTH

Peter

3. ## Re: numeric formatting without decimal point (XL2000)

Even worse: if the number has decimals, this formula will return a text value. Functions like SUM will ignore text values, but formulas with + won't, so you can get unexpected discrepancies in calculations...

But fortunately, this needn't be a problem at all! Use the original values for further calculations and the formatted values for display only. If desired, the original values can be hidden.

So your solution works very well after all. I like it, for I couldn't think of a number format to accomplish it.

4. ## Re: numeric formatting without decimal point (XL2000)

I had not noticed that functions would not use it <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

Still it made me look again <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

It looks as if I did it the hard way anyway!

=ROUND(D20,2) seems to be a better function

Peter

5. ## Re: numeric formatting without decimal point (XL2000)

Who wants simple solutions when you can have complicated ones? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

OK, Round is much better, but you may still want to use the original values for further calculations, because you lose precision using Round - unless that is what you want, of course.

Regards,
Hans

6. ## Re: numeric formatting without decimal point (XL2000)

The format code that you want is General. See attached worksheet. HTH --Sam

7. ## Re: numeric formatting without decimal point (XL2000)

Sam,

<hr>with up to two decimal places<hr>
If a number has more than 2 decimal places, your Text function will display them instead of limiting to 2.

Oh, and "General" causes problems in international use too: it is language dependent, so in my Dutch language version of Excel I got error values.

8. ## Re: numeric formatting without decimal point (XL2000)

Mike,
If your data is in column A, starting at row 1, the following formula in any other column, and copied down as many rows as you require it, should produce what you've specified:
=ROUND(A1,IF(LEN(MOD(A1,1))>1,2,0))
Cheers

PS: As noted in other posts here, truncating your data at 2 decimal places could affect calculations. If that is a concern, or if you don't want to have extra columns with these calculations on your data worksheet, consider creating another worksheet with the required output formatting for printing and using the above formula to link to the source data on the original worksheet

9. ## Re: numeric formatting without decimal point (XL2000)

This method still will depend on the formatting of the cell no matter what the rounding.
I do not believe there is a way to do what was asked simply. There is no way in EXCEL to create a truly CUSTOM format.
You either:
have to go the "convert" route change the number to a display that "looks" correct, but you must tack on VALUE before any calculation using it
Or
You must have a macro format the cell based on the contents which means it must run constantly whenever calculations are done. it could be set to only change particular cells, but your performance will start to drop dramatically.

If the "values" are only for some "presentation" or display, I would just keep the real numbers somewhere and set up a "display" table which does not use numbers but the TEXT set up to LOOK like what you want and never use those tabled numbers in calculations, only use the original values which have NOT been touched.

Steve

10. ## Re: numeric formatting without decimal point (XL2000)

Steve,

Have you actually tried my solution? It displays the results exactly as originally specified. It also certainly does not require VALUE to be tacked on before using the results in any other calculation.

Further, since Mike was talking about floating point numbers, they're most likely produced via formulae. In that case, replacing the A1 references in my formula with the formulae used to generate the floating point numbers would eliminate the need for intermediate cells.

The cost of using the rounded results, as I acknowledged in my previous post, is that the rounded-off values could affect subsequent calculations. That is why I also suggested having the required output on a seprate worksheet, with my formula referencing the unrounded data.

11. ## Re: numeric formatting without decimal point (XL2000)

Yes, it does MOST of what he wanted. But he did NOT want a rounded value. He wanted to DISPLAY the values but still have all the figures (non-diplayed) for calculations so he doesn't lose his precision. I do NOT think all that he wants is possible in excel.

Steve

12. ## Re: numeric formatting without decimal point (XL2000)

I cannot see that

=ROUND(A1,IF(LEN(MOD(A1,1))>1,2,0))

gives any different result to

=ROUND(A1,2)

Unless there is some variation caues by international setting <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Peter

13. ## Re: numeric formatting without decimal point (XL2000)

Gentlemen,

Thank you for your suggestions. I was hoping that Excel could handle what I wanted directly, but (as Steve pointed out) it appears that it doesn't. However, I adapted Peter's suggestion to set the format based on the results of "casting out ones." This appears to give the look that I want (at least on the test data that I'm using <img src=/S/grin.gif border=0 alt=grin width=15 height=15>). I had to write my own function (dModD) to perform the modulo division on Doubles, since the VBA mod operator and CLng() function both round round floating point inputs, instead of truncating them. That coding was pretty straightforward, though.

The discussion of side effects on the ability to do calculations was also very informative. It hadn't occurred to me that one could get a useful numerical result by "adding" text strings. After playing with it, I was even more surprised that it gave the correct answer when SUM() wouldn't! <img src=/S/shocked.gif border=0 alt=shocked width=15 height=15>

<pre>'if value is within 1 min. of an hour then format without decimal point
If dModD(Cell.Value, 1) < 0.167 Or dModD(Cell.Value, 1) > 0.983 Then
Cell.NumberFormat = "0"
Else
Cell.NumberFormat = "0.##"
End If
</pre>

Another interesting aspect of this discussion is what I left out of my question (because I didn't realize that it was relevant <img src=/S/blush.gif border=0 alt=blush width=15 height=15>). My worksheet is generated by VBA code, using data that were generated by another program. The worksheet's purpose is to format/present and in some cases to graph that data, so calculations aren't necessary. My user is in the US, so the international issues won't matter. It's always good to know the limitations of pieces of code, though, because somehow they seem to get re-used.

14. ## Re: numeric formatting without decimal point (XL2000)

Should

If dModD(Cell.Value, 1) < 0.167 Or dModD(Cell.Value, 1) > 0.983 Then

really be

If dModD(Cell.Value, 1) < <span style="background-color: #FFFF00; color: #000000; font-weight: bold">0.017</span hi> Or dModD(Cell.Value, 1) > 0.983 Then

??

Peter

15. ## Re: numeric formatting without decimal point (XL2000)

<img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
You are absolutely correct. So somebondy read my code after all! Thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•