=SUMPRODUCT(–(INDIRECT(“D1:D”&1+9)=$A$1)) This formula returns value 0.0 when fetch from aspose. But excel sheet shows value 4. Aspose Version 8.2.1.
I have uploaded aspose.xls and Example.java. Try to fetch G7 in Class Data sheet.
Please help me
Hi Vinod,
Thank you for providing the sample spreadsheet.
We have evaluated your presented scenario against the latest version of Aspose.Cells for Java 8.2.1, and we are able to replicate the said issue. Aspose.Cells API returns the incorrect value for the formula in G7 when Workbook.calculateFormula is called. If you comment out that statement the returned value is expected.
The said problem has been logged in our bug tracking system under the ticket CELLSJAVA-41013 for further investigation & correction purposes. Please spare us little time to properly analyze the problem cause, and to provide a fix at earliest. In the meanwhile, we will keep you posted with updates in this regard.
Can you please tell me ticket number CELLSJAVA-41013 is resolved?
Thanks,
Vinod
Hi Vinod,
Thank you for your patience with us.
The ticket CELLSJAVA-41013 has been marked resolved by the core development team. Hopefully, the fix will be delivered with the upcoming maintenance release. As soon as we schedule the said release, we will inform you here.
Hi again,
I have just checked the latest version of Aspose.Cells for Java 8.2.1.1 (attached) against your presented scenario. The latest build calculates the formula value correctly. Please give it a try on your end.
Sorry for the inconvenience.
Babar,
Thanks for your response. Sample formula that I mentioned earlier was working now. It had only one INDIRECT function. However the formula that we are trying to evaluate has multiple INDIRECT functions. And this formula is failing. I have attached the spreadsheet and the program.
Please verify.
Thanks,
Vinod
Hi Vinod,
Thank you for writing back.
We have evaluated the recently shared spreadsheet against the latest version of Aspose.Cells for Java 8.2.1.1, and have received #VALUE while reading the value from D5 after calling the Workbook.calculateFormula method. Please note, the result is correct if Workbook.calculateFormula isn’t called. We have logged this problem in our bug tracking system under a new ticket for further investigation & correction purposes. The ticket Id for your future reference is CELLSJAVA-41021. Please spare us little time to properly analyze the problem cause, and to provide the fix. In the meanwhile, we will keep you posted with updates in this regard.
We are sorry for the inconvenience caused to you.
Babar,
CELLSJAVA-41021 shows resolved. If possible, can you please share me the fix? So that, I can verify it.
Thanks,
Vinod
Hi,
Yes, we have resolved the issue “CELLSJAVA-41021”. Hopefully, we will deliver the fix with the next maintenance release (e.g v8.2.1.2) within the next 3-5 days whereas the official release v8.2.2 is scheduled around the mid of October 2014 which will include the functionality of the fix.
Once we incorporate other enhancements and conduct extensive testing, we will provide you the fix (e.g v8.2.1.2) here.
Thank you.
Hi Vinod,
Thank you for your patience.
The latest version of Aspose.Cells for Java 8.2.1.2 is attached. Please give it a try on your side, and feed us back with your results so we may close the ticket appropriately.
Babar,
Thank you so much for your support. INDIRECT Issue is solved. But we got another issue on AverageIF formula. When we use, CalculateFormula() method for AverageIf, it returns 0.0. But the value on the excel sheet is 858. Please find the attached sample excel sheet and the program. Please help us.
Aspose Version Used: 8.2.1.2
Thanks,
Vinod
Hi,
Thanks for providing us template file and sample code.
After an initial test, I observed the issue. The Workbook.calculateFormula() returns the incorrect value for the formula
“=AVERAGEIF(Data!F110:F65536,”="&“F/M”,Data!AX110:AX65536)*(52/12"),
it returns 0.0 value instead of “858.0”. I used the template file provided
by you and sample code for the issue:
“=AVERAGEIF(Data!F110:F65536,”="&“F/M”,Data!AX110:AX65536)*(52/12"),
it returns 0.0 value instead of “858.0”. I used the template file provided
by you and sample code for the issue:
e.g
Sample code:
try {
Workbook workbook = new Workbook("Averageif sample.xls");
workbook.calculateFormula();
System.out.println(workbook.getWorksheets().get("Output").getCells().get("C53").getValue());
System.out.println(workbook.getWorksheets().get("Output").getCells().get("C54").getValue());
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Output:
0.0
0.0
I have logged a ticket with an id "CELLSJAVA-41035" for your issue. We will look into it soon.
Once we have any update on it, we will let you know here.
Thank you.
Hi Amjad,
Do you have an update on this issue. i am also facing the same issue.
Hi,
I am afraid, the issue is not resolved yet. Our concerned developers were on holidays, they have come back now. Please spare us little more time (3-5 days), hopefully, your issue would be fixed soon.
Thank you.
Amjad,
Issue status is resolved. Can you please share me the latest aspose build jar to verify?
Thanks,
Vinod
Hi Vinod,
Thanks for using Aspose.Cells.
We are pleased
to inform you that we have fixed this issue and we will provide you a
fix in couple of days after some extensive testing. Please spare us some
time. Once, it is available for you, we will let you know asap by
posting the fix in this thread.
Hi,
Thanks for using Aspose.Cells for Java.
We have fixed the issue (CELLSJAVA-41035 - Incorrect value for AverageIf after calling Workbook.calculateFormula() method).
Please download and try this fix: Aspose.Cells for Java v8.2.1.4 and let us know your feedback.
The issues you have found earlier (filed as CELLSJAVA-41035;CELLSJAVA-41021;CELLSJAVA-41013) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
I’m experiencing this same issues in the .NET version of Aspose.Cells 22.5.0. Is there a workaround?