Writing Excel formulas in Alteryx
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Expression
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
