Hi,
we have discovered a strange problem reading cell values with a formula in a file saved with Excel 365. When I open this file with Excel 2016 and just save it, the GetCellValue-function works as expected. Here are some screenshots:
Excel 365:
Excel 2016:
Excel 365 file:
EIOPA_RFR_20230731_Term_Structures-1.xlsx (1.9 MB)
With kind regards,
Sebastian
Hi,
This is a strange case, I am not sure exactly what is happening, but let me explain what I do know:
<c r="C11" s="48">
<f>ROUND(RFR_spot_no_VA!C11 + MAX(0.01,Shocks!$E11*ABS(RFR_spot_no_VA!C11) ),5)</f>
<v>0.01</v>
</c>
That is, the formula is =ROUND(RFR_spot_no_VA!C11 + MAX(0.01,Shocks!$E11*ABS(RFR_spot_no_VA!C11) ),5) and the result of the formula is 0.01. This is what is saved inside the file.
When you open the file with FlexCel, if you do not explicitly recalculate, FlexCel will show you the result Excel calculated and saved inside the file. This is because the Excel results are the "most trusted" ones, and FlexCel recalculation could be different or not support some formulas, or the formulas might depend in values from linked files, or they might use macros or whatever.
So this file says the result of that formula is "0.01" and FlexCel will display that.
But I am not sure on why your Excel 365 decided to save "0.01" inside the file, in fact, if I open this file here in Excel 365 and resave it, it saves :
<c r="C11" s="36">
<f>ROUND(RFR_spot_no_VA!C11 + MAX(0.01,Shocks!$E11*ABS(RFR_spot_no_VA!C11) ),5)</f>
<v>6.6619999999999999E-2</v>
</c>
Both the AppVersion in the file you sent and in the one I got here after resaving with 365 are
<AppVersion>16.0300</AppVersion>
So in theory they are similar (the Excel you used and the one I used here).
FlexCel by default will just show you what is inside the file, because that's what Excel saved. If the file says that the formula is 1+1 and the result is 3, FlexCel will happily show you a 3.
To change this you can try recalculating with FlexCel on open (with xls.Recalc(true))
If you try recalculating with FlexCel it will show the correct results, but you shouldn't need to, and it is slower and for some files it might not even work if FlexCel doesn't know how to calculate it.
So the question is why this file has this wrong result saved. And it is not just this one, it looks like it saved invalid formula results for all formulas. Using the "Validate Recalc" demo ( Validating FlexCel recalculation (Delphi) | FlexCel Studio for VCL and FireMonkey documentation ), and the "Compare with Excel" button, I see it everything is different (warning, if you run the demo you will have to kill it manually because there are so many differences that it keeps writing them forever). For example:
Sheet:Spot_NO_VA_shock_DOWN --- Cell:BB103 --- Calculated: 0.06739 Excel: 0 dif: NaN formula: =ROUND(IF(RFR_spot_no_VA!BB103<0, RFR_spot_no_VA!BB103, RFR_spot_no_VA!BB103 - Shocks!$D103*ABS(RFR_spot_no_VA!BB103 )),5)
Sheet:Spot_NO_VA_shock_DOWN --- Cell:BC103 --- Calculated: 0.02644 Excel: 0 dif: NaN formula: =ROUND(IF(RFR_spot_no_VA!BC103<0, RFR_spot_no_VA!BC103, RFR_spot_no_VA!BC103 - Shocks!$D103*ABS(RFR_spot_no_VA!BC103 )),5)
Sheet:Spot_NO_VA_shock_DOWN --- Cell:C105 --- Calculated: 0.02583 Excel: 0 dif: NaN formula: =ROUND(IF(RFR_spot_no_VA!C105<0, RFR_spot_no_VA!C105, RFR_spot_no_VA!C105 - Shocks!$D105*ABS(RFR_spot_no_VA!C105 )),5)
Sheet:Spot_NO_VA_shock_DOWN --- Cell:D105 --- Calculated: 0.02583 Excel: 0 dif: NaN formula: =ROUND(IF(RFR_spot_no_VA!D105<0, RFR_spot_no_VA!D105, RFR_spot_no_VA!D105 - Shocks!$D105*ABS(RFR_spot_no_VA!D105 )),5)
Sheet:Spot_NO_VA_shock_DOWN --- Cell:E105 --- Calculated: 0.02583 Excel: 0 dif: NaN formula: =ROUND(IF(RFR_spot_no_VA!E105<0, RFR_spot_no_VA!E105, RFR_spot_no_VA!E105 - Shocks!$D105*ABS(RFR_spot_no_VA!E105 )),5)
Sheet:Spot_NO_VA_shock_DOWN --- Cell:F105 --- Calculated: 0.02573 Excel: 0 dif: NaN formula: =ROUND(IF(RFR_spot_no_VA!F105<0, RFR_spot_no_VA!F105, RFR_spot_no_VA!F105 - Shocks!$D105*ABS(RFR_spot_no_VA!F105 )),5)
Sheet:Spot_NO_VA_shock_DOWN --- Cell:G105 --- Calculated: 0.02583 Excel: 0 dif: NaN formula: =ROUND(IF(RFR_spot_no_VA!G105<0, RFR_spot_no_VA!G105, RFR_spot_no_VA!G105 - Shocks!$D105*ABS(RFR_spot_no_VA!G105 )),5)
Sheet:Spot_NO_VA_shock_DOWN --- Cell:H105 --- Calculated: 0.02583 Excel: 0 dif: NaN formula: =ROUND(IF(RFR_spot_no_VA!H105<0, RFR_spot_no_VA!H105, RFR_spot_no_VA!H105 - Shocks!$D105*ABS(RFR_spot_no_VA!H105 )),5)
Sheet:Spot_NO_VA_shock_DOWN --- Cell:I105 --- Calculated: 0.02857 Excel: 0 dif: NaN formula: =ROUND(IF(RFR_spot_no_VA!I105<0, RFR_spot_no_VA!I105, RFR_spot_no_VA!I105 - Shocks!$D105*ABS(RFR_spot_no_VA!I105 )),5)
Sheet:Spot_NO_VA_shock_DOWN --- Cell:J105 --- Calculated: 0.02581 Excel: 0 dif: NaN formula: =ROUND(IF(RFR_spot_no_VA!J105<0,
On the other hand, if I press the same button against the file resaved by me, I get:
Compare with Excel: R:\EIOPA_RFR_20230731_Term_Structures-2.xlsx
Finished Comparing.
**********No differences found!**********
(just to be clear the "Compare with Excel" button in this demo calls "Recalc(true)" on the file, and then compares the values FlexCel recalculates with the ones stored inside the file as calculated by Excel)
So it is just that the original file has all the formula results wrong or not specified. Could it be the case the file was modified by some third party app that didn't calculate the formulas and just saved "0" or "0.1" everywhere as formula results? Excel for sure shouldn't be saving those formula results.
Hi Adrian,
thank you a lot for your quick reply and detailed explanations!
This is really a strange behaviour, espacially since it is a file of an official european authority...
I will check if the recalc can be a solution or if we just escalate it to the european authority.