Writing Excel formulas in Alteryx

[MichaelOlesen]

Hi all,

First of all thanks for a great community.

I have an issue when trying to write an Excel formula that works in Excel. I have written a simple formula like "=SUM(H2*2)" basically multiplying a field by two which works. The formula I have is slightly more complex, where the output of the below Excel formula works in Excel, however I get an error when trying to open the Excel file after running it. 

Excel states "We found a problem with some content..." in the file and I can recover the file but Excel removes the formulas. 

Any idea on how so solve this issue will be much appreciated.

BR, 

Michael

[ChrisTX]

My first guess would be to look at the use of single quotes and double quotes. 

Your first character is a double quote, and your last character is a single quote.  They should be the same character.  But... I'm guessing you don't actually need any begin or end quotes?

In general:

If you need to use double quotes within a formula, then use single quotes at the very beginning and very end, and don't use single quotes anywhere else in the formula.

Chris

[RolandSchubert · Accepted solution]

Hi @MichaelOlesen ,

I think, the formula should be written to the "internal" format, i.e. use "," as a separator instead of ";", which is used in "FormulaLocal" format.

Let me know if it works for you.

Best,

Roland

[Robin_McIntosh]

Has anyone noticed when writing Excel formulas that when you open the Excel file, some of the formulas receive the #NAME? error even though the formula syntax is correct? I've found that this being done on formulas that contain or based on arrays and Excel is adding @ before the function. When I double click in the cell and hit enter, then the formula works even though nothing was changed. Even if I update the Alteryx formula to insert the @ before the function, Excel still throws the error.  Not sure if there's something on the formula settings of Excel that I need to change.

[MichaelOlesen]

Thanks Roland

That was indeed the issue - Always tricky when you work in with EU/DK formats and Alteryx works with US formats.

I changed the formatting as suggested and it worked as expected. 

/Michael