asked on

Excel IF statement check for DIV/0

I've got a formula that checks a cell for #DIV/0 and outputs nothing if it finds it, otherwise it outputs the result of a division of two cells. The formula is not working. I suspect because I am not outlining the #DIV/0 properly.

Here it is...
=IF(AZ3=#DIV/0,"",AY3/AX3)

redmondb

Hi, dale_abrams,

=IF(iserror(AZ3),"",AY3/AX3)

Regards,
Brian.

dale_abrams

ASKER

Thanks for the help Brian. That got rid of the error message, but the cell is showing a 0.00 due to the number format on the cell with 2 place decimal. Is there any way around having a number show up in a number format cell? I really just want it to be blank if there is a divide by 0 error.

Not sure if a little more info would be helpful here, but I am placing this formula on a group of cells outlining values for the calendar year. While most of the cells have values, the dates in the future do not. Hence the division by 0 and my will to have them be blank if the values have not been populated yet.

barry houdini

Is the value in AZ3 an error or text?

Brians' formula should work if it's an error - the cell would show as blank not 0.00. If it shows 0.00 that must be a result of AY3/AX3....so AZ3 can't have an error......

an actual error would display like this

#DIV/0!

how is that error generated? If it's text then try

=IF(AZ3="#DIV/0","",AY3/AX3)

regards, barry

dale_abrams

ASKER

I just found that when I apply this to a cell with valid data, it is still showing up with 0.00. There must be something else going on here. My example above uses a simplified version of the forumla, here is my actual formula using the tab names...

=IF(ISERROR(AZ3),"",'PgVw MicroSite'!AZ3/'No. of Visits'!AZ3)

barry houdini

Which AZ3 are you checking for error? If the formula isn't on the same sheet then the first AZ3 needs to have a sheet name.....

barry

dale_abrams

ASKER

Hi Barry,
The value in AZ3 is definitely an error and not text. I was thinking it's showing 0.00 because it's formatted as a number.

dale_abrams

ASKER

The formula is on the same sheet as AZ3

redmondb

dale_abrams,

The following formatting will "hide" a zero value (of course, my formula is returning a blank, but you could just change that to 0)...

#,##0.00;-#,##0.00;

Open in new window

Regards,
Brian.

redmondb

Any chance 'PgVw MicroSite'!AZ3 is zero?

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

what are the respective values of 'PgVw MicroSite'!AZ3 and 'No. of Visits'!AZ3 ??

If the formula returns a 0, that means that the False part of the IF statement is executed. If the True part were executed, the cell would appear blank.

Have you checked the formula with the Evaluate Formula tool? Then you can see how it is resolved step by step.

cheers, teylyn

barry houdini

which cell and sheet is the formula in? Does AZ3 (the one you are checking with ISERROR) have a formula?

regards, barry

dale_abrams

ASKER

The values in 'PgVw MicroSite'!AZ3 and 'No. of Visits'!AZ3 are blank.

The formula is in AZ3 in a third sheet called 'Avg Pgs per User'.

There was an error as a result of the formula: 'PgVw MicroSite'!AZ3/'No. of Visits'!AZ3 before I added the IF(iserror()). So, it doesn't make sense that the False part of the formula would be getting evaluated. I'm not saying that's not what's happening, but rather that I can't make sense of it.

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

redmondb

=IF(ISERROR('PgVw MicroSite'!AZ3/'No. of Visits'!AZ3),"",'PgVw MicroSite'!AZ3/'No. of Visits'!AZ3)

or, if you have Excel 2007/2010...
=IFERROR('PgVw MicroSite'!AZ3/'No. of Visits'!AZ3),"")

dale_abrams

ASKER

Barry Houdini, you are the BIG WINNER!!! Well, I guess I am too, 'cause I got my problem sorted. Big thanks to all who were helping.

redmondb

Oops...
=IFERROR('PgVw MicroSite'!AZ3/'No. of Visits'!AZ3,"")