Excel Template Actions - Preserve Excel Array Formulas
I have an Excel template where a cell contains an array formula.
Array Formula: {=AVERAGE(IF($A$2:$A$10>20, $A$2:$A$10))}
I want this formula to intake a data table column, so I am using the step named Excel: Insert a data table into an Excel template.
Inserting a data table into the Excel template creates an issue for my array formula.
The new file created removes the array formula curly braces in my cell. This makes the formula different. It seems that Excel template actions take things that look like a field reference and try to change it into a value. That is my reasoning for why the curly braces get removed in the action's output file.
Are there any recommendations for working around this expected behavior?
I would like to have array formulas on the same rows as the data table rows. So Excel: Combine Spreadsheets would not work since it adds rows above or below rows, rather than to the side.
For reference, the image below shows the end result we need. Currently the curly braces are missing in the output file.
Answers
-
It would be best if this was working in an automation directly and I am not sure if excel macros will ever be able to be supported. In this case you could use some VBA code built into the automation for any affected cells. This can also be applied after the fact to each individual affected cells. An example of the macro is below.
Sub Fix_Lost_Table_Array
Worksheets("Name of affected sheet").Range("D2").FormulaArray = "=AVERAGE(IF(R2C1:R10C1>20, R2C1:R10C1))"
End Sub
1 -
I linked a few related enhancements below that I recommend upvoting if applicable. You can also submit a new enhancement for the Ideas page here: https://community.pushbot.com/post/idea/ideas
Excel: Run macro
https://community.pushbot.com/discussion/64/excel-run-macroExcel: Save spreadsheet to table - Support formula evaluation
https://community.pushbot.com/discussion/576/excel-save-spreadsheet-to-table-support-formula-evaluation1