Results 1 to 6 of 6

Thread: Excel help - removing '0' results from formula

  1. #1
    Senior Member
    Join Date
    Apr 2005
    Location
    Essex
    Posts
    600
    Thanks
    0
    Thanked
    1 time in 1 post

    Excel help - removing '0' results from formula

    I'm aggregating a lot of information with many hundreds of results on the form as '0'. As I'm working with numbers with 000's on them, it gets quite distracting.

    In college I was shown a way of removing the 0's from display - it wasn't a find and replace - yet I can't remember if it was a macro or a conditional format or something.

    Anyone got any ideas?
    Tim N

  2. #2
    Senior Member ajbrun's Avatar
    Join Date
    Apr 2004
    Location
    York, England
    Posts
    4,840
    Thanks
    4
    Thanked
    25 times in 13 posts
    I'm not entirley sure since I don't have excel infront of me right now, but I think it you right click the cell and go to cell properties there's an option to turn the number 0 into a blank cell (i think that's what you're after, and I think it exists there ).

  3. #3
    I machine things !
    Join Date
    Nov 2005
    Location
    Southampton
    Posts
    435
    Thanks
    3
    Thanked
    1 time in 1 post
    Shamelessly copied from Excel help :

    "Display or hide all zero values on a worksheet
    On the Tools menu, click Options, and then click the View tab.
    Do one of the following:
    To display zero (0) values in cells, select the Zero values check box.

    To display zero values as blank cells, clear the check box."


    ..or...

    "Use a number format to hide zero values in selected cells
    Caution Follow this procedure to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the general number format.

    Select the cells that contain the zero (0) values that you want to hide.
    On the Format menu, click Cells, and then click the Number tab.
    In the Category list, click Custom.
    In the Type box, type 0;-0;;@ "

  4. #4
    unapologetic apologist fuddam's Avatar
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts
    another method, which may be too much hassle, would be to embed your original calc within an IF statement

    ie. =IF(original calc = 0, "", original calc)

    is useful insofar as can do a lot more with it, eg if your calc returns an error message like #N/A or #DIV0
    ordinarily that would cause problems further down the line, because you can't incorporate such results in a greater total (for eg)

    the idea would be to replace all error messages with nothing, using that IF statement.

    apologies if you're already way beyond that

    Last edited by fuddam; 18-07-2006 at 05:22 PM.

  5. #5
    I machine things !
    Join Date
    Nov 2005
    Location
    Southampton
    Posts
    435
    Thanks
    3
    Thanked
    1 time in 1 post
    I always use the ISERROR function to eliminate any possible errors from my sheets.

  6. #6
    unapologetic apologist fuddam's Avatar
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts
    zigackly! ferpectly right


Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Fantasy Formula 1
    By kaillum in forum Automotive
    Replies: 6
    Last Post: 27-10-2013, 10:41 AM
  2. Removing RAM to install in another PC
    By MEW71 in forum Software
    Replies: 2
    Last Post: 02-04-2006, 01:42 PM
  3. ATI Catalyst 5.8 released
    By =TcQi= in forum Graphics Cards
    Replies: 17
    Last Post: 18-08-2005, 12:35 AM
  4. Replies: 6
    Last Post: 12-08-2005, 08:50 AM
  5. Underachieving 3dMark03 Results...
    By cptwhite_uk in forum Help! Quick Relief From Tech Headaches
    Replies: 8
    Last Post: 23-09-2004, 06:19 PM

Posting Permissions

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