numeric formatting without decimal point (XL2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

I had not noticed that functions would not use it

Still it made me look again

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

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

Peter

Reply | Quote

Who wants simple solutions when you can have complicated ones?

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

Reply | Quote

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

Reply | Quote

Sam,

Mike asked for a format[indent]

---

with up to two decimal places

---

[/indent]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.

Reply | Quote

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

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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

Peter

Reply | Quote

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 ). 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!

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

Another interesting aspect of this discussion is what I left out of my question (because I didn’t realize that it was relevant ). 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.

Reply | Quote

Should

If dModD(Cell.Value, 1) 0.983 Then

really be

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

??

Peter

Reply | Quote

You are absolutely correct. So somebondy read my code after all! Thanks.

Reply | Quote