Comparing two rows disregarding any blanks

[jcp3677]

I am a complete rookie. I have searched Google extensively to no avail to a question that I feel like surely has a straightforward answer. I am attempting a pair wise comparison of location of fish during a 60 day period. The dates are the column headings and the fish ID's are the rows. I will be comparing the fish in row "2" to all of the other fish at first. I am using "

=IF(B$2=B3,1,0)" to get either a "1" if the location entered is the same or a "0" if it is not. I will the average the 1's and 0's to get a similarity score for the that pair of fish. Some of the dates (columns) have no location for the fish (row) in question. Right now the formula above enters a "0" because they are not the same. My question is this: What can I do to get Excel to disregard any spaces in either of the fish (rows) being compared?

Thanks you for any help you can provide.
​

 

[arthurbr]

Try =IF(OR(B$2="",B3=""),"",IF(B$2=B3,1,0))
You will need to use the AVERAGEIF function to exclude the cells containing the "" ( which is invisible...)