Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Writing Formulas to Excel

MattD
Alteryx Alumni (Retired)
Created

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
CGA
5 - 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.

JMoore
8 - 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?

csegerblom
5 - Atom

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. 

robi
7 - Meteor

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

 

KEPM23
7 - Meteor

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")))))

mrbillyjackson
7 - Meteor

It would be really awesome if this worked in .xlsx. Any progress that I might be missing from other sources?

JR_Rocillo
5 - Atom

Hi everyone, newbie here, may I know if this is now working for .xlsx?

 

Still appears as a string, rather than a calculated value.

 

Works only in .csv, but not to .xlsx nor any Microsoft excel file types.

 

Can I get a like if this is also an issue to you? so we can get the attention of the experts here.

 

Please let me know if you agree or disagree. Thank you in advance.

 

btw, how can I attach the workflow here for sample?

gauravnegi2
8 - Asteroid

Hi @MattD,

 

I tried this for ".xlsx" but still instead of the value I do see the formula, I have to click on the cell and press enter to populate the values .

 

Is there any way we can automate this or i am missing something .

it will be really helpful if you can guide me .  

 

 

 

robi
7 - Meteor

Hi

 

It's OK for me :
2021-04-08 14_47_44-test_excel.xlsx - Excel.png

 

Can you verify that You are not in R1C1 formula style (columns identified with numbers).

 

You must not put the ' in the formula Alteryx

2021-04-08 14_57_44-test_excel.xlsx - Excel.png

 

gauravnegi2
8 - Asteroid

Hi @robi  ,

 

I used the same workflow  Matt shared. this is what the output is :

 

gauravnegi2_0-1617888726031.png

 

Can you please share the workflow that would be really helpful and is there any mistake I am doing here in the formula.

 

 

robi
7 - Meteor

Hi @gauravnegi2 

 

Please remove spaces in the formula before the = sign

=(B2*C2)

 

I prefer = LC2*LC3 in Excel in french or RC2*RC3 in Excel in english because the formula will be the same in the column for all rows.

Excel - File - Options - Formula - reference style R1C1

 

copy/paste text below with notepad and save in .yxmd

 

 

 

 

<?xml version="1.0"?>
<AlteryxDocument yxmdVer="2021.1">
<Nodes>
<Node ToolID="1">
<GuiSettings Plugin="AlteryxBasePluginsGui.TextInput.TextInput">
<Position x="30" y="42" />
</GuiSettings>
<Properties>
<Configuration>
<NumRows value="1" />
<Fields>
<Field name="A" />
<Field name="B" />
<Field name="formuleexcel" />
</Fields>
<Data>
<r>
<c>12</c>
<c>5</c>
<c>=A2+B2</c>
</r>
</Data>
</Configuration>
<Annotation DisplayMode="0">
<Name />
<DefaultAnnotationText />
<Left value="False" />
</Annotation>
</Properties>
<EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxTextInput" />
</Node>
<Node ToolID="2">
<GuiSettings Plugin="AlteryxBasePluginsGui.DbFileOutput.DbFileOutput">
<Position x="174" y="42" />
</GuiSettings>
<Properties>
<Configuration>
<File MaxRecords="" FileFormat="0">test_excel.csv</File>
<Passwords />
<FormatSpecificOptions>
<LineEndStyle>CRLF</LineEndStyle>
<Delimeter>;</Delimeter>
<ForceQuotes>False</ForceQuotes>
<HeaderRow>True</HeaderRow>
<CodePage>65001</CodePage>
<WriteBOM>True</WriteBOM>
</FormatSpecificOptions>
<MultiFile value="False" />
</Configuration>
<Annotation DisplayMode="0">
<Name />
<DefaultAnnotationText>test_excel.csv</DefaultAnnotationText>
<Left value="False" />
</Annotation>
<Dependencies>
<Implicit />
</Dependencies>
</Properties>
<EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxDbFileOutput" />
</Node>
<Node ToolID="3">
<GuiSettings Plugin="AlteryxBasePluginsGui.DbFileOutput.DbFileOutput">
<Position x="174" y="138" />
</GuiSettings>
<Properties>
<Configuration>
<File MaxRecords="" FileFormat="25">test_excel.xlsx|||data</File>
<Passwords />
<FormatSpecificOptions>
<PreserveFormat>False</PreserveFormat>
<SkipFieldNames>False</SkipFieldNames>
<OutputOption>RemoveFile</OutputOption>
</FormatSpecificOptions>
<MultiFile value="False" />
</Configuration>
<Annotation DisplayMode="0">
<Name />
<DefaultAnnotationText>test_excel.xlsx
Query=data</DefaultAnnotationText>
<Left value="False" />
</Annotation>
<Dependencies>
<Implicit />
</Dependencies>
</Properties>
<EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxDbFileOutput" />
</Node>
</Nodes>
<Connections>
<Connection>
<Origin ToolID="1" Connection="Output" />
<Destination ToolID="2" Connection="Input" />
</Connection>
<Connection>
<Origin ToolID="1" Connection="Output" />
<Destination ToolID="3" Connection="Input" />
</Connection>
</Connections>
<Properties>
<Memory default="True" />
<GlobalRecordLimit value="0" />
<TempFiles default="True" />
<Annotation on="True" includeToolName="False" />
<ConvErrorLimit value="10" />
<ConvErrorLimit_Stop value="False" />
<CancelOnError value="False" />
<DisableBrowse value="False" />
<EnablePerformanceProfiling value="False" />
<DisableAllOutput value="False" />
<ShowAllMacroMessages value="False" />
<ShowConnectionStatusIsOn value="True" />
<ShowConnectionStatusOnlyWhenRunning value="True" />
<ZoomLevel value="0" />
<LayoutType>Horizontal</LayoutType>
<MetaInfo>
<NameIsFileName value="True" />
<Name>genere_formule_excel</Name>
<Description />
<RootToolName />
<ToolVersion />
<ToolInDb value="False" />
<CategoryName />
<SearchTags />
<Author />
<Company />
<Copyright />
<DescriptionLink actual="" displayed="" />
<Example>
<Description />
<File />
</Example>
</MetaInfo>
<Events>
<Enabled value="True" />
</Events>
</Properties>
</AlteryxDocument>

 

 

gauravnegi2
8 - Asteroid

Hi @robi 

 

using your flow as well i am getting the same thing please see the screenshot.

 

I have to click on the cell and press enter to make formula working. 

gauravnegi2_0-1617965019739.png

 

robi
7 - Meteor

Hi

Do you open the .csv (with Excel) or the .xlsx ?

Do you have this option Excel enabled ?

If yes : you will see formula, you will not see the results

 

affichage formule.png

 

gauravnegi2
8 - Asteroid

Hi 

 

This option is unchecked .

 

I am opening .xlsx file. 

for CSV that is working good.

 

gauravnegi2_0-1617970201236.png

 

robi
7 - Meteor

OK, are you in automatic calcul mode ?

In this case, can you press F9 to force calcul or change the File-option-Formula-Calcul mode to Automatic

calcul auto.png

xAJBx
7 - Meteor

.xlsx is not working for me either.  Would be nice

Pbhave
6 - Meteoroid

Hello, this thread is very helpful for me. But I have a conditional if formula. I want Alteryx output file to have a column populated with the formula referencing to respective cells. Could you please help me write it in Alteryx?

 

My formula is as below:

 

=IF($TL3="%",(TN3*INDEX(TU$3:TU$65,MATCH($TM3,$M$3:$M$65,0))), IF($TL3="SRATE",SUM(100%+TN3)*TB3,IF($TL3="YRATE",SUM(100%+TN3)*QD3,IF($TL3="FV",TN3,0))))