• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Do not display IF ZERO

N

ninad7

Member
Occassionally, I need to use the formula =if(A1-B1=0, "", A1-B1) in C1; A1,B1,C1 being any cell or A1-B1 representing any formula and C1 representing target cell. It is tiresome to edit each required cell with the if statement. Can someone write a VBA for this.


This function, let's call it DNDIZ (short for Do Not Display If Zero) with the required formula will do exactly that. Will make life easy - at least for me. Example, entering DNDIZ(A1-B1) in the cell C1 will display a positive or negative number but not zero.


Unless there is an existing function in XL.


Ninad.
 
Found this:


http://office.microsoft.com/en-us/excel/hp051998791033.aspx#Use%20a%20number%20format%20to%20hide%20zero%20values%20in%20selected%20cells
 
Well the easiest way is to uncheck the "To display zero (0) values in cells"

You can find it under the tools menu / advanced and quite far down you find "Show spreadsheet alternatives" or something like that.

My version of XL 2007 is in swe so the English names may not be exact


One other way to "hide" the zeroes is to make a Conditional Formatting

(http://office.microsoft.com/en-us/excel/HP051992781033.aspx?pid=CH010036941033)

were you set the zeroes to be white (same color as your background)and presto they are gone :)


Works like a charm if you just want to hide a specific character in a selected area
 
You must log in or register to reply here.
Back
Top