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 Discussions

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

Excel Formula Output

Lion24
7 - Meteor

Hi,

 

I am wondering if it is possible to output formulas in excel rather than just numbers. In other words, if a column in Alteryx is the product of two columns - can I output into excel so that when I click into the cell I can see the formula?

 

Example:

Column A = 100

Column B = 2

 

If I create Column C called "Market Value" with the formula tool and set it equal to [A] x [B] I will get 200. When I output into excel, all I will see is 200 in column C. Is there any way I can output so that when I click into the cell I will see =[A] x [B]?

 

Thanks!

Lion24

8 REPLIES 8
AmeliaG
Alteryx
Alteryx

Hi @Lion24,

 

Thanks for your question! You can use the 'Field Info' tool to pull the formula information for a column. See screenshot below 'Source' field to see the formula information for the column. 

 

fieldinfo.png

geraldo
13 - Pulsar

Hi

 

Here's a suggestion on how to assemble the formula without assigning the values. In excel to work ranges must be named

 

 

attached workflow

 

 

[]

Lion24
7 - Meteor

Hi Amelia, 

 

Thanks for the response. I see how I can use that tool to show the formula. However, I still want to output the actual result of the formula in the column but when I click into the cell I can see the formula in the excel formula bar. Is this possible?

AmeliaG
Alteryx
Alteryx

Hi @Lion24,

 

To my knowledge, this is not possible. The formula is only contained in the metadata of the field (what is provided by the fieldInfo tool) and is not contained in the field itself. 

 

Can you help me better understand the "why" so perhaps I can offer a different suggestion?

 

Thanks,

 

Amelia

danilang
19 - Altair
19 - Altair

Hi @Lion24 

 

Check out this post, by the esteemed @MattD.   It shows a way to push formulas to excel by writing to a CSV file and using the fact that when a csv file is opened in excel, properly formatted formulas will be automatically converted.

 

Dan

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.

 

Please help.

 

Please see sample attached.

ebwest
5 - Atom

I'm not sure if this has been answered already, but you can use the Record ID tool and a formula to write the formula out to Excel and have it populate the formula result and be able to see the formula when you click in the cell.

 

You bring in your data, add a "Record ID" tool to number the rows, and then add a "Formula" tool to add the string version of the formula. The example below would do A2-B2 on row 2, A3-B3 on row 3, etc...

 

"=A"+tostring([RecordID]+1)+"-B"+tostring([RecordID]+1)

 

When you export to Excel and open the file, it shows "=A2-B2", etc... in the field when you click on the cell, and it shows the numeric result when you're not clicked in the cell.

 

Hope this helps.

JarekSkudrzyk
11 - Bolide

@ebwest this is a great solution - simple and effective! thank you for sharing it!

Labels