Excel, decimal places only for decimal numbers 2

[timotai]

Hi all

I don't know if this is possible but I hope it is.

What format could I use or is there a setting so that decimal places only appear for decimal numbers?

E.g.

I am using percentages and in some case it could be 95%, 100%

in other cases it might be 99.98, 98.23

If it is a whole number I want it to show without decimals (99%) if it is a decimal number I want it to show as a decimal (99.98%)

Please help.

All help and advise is GREATLY appreciated

Many Thanks

Tim

 

[xlbo]

I would advise against that as the reason for showing decimals is so that the 10s, 100s 10ths 100ths etc line up so the data is easier to read

I'm pretty sure this can't be done with formatting but also pretty sure that it can be done with formulae...let me know if you really want to go with this and we'll see what we can come up with

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[timotai]

I do want to go down this route but dont really want a formulae in the fields I am actually using. The reason for this is that I am importing data into these fields. Can you think of any way around this

Thanks

Tim

 

[xlbo]

Formatting won't hack it I'm afraid - formula or code is the only way to go


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[timotai]

Lets go code, I am running macros anyway so could include it at the end just to format.

Thanks

Tim

 

[xlbo]

In which case, it's just a loop to test that the 1000ths decimal is zero

application.screenupdating = false
for each c in yourRange [COLOR=green]'amend to be a valid range[/color]
 if int(c*100) = c*100 then
     'no decimal places
     c.numberformat = "0%"
 else
     'leave decimal places
     c.numberformat = "0.00%"
 end if
next c

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Zathras]

You can come close with a custom format like this:
[tt]
#.##%
[/tt]

 

[timotai]

Thanks xlbo for you help it is greatly appreciated. but Zathras has got it. I tried something similar but didnt put the decimal in it. That is great! Thank you so much!


Tim