Error calculating formula and interpreting parenthesis


#1

Hello!
I have this formula in my spreadsheet:

= O24 * J10 * (1-J8/100) *(1 - J9/100)


this formula give an error when execute the program. Where is the error? I changed it to:

= O24 * J10 * (1-J8/100 - J9/100 + J8/100*J9/100)


this formula don't give error but the result is not correct, when validate the formula in excel, the result change and give the correct result.

Aspose excel calculate this:

= O24 * J10 * (1-J8/100 - J9/100 + J8/(100*J9/100))

and Excel calculate:

= O24 * J10 * (1-J8/100 - J9/100 + (J8/100)*(J9/100))

I resolved this issue with this change:

= O24 * J10 * (1-J8/100 - J9/100 + J8*J9/10000)

but Aspose have a error calculating formula and interpreting parenthesis.


#2

I tested it but didn’t find the problem. Which version are you using? Please try v3.2.1 at http://www.aspose.com/Downloads/Aspose.Excel/Default.aspx.


#3

The version is 3.2.0.0. The code is:


foreach (DataRow lDR in lDS.Tables[0].Rows) {
.
.
.

sheet.Cells[“V” + fila].Formula="= O"+ fila + " * J10 * (1 - J8/100) * (1 - J9/100)" ;
.
.
.

fila++;
}

and the error: {“Invalid formula:”= O18 * J10 * (1 - J8/100) * (1 - J9/100)"." }


Ooops, if I write…:

sheet.Cells[“V” + fila].Formula="= O"+ fila + " * J10 * (1-J8/100)*(1-J9/100)" ;

don’t give error, but if I write:

sheet.Cells[“V” + fila].Formula="= O"+ fila + " * J10 * (1 - J8/100) * (1 - J9/100)" ;

give error: {“Invalid formula:”= O18 * J10 * (1 - J8/100) * (1 - J9/100)"." }

?A problem with spaces?

thanks.


#4

Thanks for your report.

Yes. This issue is caused by the extra spaces.

Please try the attached fix.


#5

ok, thanks, I'll test it. This issue is resolved too?


= O24 * J10 * (1-J8/100 - J9/100 + J8/100*J9/100)


this formula don't give error but the result is not correct, when validate the formula in excel, the result change and give the correct result.

Aspose excel calculate this:

= O24 * J10 * (1-J8/100 - J9/100 + J8/(100*J9/100))

and MS Excel calculate:

= O24 * J10 * (1-J8/100 - J9/100 + (J8/100)*(J9/100))


#6

Not fixed in previous fix. But this attached fix does.