Hi all,
I am trying to set a formula using the net present value function to sum a column of numbers. This is in order to calculate the cost of a rising annuity.
i have separately calculated the column of numbers, but because I only want to sum a certain number of them I need to set the formula precisely.
I have tried the following commands;
Sheet.getCellByPosition(8,12).Formula = “=NPV(H3,G15:G” & 15+lifeExpectancy% & “)*(1+H2)”
and
Sheet.getCellByPosition(8,12).setFormula("=NPV(H3,G15:G" & 15+lifeExpectancy% & “)*(1+H2)”)
where lifeExpectancy% is a positive integer.
Both command place the formula I would expect in the target cell;
=NPV(H3,G15:G25)*(1+H2)
But in both cases it doesn’t calculate but instead gives me error:508
If I copy and paste the formula into that or any other cell it then calculates as expected.
Is this a bug, or am I doing something wrong?
It seems to work fine with other formulae, and appears to be related to the concatenation of rows being summed. I’d rather not have to iteratively build an array of rows to insert into the formula - can anyone think of another way of doing it please?
I am on version 6.1.1.2 on Windows 10