Decimal Format

[tyija1995]

Hello,

Is there anyway to format a cell in such a way that if I enter an integer then in the cell I see just the integer, then if I enter a decimal (say 6.5) I see 6.5 in the cell,

Currently I can't get the format to automatically change, I see 6 (when I input 6) and then 7 if I input (6.5)

If I add a decimal then 6 shows up as 6.0 when I just want a 6

Hope this makes sense.

 

[Fluff]

You could use conditional formatting.
Format the cells to show decimals & then with Cf use
=int(A1)=a1
and set the format to zero decimals

 

[Joe4]

The default "General" format will only show what you enter, i.e. if you enter 6, it will show 6; if you enter 6.5 it will show 6.5.
The only thing with that format is it will drop any trailing zeroes, i.e. if you enter 6.0, it will show 6; if you enter 6.50 it will show 6.5.

Is that what you are looking for? If not, then I am afraid I do not understand exactly what it is you are asking.

 

[tyija1995]

Thanks for the reply guys, sorry I forgot to mention I am using a % format, so when I enter 6 in the cell it comes up as 6% for example

I want to see 6% when I enter 6, and 6.5% when I enter 6.5

I thought at first to use CF using a . as the criteria but ofcourse 6 (6%) is 0.06 so this won't really work, so the int function will not work too, apologies for this confusing addition!

EDIT: the cell can contain any % between 0 and 100 inclusive.

 

[Fluff]

In that case you could use
=INT(A1*100)=A1*100

 

[tyija1995]

Brill, that has worked! Thanks a lot

 

[Joe4]

Percents are really numbers between 0-1, i.e. 6% is equivalent to .06, 6.5% is equivalent to .065.
So, you can either enter the numbers with the percent signs, like this: 6%, 6.5% or without percent signs like this: .06, .065

If you want to enter them as number like this: 6, 6.5 WITHOUT entering the percent signs, one way would be to add a Worksheet_Change event procedure script to multiply your entry by 100.

 

[tyija1995]

Hi Joe,

Thanks for the comments it is good to know going forward. Fluffs CF formula has worked for what I have wanted so my issue has been resolved. But I appreciate your reply, always good to learn new things!

 

[Fluff]

Glad we could help & thanks for the feedback