Range Array Formula

Jan 23, 2013 at 7:44 PM

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 multi-cell 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

Jan 23, 2013 at 8:26 PM

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.

Jan 23, 2013 at 8:57 PM

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.

Jan 23, 2013 at 9:43 PM

Awesome thanks!

Coordinator
Jan 23, 2013 at 11:08 PM

Pick up the latest source code. Thanks.

Jan 24, 2013 at 4:52 PM

Works great now, thanks.