I get #VALUE! from my sheet

[JeppeDonslund]

In my spreadsheet I have 300 rows, and in 60 of them I get #VALUE! from a field that is made with a formular, adding two fields from another sheet.

I don't get why it is only some of them and not all 300 rows.

What could be wrong? In the sheet I can see the right value.

[MarkBaker]

Nor do I get what might be wrong; but without a bit more detail I can't even think what it might be.
At the very least, I'd need to know what those formulae are, and preferable with sample data that's used in the formulae. Is it really a simple addition? Are the fields/cells that are being added together numeric values? That shouldn't cause any '#VALUE!' errors.

[JeppeDonslund]

Well, actually it is quite complex. In my XLSX file I see this.

The formula is this: ='SDS Master data'!BE40+'SDS Master data'!AT40

Both fields in SDS Master data are formulas also.

AT40 is this: =SUM(AS40*(AE40/1000)) where AS40 and AE40 both are numbers.

BE40 is this: =(BD40/AX40)*AV40 that contains of both numbers and other formulas.

Maybe it is because of all the formulas it is going wrong.

[MarkBaker]

The level of nested formulae shouldn't be an issue; the PhpSpreadsheet Calculation Engine is perfectly capable of handling many levels of nesting, even with recursion (self-referential).

The PhpSpreadsheet Calculation Engine does have a debugger: you can see how it can be used in this gist

That may provide some clues to identify where the problem lies.

[JeppeDonslund]

I think I found it. I worked my way backward throught the formulas and fields and found that some fields with the format "CURRENCY" was typed as 12.48 € and not just 12.48.
I will change all that fields and see if that helps.

[MarkBaker]

There is actually a PR to resolve formatted currency strings as numeric values that should be a part of the next release PR #3189
We already handle basic numeric strings, and fractions; and handling for formatted percentages as numerics was merged to master earlier this month.