What is the difference in results, if any, between these 2 formulas?

[Eloise T]

I understand the use of the "$" in an array formula, but in the following two formulas, will there ever be a circumstance where there's a difference in results?

{=IF(A3<>"",IF((COUNTIF($A$3:$A$2001,TRIM(A3))>1),"Duplicate",""),"--")}

{=IF($A3<>"",IF((COUNTIF($A$3:$A$2001,TRIM(A3))>1),"Duplicate",""),"--")}

 

[Somendra Misra]

Hi,

I don't see any need of this formula to be an array formula. Any specific use for them?

Plus, the $ will only change the result if you copy the formula across.

Regards,

 

[Eloise T]

Hi,

I don't see any need of this formula to be an array formula. Any specific use for them?

Plus, the $ will only change the result if you copy the formula across.

Regards,

So the "$" in {=IF($A3<>"", is unnecessary and either formula will produce the exact same results, correct?

The formula, located in Column J (J3 through J2001), looks at the work order number in Column A and makes sure it has not been duplicated in A3:A2001, otherwise it puts "Duplicate" in the cell.

 

[Asheesh]

Yes, absolutely right...there is no need to use "$"

 

[Eloise T]

Yes, absolutely right...there is no need to use "$"

THANKS!

 

[Asheesh]

you welcome

 

[Eloise T]

you welcome

Oooops! I figured out why the $ is in front of the A... When you drag the formula to the right, which I inevitably do, it has to stay pointed at Column A.
Lesson learned for me. Thankfully I caught it before doing any damage.
I do appreciate your assistance...you made me to think it though. Thanks.

 

[Asheesh]

Read the comment no 2 by Somendra, he says "the $ will only change the result if you copy the formula across."

Dragging the formula down will not affect the result at all..

 

[Eloise T]

Understood. Thank you.