2

I have not found division of real numbers (ie, /) in worksheet functions. As a consequence, to evaluate =SUM(2,SUM(30,40)/3), we cannot use one expression ctx.workbook.functions.sum(1,ctx.workbook.functions.sum(30,40)/3); we have to do ctx.sync two times:

function test () {
    Excel.run(function (ctx) {
        var result = ctx.workbook.functions.sum(30,40);
        var result2;
        result.load();
            return ctx.sync()
                .then(function () {
                    result2 = ctx.workbook.functions.sum(1,result.value/3);
                    result2.load(); })
                .then(ctx.sync)
                .then(function () {
                    console.log(result2.value); });
    }); 
}

That means, if there are several / in one expression, we have to use even more ctx.sync to evaluate it, which is very tedious (and especially hard to be automatically constructed).

So it would be really great to either find the worksheet function or a workaround for /, so that we could still evaluate an expression containing / in one step.

PS: it seems that there is no worksheet function for +, *, - either, but we could use workarounds: sum for +, product for *, and sum(..., product(-1, ...) for -.

SoftTimur
  • 5,630
  • 38
  • 140
  • 292

2 Answers2

1

At least for dividing by a constant (e.g., 3), isn't that the same as product(something, .33333333)

Michael Zlatkovsky - Microsoft
  • 8,424
  • 1
  • 33
  • 46
  • I don't know the dividend and the divisor... So now, to answer [my previous question](http://stackoverflow.com/questions/38329416/evaluate-a-sub-formula-of-a-formula), theoretically, given any sub-formula, we could construct automatically one string with worksheet functions, and then use `eval` and `ctx.sync`... glad to know it is doable... thanks for your help... – SoftTimur Jul 12 '16 at 21:14
1

One solution is to use a combination of the product function and the power function with the exponent -1. This solution will work even if the denominator (divisor) is a variable and not a constant.

var myNumerator = 888, myDenominator = 4;
var funcs = ctx.workbook.functions;
var result = funcs.product(myNumerator,funcs.power(myDenominator,-1));

In your specific example to evaluate =SUM(2,SUM(30,40)/3), the code would be:

result = funcs.sum(2,funcs.product(funcs.sum(30,40),funcs.power(3,-1)));
Michael Saunders
  • 2,662
  • 1
  • 12
  • 21
  • I don't think I would have thought of this capability without your postscript anyway, so thanks for adding some new tricks to our API arsenal :) – Michael Saunders Jul 12 '16 at 21:03
  • Very cool indeed. @SoftTimur, note that these functions chain in a way that any function accepts a previous function's result, so you can do the calculation just like Michael Saunders wrote it above, with only a single ".load" and ".sync" at the very end. – Michael Zlatkovsky - Microsoft Jul 12 '16 at 22:38