Search code examples
excelexcel-formulaoffice-jsarray-formulas

How to enter Array Formulas

Array formulas are those entered in Excel with Ctrl + Shift + Enter.

With Excel API, when I read an array formula (Range.formulas property), I get something like [["{=SUM(IF(A1:A10>50,1))}"]].

When I try to write this formula back to the worksheet, it enters a constant string (because of the brackets). If I delete the brackets and enter the formula, it will be entered as a normal formula, not as an array.

How can I enter an array formula?

In VBA, when you read an array formula, it returns the formula without the brackets. When you want to enter an array formula, you write range.FormulaArray = =SUM(IF(A1:A10>50,1)) (again, without the brackets).

It would be good to keep both plataforms (JS and VBA) consistent and also provide properties like Range.FormulaArray, Range.HasArray and Range.CurrentArray.

Solution

  • The Range.formulas property can be used to read or set formulas for individual cells within a Range. The following code snippet populates range C3:D5 with values (by specifying a 2-dimensional array of values) and then sets the Range.formulas property (by specifying a 2-dimensional array of formulas) such that the formula in cell D5 (i.e., the cell located in the 3rd row, 2nd column of the specified range) is =SUM(D3,D4).

    Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "C3:D5";
    var values = [["First Number", 1000], ["Second Number", 200], ["Difference", null]];
    var formulas = [[null, null], [null, null], [null, "=SUM(D3,D4)"]];
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.values = values;
    range.formulas = formulas;
    range.load('text');

    return ctx.sync().then(function () {
        console.log(range.text);
        console.log(range.formulas);
    });
}).catch(errorHandlerFunction);

    Re adding support for the additional properties that you've mentioned, if you'd like to add this suggestion to Office Developer Platform UserVoice, other users can vote on it there, enabling the Office PM team to get a sense for overall priority/demand as they plan for future enhancements to the API.

    UPDATE

    Using the Excel JavaScript API to set an array formula (like you would using Ctrl+Shift+Enter in the UI) is not currently supported. Office Developer Platform UserVoice is the correct place to track this feature request.