
I'm using worksheet.Range.FormulaR1C1 to set a array formula. However, the cells in the array formula are not linked together. It's as if I went to each cell, pasted my formula and pressed CTRL+SHIFT+ENTER instead of selecting the required range
for the formula and pressing CTRL+SHIFT+ENTER.
Any way to make this work?
Specifically, I'm trying to use the LINEST function to do a polynomial regression, but you must use a multicell array formula.


Coordinator
Jan 23, 2013 at 8:15 PM

Did you put the array formula in between curly braces? http://closedxml.codeplex.com/wikipage?title=Using%20Formulas&referringTitle=Documentation



Yes, array formulas on a Cell by cell basis are working properly.
Just to show i'm formatting it right here is the line of my code where I set the formula:
peakWorksheet.Range(4, phiIndex * (patternData.NumOfFreqs + 2) + freqIndex + 2, 6, phiIndex * (patternData.NumOfFreqs + 2) + freqIndex + 2).FormulaR1C1 =
string.Format("{{TRANSPOSE(LINEST('{0}'!R{1}C{2}:R{3}C{2},'{0}'!R{1}C{4}:R{3}C{4}^{{1,2}}))}}",
targetWorksheetName,
indexOfMaxDp + EndOfHeaderRowIndex + 2  10,
phiIndex * (patternData.NumOfFreqs + 2) + freqIndex + 2,
indexOfMaxDp + EndOfHeaderRowIndex + 2 + 10,
phiIndex * (patternData.NumOfFreqs + 2) + 1);
If I open up the resulting excel file then highlight the 3 cells that need to be linked for the array formula to work properly, and then press CTRL+Shift+Enter then I get the correct results.
My guess is that the ClosedXML is just looping through each cell in the range and applying the formula, but it's not linking the cells in the range together into an array.


Coordinator
Jan 23, 2013 at 8:35 PM

I think you're right. Please create simple example that reproduces a file the issue. I'm not that familiar with array formulas so I need the simplest example possible. Thanks.



Here's a quick example using the Transpose array function in excel:
namespace ArrayFormulaExample
{
class Program
{
static void Main(string[] args)
{
var workbook = new XLWorkbook();
var example = workbook.Worksheets.Add("example");
example.Cell(1, 1).Value = 10;
example.Cell(1, 2).Value = 20;
example.Range(2, 1, 3, 1).FormulaA1 = "{TRANSPOSE(A1:B1)}";
workbook.SaveAs(AppDomain.CurrentDomain.BaseDirectory + "ArrayFormulaExample.xlsx");
}
}
}
In the resulting excel file, cell A2 should equal 10 and cell A3 should equal 20.
However since the two cells are not part of the same array they both equal 10, its showing us the first value in the array. Now if you select cell A2 and A3, click into the formula bar and press CTRL+SHIFT+ENTER you will see that A3 is now 20 as it should be.


Coordinator
Jan 23, 2013 at 9:14 PM

Thanks for the example. I'm working on it.



Awesome thanks!


Coordinator
Jan 23, 2013 at 11:08 PM

Pick up the latest source code. Thanks.



Works great now, thanks.

