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

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.  Register To Reply

2. ## 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  Register To Reply

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

Why not use a custom number format of

0;-0;  Register To Reply

4. ## 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....
//
//
/  Register To Reply