Handling Excel Output Summation with Formulae Embedded

Hello,

In my application, I need my output written to Excel, and since there is subsequent manual input (lines 2 and 9), I'm trying to include a summation formula on row 10, as shown in both Columns D & E:

Excel Output Issue.jpg

My problem is regardless of whether I use an Output Data tool, or Basic Table + Render tool, the numeric values that you see above are not considered numeric in Excel. That is, the summation formula always gives 0.

I would expect 15,996 in D10, and 53,484,665 in E10 above.

As an aside, I had seen the "FORMULACORRECTOR" approach (D10) used elsewhere, which does require the extra step of a manual search & replace in Excel afterwards to remove it, and I'm not sure why that was used in the first place.

Output

Accepted answers

Peachyco

@jdallen75

The formula is performing as expected. The issue is that their presence in those columns makes Alteryx write the entirety of those columns as strings, so the numbers in them are treated as strings and the formula sums them as zero.

Maybe you can place those subtotals in a different column? Something like this (this was generated by Alteryx):

Alteryx - jdallen75 Excel formula.png

All comments

Prometheus

@jdallen75 Before you send your data to the Output Data tool, consider changing the data type of "Type 2" to a numeric data type, such as Int32 or Double. If there are no non-numeric values in that field when you output the data to Excel, they should all show up as numbers when you open Excel.

jdallen75

@Prometheus The only issue is that in Alteryx itself, I've generated the summation formula - so it won't interpret Columns D or E as numeric (int/double) because of the string formula.

Any ideas how to get around this?

Thanks,

Jeff

jdallen75

I'm basically trying to generate an Excel form, where Alteryx has calculated all of the Report Items, the user then updates Manual Inputs, and the Subtotal already contains a formula to kick out the calculated value.

Prometheus

@jdallen75 I turned your issue into an app where the user can input a number value into Data Columns 1 & 2, then I unioned the data and used a Summarize tool to get the sum of the Data Column 1 field and the Data Column 2 field. There's also an opportunity for the user to input data into Data Columns 1 & 2 (index 5). I brought Index 5 data back in using an Append Fields tool, then subtracted index 5 from the sum Data Column 1 (index 1+2+3+4). Does this work for you?

Sample Alteryx Issue v2.yxzp

jdallen75

Hi @Prometheus I appreciate the effort, however the client does not deal with Alteryx Designer at all; they provide us with a number of input data files, *we* run the Alteryx workflow(s), and hand them the resulting Excel output files, where they can enter in adjustments (the manual inputs), and they get the output numbers they need. They only deal with the final Excel file.

So in general, is there no way to construct Excel formulae (as I did) and have that generated in the Excel output such that it performs as expected? The number of rows in the SUM function is also dynamic, based on what is calculated using the client-provided inputs.

Jeff

jdallen75

@Peachyco One other question: I'm not too familiar with these tools yet, but do you know if it's possible to use the Table, Layout, and Render (to Excel) tools, to allow the sub-table outlined in red to appear beneath that outlined in green? My thinking is to attempt to keep B2:C6 as numeric data types, and then drop the red sub-table beneath it. This is a stretch, but worth asking.

Shift Subtable.jpg

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703