how-to turn off the <@> symbol insert

[kent_culpepper]

i am programmatically inserting the following formula;
=IF(RC[1]>0,RC[1],INDEX(distTTKitTKCtValue,MATCH(1,(RC[-1]=distTTKitTkCtTT)*(RC[-29]=distTTKitTKCtRMId),0)))

microsoft is automatically adding the <@> like;
=@IF(RC[1]>0,RC[1],INDEX(distTTKitTKCtValue,MATCH(1,(RC[-1]=@distTTKitTkCtTT)*(RC[-29]=@distTTKitTKCtRMId),0)))

this returns an error #N/A in my worksheet

when i remove the <@> (manually) the formula returns the result fine

why is this occurring\ and might be a solution to this <@> business?

[#2]

@kent_culpepper 

Why it is added is here Implicit intersection operator: @ - Office Support (microsoft.com)

You may try this setting

if helps

[#3]

@Sergei Baklan 

Thanks for reply. Yes, I read that article before posting. There was another similar post that suggested the turning off of an option but it was not obvious to me which option. I will go ahead and try that setting.

In the meantime, I found a work-around that seemed to work.. which was using the "FILTER" variation of the formula which, yes still adds the @ symbol. But actually returns the expected result, instead of an error. So, its all very interesting.

Thanks!

[#4]

Also, if your formula is targeting newer excel versions that use dynamic arrays and not the legacy implicit intersection method, then I believe you should be using the Formula2 property of the range object and not the formula property.

https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula...

[#5]

@JMB17 

You are correct. In a macro I was creating I had to use .formula2 and .value2. If I didn't when it returned a formula in text string format back to the formula bar in another cell it added the @ symbol because I was using dynamic arrays in excel 365. It gave an #N/A error when it inserted the symbol.