community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Writing Formulas to Excel

Community Data Engineer
Community Data Engineer
Created on

When writing to Excel, have you ever had the need to populate a column with values that come from formulas referencing other cells in your data? You’ve probably noticed that when writing to either .xlsx or .xls Excel file formats that your output still appears as a string, rather than a calculated value, like the below:

 

.xlsx
1.png


Each formula cell has the proper cell references and syntax but will need to be interacted with (add or remove a space in the formula and hit enter on your keyboard) to calculate the true formula value.

 

.xls
2.png


When expanding on the string you’ll see there’s an apostrophe (‘) preceding the formula and you’ll need to remove each and hit enter on your keyboard for the formula
to execute.
3.png

 

 

In the attached v10.1 workflow, Writing to Excel with Formulas.yxmd, we provide a sample formula expression to build formulas with basic cell indexing (using a record ID field) and the ability to write formulas that can be interpreted by Excel. This is done by leveraging the .csv file format; when excel opens this output file type it automatically recognizes the formula syntax and will interpret it as such – populating each cell with the true formula calculated value:
4.png

 

Attachments
Comments
Atom

formulas.PNG

MattD,

Thanks for the tip on this. I used but when I do it in xlsx it opens but still showing the formula and not the calculation.Any idea why? we ran the fworflow and got the output in two different computers and same issue.  however once we put on the cell and do enter the calculations comes up. any suggetiosn is appreciated.

Asteroid

Same issue here as CGA, any assistance would be helpful. should a vba macro also have to be kicked off to format the file after export to force the calculation in the cells?

Has anyone been able to figure out how to show the calculations rather than the formula itself when outputting as .xlsx? Seems to work when outputting as .csv but not otherwise. 

Meteoroid

I have written a simple macro which uses TextToCoumns to force Excel calculation.

 

My Excel is in French and with L1C1 notation (R1C1 in english)

But my formula génrated via Alteryx Formula "=LC(1)" is not well interpreted...

I have to write "=RC[1]"

 

Same problems with Recherchev (vlookup), Si (If)...

 

I don't find the Excel parameters ...

 

VBA Excel :

wb.ws.columns(2).select

selection.TextToColumns Destinations:=ws.columns(1),DataType:=xldelimited...

 

Meteoroid

Hello Can someone help me to write this specific formula but then the cell "G2" should be incrementing, i tried using tostring but i am having a parse error message.

 

the goal is to have this formula in the xls file, G2 value should be increasing or incrementing per row example G2, G3, G4 etc.

 

=IF(G2>75000000,"DCM",IF(G2>2500000,"GCM",IF(G2>500000,"RCM",IF(G2>250000,"CVA CBC",IF(G2=0,"NA","CVA COC")))))