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);