+ Reply to Thread
Results 1 to 5 of 5

Custom Number Format question

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    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?)
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    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


    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,714

    Re: Custom Number Format question

    Quote Originally Posted by mc84excel View Post
    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\%) .

    Quote Originally Posted by mc84excel View Post
    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" .
    Last edited by joeu2004; 02-04-2016 at 08:24 PM. Reason: EDIT

  4. #4
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Custom Number Format question

    Good one Joeu2004!

    I forgot about the third parameter in the Custom format.

    DAC

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Custom Number Format question

    Thank you both of you (reps)


    Quote Originally Posted by joeu2004 View Post
    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need formula or code to convert number(s) from custom format to number format
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-12-2015, 07:44 PM
  2. Custom format question
    By Youdaman in forum Excel General
    Replies: 4
    Last Post: 07-06-2009, 11:50 AM
  3. Custom Format Question
    By konstantinr in forum Excel General
    Replies: 3
    Last Post: 05-24-2008, 08:36 AM
  4. Custom Format question....
    By ddd in forum Excel General
    Replies: 2
    Last Post: 07-22-2007, 06:25 PM
  5. how do I add phone number format as a permanent custom format?
    By frustratedagain in forum Excel General
    Replies: 3
    Last Post: 02-03-2006, 11:52 PM
  6. [SOLVED] Custom Number format question
    By Joh in forum Excel General
    Replies: 5
    Last Post: 10-25-2005, 10:05 AM
  7. custom date format question
    By xcelvisitor in forum Excel General
    Replies: 1
    Last Post: 05-14-2005, 07:03 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1