Excel formula translation corrupts sheet

Occasional Visitor

Dear experts, receiving an Excel from a foreign country leads to an unintended translation of the formula in a series of cells, which leads to lots of cells giving #VALUE fault messages. What could solve this?

Schermafbeelding 2021-02-09 om 21.38.04.png

3 Replies


The brackets { } around the formula in your screenshot indicate that it has been saved as an array formula. Try saving it as an ordinary formula:

- Select the cell.

- Press F2.

- Press Enter without changing anything.



#VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing." The error is very general, and it can be hard to find the exact cause of it.


Read this reference


The #VALUE error is a bit tricky because some functions automatically ignore invalid data. For example, the SUM function just ignores text values, but regular addition or subtraction with the plus (+) or minus (-) operator will return a #VALUE! error if any values are text.

You can read this MVP blog.