# Custom Number Format question

1. ## Custom Number Format question

I have a range of numbers I want to show as percentages. e.g. "40" to display as "40%"

This is the nearest I have got:
PHP Code:
``` ##\%;[Red](##\%)  ```
However where the number is "0", it displays as "%". How do I alter the custom number format to have 0 display as "0%"?

(Optional question - I'd also be interested in having 0 displayed as "N/A" assuming this is possible?)

2. ## Re: Custom Number Format question

Try 0#\%;[Red](0#\%)

To get 0 to display as N/A you could use an IF() formula!

I hope this helps, please let me know!

Regards,

David

3. ## Re: Custom Number Format question

Originally Posted by mc84excel
I have a range of numbers I want to show as percentages. e.g. "40" to display as "40%"[.] This is the nearest I have got: ##\%;[Red](##\%)
[...] How do I alter the custom number format to have 0 display as "0%"?
Change "##" to "0", to wit: 0\%;[Red](0\%) .

Originally Posted by mc84excel
Optional question - I'd also be interested in having 0 displayed as "N/A" assuming this is possible?
0\%;[Red](0\%);"N/A"

[EDIT] That custom format has the following form, in general: formatIfPos;formatIfNeg;formatIfZero .

PS.... I think it is ill-advised to format integers as "%". It will be confusing: when you see 40% in a cell, is it 40 or is it 0.40?

IMHO, it is better to divide the cell value by 100 (or not to multiply it by 100) so that 40% is truly 0.40, and use the same custom formats without the backslash, to wit: 0%;[Red](0%);"N/A" .

4. ## Re: Custom Number Format question

Good one Joeu2004!

I forgot about the third parameter in the Custom format.

DAC

5. ## Re: Custom Number Format question

Thank you both of you (reps)

Originally Posted by joeu2004
PS.... I think it is ill-advised to format integers as "%". It will be confusing: when you see 40% in a cell, is it 40 or is it 0.40?

IMHO, it is better to divide the cell value by 100 (or not to multiply it by 100) so that 40% is truly 0.40, and use the same custom formats without the backslash, to wit: 0%;[Red](0%);"N/A" .
I know what you are saying and I agree. This is a one-off exception. The numbers come from SQL as integers only and the percentages are for display purposes only (i.e. the integers are not used in any extra calculations. the sheet is printed to PDF immediately after this formatting)

There are currently 1 users browsing this thread. (0 members and 1 guests)