Writing Excel formulas in Alteryx

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

Expression

Accepted answers

RolandSchubert

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

All comments

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

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.

Excel Formulas.yxmd

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335