Formula Error

Aspose.Cells Product Family
1

Hi,

I am confused at the following error when I compute the following formula:

If I try to compute the following formula:
=IF(E2<20,1*(1+E1),2*(1+E1))

Where E1 = 0, E2 = 0

It returns '#VALUE!'

Thank you, Gi.

2

I am using Aspose Cells 1.9.3 for Java

3

Hi,

I have tested the formula, the result is correct, following is my test code and the attachment is the result file:

Workbook workbook = new Workbook();
Cells cells = workbook.getWorksheets().getSheet(0).getCells();
Cell cell = cells.getCell("E1");
cell.setValue(0);
cell = cells.getCell("E2");
cell.setValue(0);
cell = cells.getCell("E3");
cell.setFormula("=IF(E2<20,1*(1+E1),2*(1+E1))");
workbook.calculateFormula();
System.out.println("|" + cell.getValue() + "|");
workbook.save("t.xls");

4

Hi,

If the value of cell E1 and E2 you set in your case are not number, but String, then the formula will give this error.

I think you can set the value as number, just as I did in the test code, to avoid this issue.

And we will look into it to determine whether it is possible to convert String to number automatically, but I am afraid that maybe it is a burden for performance.

5

Thank you for your quick response.

After examining the workbook, I noticed that one of my fields was in fact a string field. This wasn’t apparent to me because my system is autogenerating the workbooks on the fly, but your message about a field being a string helped greatly.

But yes, it would be extremely helpful if Strings could be automatically converted to numbers, if not, maybe an error message that may hint at this.

Again, thanks!

6

Hi,

Please try this fix, now we support converting String value to Number automatically when it expects number in formula calculation.