Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Writing Excel formulas in Alteryx

6 - Meteoroid

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. Formula.PNG


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.




15 - Aurora

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.





16 - Nebula
16 - Nebula

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.





11 - Bolide

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.




6 - Meteoroid

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.