Formula calculation including text and numeric cells

I have a formula on a template worksheet in cell b50



=c38-b38+c40-b40



c38 and b38 are = “”

i copy that formula across row 50 and see #VALUE! in both

B50 and C50 - but the correct values in D50…



I have tried various formulas including

=IF(ISNUMBER(C38),C38,0)-IF(ISNUMBER(B38),B38,0)+IF(ISNUMBER(C40),C40,0)-IF(ISNUMBER(B40),B40,0)



i also tried something like this

if(iserror(c38-b38),0,c38-b38) + c40-b40



but in all cases i get 0 or #VALUE! in the first 2 columns.



If i add a 3rd column to the formula

eg =c38-b38+c40-b40+d38+d40

i still only see 2 columns of errors



i’m using 4.1.0.2



what might be wrong?

i have just noticed while examining the cell object, that c32 and b38 have type “isString” but d38 over have type “isNull”

I think this may be the souce of the problem.



brad

Hi,

Could you post us your template excel file to show your problem, so that we may resolve the issue.

Thank you.

Regards

Amjad Sahi

Aspose Nanjing Team

It’s attached here.

The problem formula lies in cell b72



I’ve worked around the problem by doing a .clearcontents

on cells b38-z38



Note that cells b38 -z38 are sometimes filled in by a program that reads the information in the template , but if there are no accounts receivable, those lines are simply emptied.

b40-z40 has numeric value in it



brad

Hi brad,

I checked your template file and checked the B72 cell. The formula you are applying in that cell is "=C38-B38+C40-B40" whereas C38 and C40 cells have string data and B38 and B40 cells have numeric data. how can you manipulate them. #VALUE! error occures when you put wrong type of args, say you place string instead of numbers etc. You can concate (&) two strings but cannot add (+) or (-) two strings or string with numbers.

I also did the test, opened your template file with Aspose.Cells and put numeric values into C38 and C40 cells and then saved the excel file. When I open the saved file in MS Excel , I see perfect results in B72 cell.

Regards

Amjad Sahi

Aspose Nanjing Team

Those string values are replaced with the empty string during processing of the form.

When a cell has the empty string value in asponse it maintains its identiy as a type string - instead of switching to type null



Excel assumes an empty string to be = 0, so I think that’s what aspose.cells should do too.



Brad



Hi Brad,

In the earlier version of Aspose.Cells we do set the cell to null when setting empty string, but we find that this may also cause problem in calculating formula.

To set a cell to null, please put a null value to it instead of an empty string. The following piece of code will work fine:

Workbook wb = new Workbook();
wb.Open("d:\\test\\aspose_formula_example.xls");

Cells cells = wb.Worksheets[0].Cells;
cells["C38"].PutValue(null);
cells["C40"].PutValue(null);

wb.CalculateFormula(true);

Console.WriteLine(cells["B72"].Value);