Forum Discussion
Excel inserts '@' Symbol into formulars, which breaks them
Hey Tech community, I am facing the following challenge and hope someone here can explain why it is happening and how to prevent the issue: I have created an Excel template that uses formulars to l...
This is driving me nuts. When writing out a formula from a .NET application using the formula =SUMPRODUCT(IF(A5:A20<>"Blue",1,0),B5:B20) then open the Excel file created The formula has changed to =SUMPRODUCT(IF(@A5:A20<>"Blue",1,0),B5:B20), the addition of the @ symbol in the formula results in a broken formula returning #N/A. Remove the @symbol and the formula works . What the hell Microsoft.
Do you still get the '@' if you substitute
--(color<>"Blue")
for
IF(A5:A20<>"Blue",1,0)using a defined name for the array and not using IF to coerce the Booleans to values.
Thanks for the reply. When using a defined name for this I get #Value! error in the cell. The error is A value used in the formula is of the wrong datatype.
- I'm using VB.NET not VBA, don't think this is an option.
