Handling Excel Output Summation with Formulae Embedded

[jdallen75]

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:

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.

[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 Can you post your workflow with some fake data in it so I can get a clearer picture of what you're getting and what you're trying to get?

[jdallen75]

@Prometheus here is a simplified workflow and resulting output.

So the requirement is the user enters values for Manual Input 1 and Manual Input 5. Then line 7 (Idx = 6) kicks out the subtotal formula (which itself is a string, making columns C and D strings).

Thanks!

[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?

[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

[Peachyco · Accepted solution]

@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):

 

[jdallen75]

@Peachyco I looked through an older example I had found (where the FORMULACORRECTOR was used) and consistent with your suggestion, the formulae are never in the same column as the Alteryx-calculated subtotals. So I think I can adjust the layout based on this feedback. Thanks, Jeff