+ Reply to Thread
Results 1 to 6 of 6

Please modify my code for custom formatting for decimal places with certain numbers

  1. #1
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Question Please modify my code for custom formatting for decimal places with certain numbers

    Hi

    I'd like to format some cells so that and number below 1 has 1 decimal place(0.1) , any number above 1 has no decimal place (5) and if the number is zero then don't display anything (). Additionally it would be nice if any text would just be left to display as text (text).

    So far I have come up with the following code

    [<1]_(###0.0_);_(###_)

    It almost does the job however if a zero is present 0.0 is displayed. To be a little more specific the cells displaying 0.0 are actually blank, they use an indirect function to retrieve numbers from elsewhere and when no number is present currently 0.0 is displayed. The indirect function happens to be =INDIRECT("item"&D1&C1&"_week_"&$B1&$B2). I wish that when this code did not return a value that the cells could be empty rather than containing 0.0.

    Can my code be easily modified to include a further clause involving zeros?

    Btw I had almost come up with something that did this but it left a decimal (.) after some whole numbers and I didn't like this.

    Thanks in advance for any assistance

    Michael from Australia.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,541

    Re: Please modify my code for custom formatting for decimal places with certain numbers

    [=0]_(0;[<1]_(###0.0_);_(###_) seems to work..

  3. #3
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: Please modify my code for custom formatting for decimal places with certain numbers

    That turns the 0.0 into just a 0

    It's a definite improvement but is there some way to have nothing show at all?

    i.e incorporate "" to display a blank cell in place of the zero

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,541

    Re: Please modify my code for custom formatting for decimal places with certain numbers

    Perhaps =IF(INDIRECT("item"&D1&C1&"_week_"&$B1&$B2)=0,"",INDIRECT("item"&D1&C1&"_week_"&$B1&$B2)) ?
    But this will slow everything down due to the volatility of INDIRECT

    You can also choose to not display zero values ( it's somewhere in XL's options- only 2003 here) but is that what you want?

    Edit [=0]_(;[<1]_(###0.0_);_(###_) also works
    Last edited by Pepe Le Mokko; 07-10-2012 at 11:35 AM.

  5. #5
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: Please modify my code for custom formatting for decimal places with certain numbers

    Thanks for that, [=0]_(;[<1]_(###0.0_);_(###_) was the trick I needed

    The If statement might not have worked as the cells returing a zero through the indirect function are actually empty but I could have changed it perhaps to check for "" and return "" As you mentioned this would have been slow though.

    The custom format I usually use to hide zeros is 0.0;-0;;@ but that's as a stand alone format not sure how I would have worked that in to the decimal place thing.

    Thanks again
    Last edited by mick86; 07-10-2012 at 03:18 PM.

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,541

    Re: Please modify my code for custom formatting for decimal places with certain numbers

    You're welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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