I need to open an Excel workbook (xlsx) with predefined formulas, input values to some of its cells and then recalculate the formulas and get the results.
Unfortunately, there is one formula that Aspose won’t calculate properly and always returns an error. But when I open the resulting workbook, the result is correct, so I guess its something with Aspose Cells and not the formula itself.
It was entered in the workbook in french by my end users as;
=AP7*LOI.NORMALE.STANDARD.N((1-AO7)^(-0,5)LOI.NORMALE.STANDARD.INVERSE.N(AQ7)+(AO7/(1-AO7))^0,5LOI.NORMALE.STANDARD.INVERSE.N(0,999);VRAI)-AQ7AP7
Aspose translates it as:
=AP7_xlfn.NORM.S.DIST((1-AO7)^(-0.5)_xlfn.NORM.S.INV(AQ7)+(AO7/(1-AO7))^0.5_xlfn.NORM.S.INV(0.999),TRUE)-AQ7*AP7
What caused the _xlfn to be added at the beginning of the functions in aspose only? My workbook is in Excel 2010.
Thanks!
Just to simplify things, I created a new Excel file (the problematic one contained sensitive information).
In cell A1, I entered the following formula:
=LOI.NORMALE.STANDARD.INVERSE.N(G1)
It should be translated as
=NORM.S.INV(G1)
In A2:
=LOI.NORMALE.STANDARD.N(G2;VRAI)
which should become
=NORM.S.DIST(G2,TRUE)
I wrote the following .Net code:
Dim wkb As New Workbook(“C:\POC_Aspose_AMF\Fichiers\Templates Excel\TestNorm.xlsx”)
wkb.CalculateFormula()
Dim f1 As String = wkb.Worksheets(0).Cells(“A1”).Formula
Dim f2 As String = wkb.Worksheets(0).Cells(“A2”).Formula
Dim v1 As Object = wkb.Worksheets(0).Cells(“A1”).Value
Dim v2 As Object = wkb.Worksheets(0).Cells(“A2”).Value
Which return those values:
f1 “=_xlfn.NORM.S.INV(G1)”
f2 “=_xlfn.NORM.S.DIST(G2,TRUE)”
v1 “#NAME?” {String}
v2 “#NAME?” {String}
As you can see, the “_xlfn” prefix was added to both functions and the result is invalid.
I’m currently trying to design a proof of concept with Aspose Cells and I really need to make those formulas work quickly (and I guess they should, since they are not in the list of unsupported formulas)
Thanks!
Hi Maxim,
Thank you for considering Aspose APIs.
I have checked your provided spreadsheet against the latest version of Aspose.Cells for .NET 8.4.2.2. My initial investigation suggests that the API is unable to properly calculate the shared formula as it returns correct results if Workbook.CalculateFormula method isn’t called. After calling the said function the calculated values becomes #NAME? error. I will perform a few more tests before logging it for the product team’s review, and will get back to you with ticket reference.
Hi again,
This is to inform you that I have logged this incident for the product team’s review under the ticket CELLSNET-43675. Please allow us some time to properly investigate the matter, and get back to you with updates in this regard.
We are sorry for the inconvenience caused.
Hi Maxim,
This is to inform you that we have fixed the problem logged earlier as CELLSNET-43675. We will shortly provide the fix here with upcoming maintenance release of Aspose.Cells for .NET.
The issues you have found earlier (filed as CELLSNET-43675) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
Hi,
I took the work of Maxim for the proof of concept with your tools Aspose.Cells.
I tested two problematic functions and there are still a problem. I joined my two proofs files.
I created the file ""Formules.xlsx" with Excel 2013 French version.
Run the following code with Aspose.Cells v8.5.2
Dim workbook As New Aspose.Cells.Workbook("Formules.xlsx")
workbook.Save("FormulesAspose.xlsx")
When I open the file FormulesAspose.xlsx with Excel, formulas are NORM.S.DIST and NORM.S.INV instead of LOI.NORMALE.STANDARD.N and LOI.NORMALE.STANDARD.INVERSE.N
When I extract all files from FormulesAspose.xlsx and I look at the file xl\worksheet\sheet1.xml we see that the function name is NORM.S.DIST instead of _xlfn.NORM.S.DIST like the original document.
Hi François,
Thank you for writing back.
When I extract all files from FormulesAspose.xlsx and I look at the file xl\worksheet\sheet1.xml we see that the function name is NORM.S.DIST instead of _xlfn.NORM.S.DIST like the original document
I have evaluated the scenario as quoted above while using the latest version of
Aspose.Cells for .NET 8.5.2.2, and I am unable to replicate it on my side. Please note, the formulas when checked in xl\worksheets\sheet1.xml appears to be _xlfn.NORM.S.DIST & _xlfn.NORM.S.INV for both source and resultant spreadsheets. Please check the attached output generated with aforesaid release and a snapshot showing the comparison of sheet1.xml in notepad application. Moreover, your provided FormulesAspose.xlsx shows
#NAME? error when calculated manually using Excel application whereas the output.xlsx (generated with v8.5.2.2) calculates the formulas correctly while using Excel application as well as
Workbook.CalculateFormula method therefore the behavior could due to some issue in your current version of the API. Please give a try to the latest release on your side to feed us back with updates in this regard.
When I open the file FormulesAspose.xlsx with Excel, formulas are NORM.S.DIST and NORM.S.INV instead of LOI.NORMALE.STANDARD.N and LOI.NORMALE.STANDARD.INVERSE.N
Please allow me some time to verify the above mentioned concerns and discuss the matter with the product team. However, we may require additional information such as following for thorough investigation.
- Operating system version & architecture
- Target .NET framework version
- Locale/regional settings of operating system
- Architecture & MSO version of Excel 2013 installed on your side
The problem is solved with version 8.5.2.2
Thanks you
Hi François,
Thank you for the confirmation. It is good to know that the problem has resolved by using latest revision of the API. Please confirm that you are also not able to observe the problem as quoted below so we could close this incident.
When I open the file FormulesAspose.xlsx with Excel, formulas are NORM.S.DIST and NORM.S.INV instead of LOI.NORMALE.STANDARD.N and LOI.NORMALE.STANDARD.INVERSE.N
Hi,
Yes I confirm that Excel show the correct name. I see LOI.NORMALE.STANDARD.N and LOI.NORMALE.STANDARD.INVERSE.N.
Hi François,
Thank you for the confirmation. We may now close this incident. Please feel free to contact us back in case you need our further assistance with Aspose APIs.