EXACT FUNCTION

L

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?
 
G

Glenn

Lana said:
Does anyone know if you can exclude blank cells when using the exact function
to compare two cells to see if they are duplicates?
Click to expand...

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.
 
B

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
 
S

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.
 
B

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 !
 
P

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
 
H

Harlan Grove

Rick Rothstein said:
Or possibly even this way...

=IF(A1&B1="",NA(),EXACT(A1,B1))
Click to expand...

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

Rick Rothstein

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