Percentage problem

[ecawilkinson]

Hi,

I have not used Excel in a few years and am having a problem displaying a percentage correctly in a column of figures. I want cells that will show the percentage exactly as it is, no matter how many decimal places, e.g. if the cell content is 5 then I want to see 5%. if it is 5.1, I want to see 5.1%, if it is 5.12, then I want to see 5.12%.

thank you,
Chris

 

[live_excel]

First select cell > then right click > format Cell > select percent > Select decimal 2 to 0 > click ok

 

[ecawilkinson]

Percentage Problem

Hi,

I am trying to get a cell to show a percentage with a variable amount of places after the decimal, e.g. if the cell contains 1.23 then I want the cell to display 1.23% after formatting. However if the cell only contains 1 then I want the cell to display 1% after formatting. I tried right-clicking the cell then Format Cells...>Percentage and changing the number of decimals to 0 but that truncates to 0 decimals places no matter how many there are in the cell contents. I tried the custom formula #.#% but that show a trailing decimal point if the cell contains a whole number such as 5. Any help would b much appreciated as I have tried a few different ways and none of them worked. I'm sure the answer is very obvious, I just cannot see it.

thanks,
Chris

 

[live_excel]

Re: Percentage Problem

First select cell > then right click > format Cell > select percent > Select decimal 2 to 0 > click ok

 

[Joe4]

Re: Percentage Problem

I have merged your two threads together. Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).

 

[ecawilkinson]

Re: Percentage Problem

Hi,

your answer does not work. If you select 0 decimal places in the Format Cells...>Percentage section then that is what you get, all the time no matter how many decimal places there are in the cell. I want a variable number of decimal places. For examples. if the cell originally contains the number 1.23456 then I want it to display 1.23456% after formatting, but also if it contains 1.2 then I want it to display 1.2% after formatting, then also if it contains 1 then I want it to display 1% after formatting. Your reply simply does not work in all these different situations. It truncates to 0 decimal places no matter what the cell originally contains.

 

[Joe4]

Re: Percentage Problem

What is the maximum number of decimal places you may have?
Let's say it is 9. Then you could use this Custom Format:
0.#########%

That would give you pretty close to what you want. The only caveat is that numbers with no decimals, like 1%, would be displayed as 1.%

The only other way I can think of to give you exactly what you want would probably require VBA.

 

[ecawilkinson]

Re: Percentage Problem

Thanks, Joe4. I was afraid that that might be the case. I did try your suggestion and, as you say, it leaves an unwanted decimal point if there are no decimal places, at all.

 

[Joe4]

Re: Percentage Problem

Are you open to a VBA solution?
If so, can you tell me which rows/columns these percentage values will be entered in?

 

[Eric W]

Re: Percentage Problem

You can do it with a bunch of Conditional Formatting rules. For example, if your column is E, and the values are all percentages, select column E and format it as Percentage, 0 decimals. Then select column E, click Conditional Formatting > New Rule > Use a formula > and enter: =LEN(MID(E1,FIND(".",E1)+1,99))=3 > then click Format > Number tab > Percentage > 1 decimal place.

Now add another rule for as many digits as you think you'll have, changing the 3 and 1 to 4 and 2, 5 and 3, 6 and 4, etc.

Kind of clunky, but it works.