Custom format to display one or no decimal places (stray period!)

[Legacy 181763]

Please can anyone tell me how (or if!) I can use the custom format option to display numbers as percentages to either 1 or 0 decimal places:

Example 1: Value in cell 0.29449, Required display 29.4%
Example 2: Value in cell 0.29, Required display 29% (my major issue is that the closest I can get results in it displaying "29%." i.e. a final stray period remains on the end!

My specific situation is that I have a stacked bar chart in Excel which comprises a number of series, each corresponding to a percentage value. I wish to add data labels which are of the form #0.x% (where x would not equal zero) or #0% (otherwise). e.g. four series, with the values 0.294, 0.3, 0.045, 0.7 would result in data labels on the chart as follows 29.4%, 30%, 4.5%, 70%

Many thanks!

A

 

[Gerald Higgins]

Hi, welcome to the board.

I don't think you can do that, at least not easily.
I think your choices are
29.4% 30.0% 4.5% 70.0%
OR
29% 30% 5% 70%
and so on.

You could manually edit the format for each label, but that might be a pain.
You might be able to get a macro to do it as well.

 

[Weaver]

I've tried this without much success in the past - sorry I don't have a contribution this time, but I'd be interested to see if anyone else has.

 

[GlennUK]

Reference your percentages in another range, and multiple by 100. Then change the number format to be Custom of:
General"%"
and use that range for your chart.

Excel Workbook
	A	B	C	D
1	29.4%	30.0%	4.5%	70.0%
2
3	29.4%	30%	4.5%	70%
Sheet15

 

[Gerald Higgins]

GlennUK - I should have thought of that

 

[Legacy 181763]

Glenn - many thanks. Works a treat!

 

[RoryA]

Doesn't that kind of ignore the 1 DP issue for numbers like 0.2449?

 

[barry houdini]

Perhaps use Glenn's suggestion but round to 1 decimal when multiplying by 100, e.g.

=ROUND(A1*100,1)