Reading wrong cell calculated value

[thomas-rooty]

Good morning,

I am trying to read a cell value at a precise coordinate, that cell contains an addition of two other cells (=A1+B1 for example).
Unfortunately for me, A1 and B1 aren't easy cells that only contains an integer, in fact they're composed of complex functions like IF statements, INDEX(), VLOOKUP(), and references to other sheets.

Here's the cell formula :

=IF(A212<1;"";A212*D212*indices!A212*VLOOKUP('à compléter'!$B$11;Qualité;4;FAUX)*VLOOKUP(C212;age;2;FAUX)*INDEX(Feuil1!$A$226:$F$241;MATCH('à compléter'!$B$10;Feuil1!A$226:$A$241;0);MATCH('Liste matériels et chiffrage'!$D212;Feuil1!$A$225:$F$225;0)))

What my code is doing is reading through the generated excel file using PHPSpreadsheet, it stores the generated.xlsx into a temp directory which allows me to reinstance it into a new spreadsheet.
The funny fact is that my generated excel file when opened contains the correct value, lets assume $179. But when I try to read it from my code I get something else like $359.

I also have to disable the calculationCache but it doesn't seems to fix my bug.

Have you already encountered a case where php returned another value as it should be in the final .xlsx file ?

Unfortunately my code is a bit private because it's made to generate customer contract, but if needed, I could rewrite something similar to reproduce the bug.

[MarkBaker]

It's possible that there's an error in one of those calculations, and Excel functions like INDEX() VLOOKUP() and MATCH() are surprisingly complicated; but without being able to debug the evaluation of your actual formulae (with values), I can't really do much to diagnose the problem.

If you could rewrite something similar, or alternatively debug the formula evaluation process yourself. I've published a gist showing how to enable logging in the calculation engine, and display the internal process of formula evaluation

[thomas-rooty]

Hi @MarkBaker ,

Sorry for the lack of information that I gave you. In fact, I resolved the problem by myself and i'll show you what was causing the bug because I think it could be easily fixed in phpspreadsheet :

I had an INDEX formula containing two MATCH, each for row and column of a table in another sheet, the first MATCH() was for the column and was working without any problem, unfortunately I had an #REF for the first occurence of MATCH() for the row n°225 as shown on the screenshot

My MATCH() for the row was pointing from the red square to the "4", I have invisible values like 1, 2, 3, 4 to calculate something in return and just below "V10" I have an empty cell, which is totally normal. What was causing the bug was that the cell into the red square was empty AND wasn't stylized like the cell below V10, Excel itself returned to me 0 because it knows that it was a part of the matrix but PHPSpreadsheet didn't want to act like Excel and returned me an #REF just because it wasn't stylized the same way, I just had to copy paste the empty cell below V10 to its left and everything works like a charm

Hope this helps and have a good day