+ Reply to Thread
Results 1 to 4 of 4

Using an if statement to check if the formula results in zero

  1. #1
    Registered User
    Join Date
    09-29-2014
    Location
    WI
    MS-Off Ver
    2010
    Posts
    1

    Using an if statement to check if the formula results in zero

    I have an extremely complicated formula running in many cells in a large sheet. I need each cell to check if this formula results in a 0 value. If the value is 0, it prints a blank, and if it isn't, then it print the result of the formula.

    The only way I've come up with is doing it like so:

    IF([complex formula]=0,"",[complex formula])

    This means I have to write the formula twice, and edit it twice if I want to tweak it. There has to be a better way.

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Using an if statement to check if the formula results in zero

    That is how it is done.

    Depending upon how your data is laid out, there may be different ways of cross-checking your results. If it is a proper data set...one row per record, 1 column per field, it shouldn't be too difficult to create a cross-check.
    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,276

    Re: Using an if statement to check if the formula results in zero

    Why not use a custom number format of

    0;-0;

  4. #4
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,691

    Re: Using an if statement to check if the formula results in zero

    Hello
    welcome to the forum....
    Yes your problem is quite serious....it happens many times that we have to duplicate the formula just to check a zero...
    So here is a simpler method--
    I have made a user defined function--
    Just go to Developer tab>>Visual Basic>>insert>>module>> and paste the following code
    Code:-
    Please Login or Register  to view this content.
    Now type in the formula =ifzero(complexformula)
    it will return a blank if a zero is there...
    hope it helps
    Regards
    Sourabh

    //<<<---If you like my suggestion then you can thank me by pressing the Add reputation button below....
    //
    //
    /
    Last edited by sourabhg98; 01-22-2015 at 01:04 PM.

+ 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. Formula to check multiple values for multiple specific results
    By lqangel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-16-2015, 01:29 PM
  2. Replies: 2
    Last Post: 03-04-2014, 06:31 PM
  3. Need formula to Populate Results of Dropdown "Check" Boxes
    By CmH315 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-16-2014, 09:52 PM
  4. check the values and display the results
    By gurum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2013, 08:01 AM
  5. Vba to loop to check value and copy results over
    By bkeat in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2013, 08:32 AM

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