EXACT FUNCTION

[Lana]

Does anyone know if you can exclude blank cells when using the exact function
to compare two cells to see if they are duplicates?

 

[Glenn]

Does anyone know if you can exclude blank cells when using the exact function
to compare two cells to see if they are duplicates?

Two blank cells compared with EXACT() will result in "TRUE". One blank cell
(and one non-blank cell) compared with EXACT() will result in "FALSE". What
result are you looking for.

 

[Bernard Liengme]

Ho about:
=IF(AND(ISBLANK(A1),ISBLANK(B1)),NA(),EXACT(A1,B1))

You could replace NA() by "" if this works better fro you
best wishes

 

[Sheeloo]

What do you mean? Not get TRUE if both are blank?
If yes, then one way is to use a formula like the one below;
=IF(AND(A1="",B1=""),"",EXACT(A1,B1))

If you want to get blank if either one is blank then use OR instead of AND

This will return a blank if both A1 and B1 are blank and return TRUE/False
otherwise.

 

[Harlan Grove]

Ho about:
=IF(AND(ISBLANK(A1),ISBLANK(B1)),NA(),EXACT(A1,B1))

....

Or simplify.

=IF(COUNT(A1,B1)=2,EXACT(A1,B1),whatever)

 

[Bernard Liengme]

Or
=IF(COUNTA(A1,B1)=2,EXACT(A1,B1),whatever)
if the entries are text

No need to use EXACT to compare numbers, Harlan !

 

[Peo Sjoblom]

OTOH if the values are derived from formulas then isblank will fail and so
will counta so you might want to use

=IF(AND(A1="",B1=""),NA(),EXACT(A1,B1))



--


Regards,


Peo Sjoblom

 

[Bernard Liengme]

Good point

 

[Rick Rothstein]

Or possibly even this way...

=IF(A1&B1="",NA(),EXACT(A1,B1))

 

[Harlan Grove]

Or possibly even this way...

=IF(A1&B1="",NA(),EXACT(A1,B1))

Produces #VALUE! errors if either cell is truly blank when transition
formula evaluation is enabled.

 

[Rick Rothstein]

I have never used transition formula evaluation myself, so thanks for
pointing that out... I really appreciate it.