Comparing two cells whose results are #/DIV0!

easycapital
easycapital used Ask the Experts™
on
So, C1 will read =If( A1 = B1, 1, 0).  The formula would be copied down column C.  I am running into the issue that when comparing the A column with the B and there is an #/DIV0! error in either/both columns, then the result will be an error.  What is the simplest way of resolving this issue. Would it by converting the formula the cells values to text? Looking for a formula to apply.

Further info:  The list continuous down each column with perhaps non error results, say, regular numbers.  

Thanks,
JP
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Shouldn't you be resolving the error where it occurs in A or  B rather than testing for it in a later formula?

Author

Commented:
Hi Peter,

The approach taken is not to suppress the error.  It helps out later on on which scenarios the item does not apply.  

The question still stands, if anyone knows how to resolve the original posted question.

Thanks,

JP
Neil RussellTechnical Development Lead

Commented:
Try the following in C1
 
=IF(ISERR(A1) + ISERR(B1),0,IF(A1=B1,1,0))
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Commented:
Excel can test for error using this function iserr and iserror
e.g.
=if(or(iserror(a1),iserror(b1)),"One of the value have error",if(a1=b1,1,0))
too late!
I'll post it anyway:(only half done)
=IF(ISNA(A1),IF(A1=B1,1,0),0)
 

Author

Commented:
Can't we just compare and see if the value as "text" match? If so, how do we do that?
Neil RussellTechnical Development Lead

Commented:
Why?
You want to know when Both A1 and B1 are in error? Not just if A1 = B1? If that is the case you should be questioning why you have errors yet again..
The value as Text would be an error if the cell is in error, so no different but a LOT slower.

Author

Commented:
So, if we said -- If (text in A1 equals text in B1 , 0 , 1)

Where "text in A1 equal text in B1" would be replaced with an appropriate formula.

JP
Neil RussellTechnical Development Lead

Commented:
There is no formula to convert an ERROR cell into text, you would need to again check and see if the cell was valid or an error first and convert it accordingly. You are getting into the realms of having a formula that is 10 times longer to execute purely for the fun of it.
 
Neil RussellTechnical Development Lead

Commented:
Rememer that a cell with #DIV/0! does NOT contain #DIV/0 it is an error condition. The cell has NO VALUE just an error indicator that MUST be tested for using iserror()
 
Most Valuable Expert 2013
Commented:
If the only errors will be #DIV/0! then you could use this formula
=CHOOSE(ISERR(A1)+ISERR(B1)+1,A1=B1+0,0,1)+0
That way you get 1 whenver both cells are the same, both blank, both #DIV/0! or both the same value, otherwise you get zero.
If that isn't what you need then please give some examples of required results
regards, barry
Cory VandenbergSenior Risk Manager

Commented:
As barry has said, if #DIV/0! is the only error, use of the ISERR function would suffice, however, using the ERROR.TYPE() function would ensure that the errors seen in column A and B were the same.  Otherwise, a #DIV/0! error might be interpreted as the same as a #VALUE! error.

=IF(ISNA(ERROR.TYPE(A1)=ERROR.TYPE(B1),IF(A1=B1,1,0),IF(ERROR.TYPE(A1)=ERROR.TYPE(B1),1,0))

Cheers,
WC

Author

Commented:
Barry, right on.  I did modify the formula to have it show a zero if the results are the same and a one if they are different.  Take a look at it:  =CHOOSE( ISERR(A1) + ISERR(B1) + 1, (A1 = B1 -1) * -1, 1, 0) + 0  

Thanks to all the other experts who joined the question.

JP
Cory VandenbergSenior Risk Manager

Commented:
Oops, missing )

=IF(ISNA(ERROR.TYPE(A1)=ERROR.TYPE(B1)),IF(A1=B1,1,0),IF(ERROR.TYPE(A1)=ERROR.TYPE(B1),1,0))

Author

Commented:
Barry,

Take me out my doubt.  Why are you adding + 0 in the formula.  Is it to tell the formula that the answer is numerical, or what?

JP
Most Valuable Expert 2013

Commented:
JP,
actually I left a little bit in the formula that shouldn't be there....although it wouldn't affect the result.
The +0 came about because of my almost chronic ambition to shorten formulas as far as possible! I stared off with
=CHOOSE(ISERR(A1)+ISERR(B1)+1,(A1=B1)+0,0,1)
where the (A1=B1) part returns either a TRUE or FALSE result, and adding 0 "co-erces" that to a 1 or 0 (to avoid an IF). Then I realised that if I put the +0 outside the CHOOSE function I could also remove the parentheses to shorten....but I forgot to remove the now unnecessary first +0, so in fact it should have been
=CHOOSE(ISERR(A1)+ISERR(B1)+1,A1=B1,0,1)+0
In reality there isn't much to choose between any of those versions, they'll all do the same and any time saving or efficiency will be infinitesimally small
regards, barry
 

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial