Error calculating formula and interpreting parenthesis

Aspose.Cells Product Family
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

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.

7

@COGEJH,
Aspose.Excel is discarded and no more under active development now. It is replaced by Aspose.Cells that supports all the latest features in MS Excel. You can work with formula using Aspose.Cells by using all the latest formulae available in different spreadsheet management softwares. You can find the list of supported formulas and other advanced features related to formula in the following documents.

Supported Formula Functions
Formulas

For more information about this new product, refer to the following document:
Developer Guide

Download the latest trial version here:
Aspose.Cells for .NET (Latest Version)

Download a complete runnable solution here to test variety of features of this product.