Excel Interop：Cells.Formula无法跨语言自动翻译公式的问题求助

The Issue You're Facing #

When you use the Formula property to set a formula like this:

xlWorkSheet.Cells[i, "J"].Formula = string.Format("= MEAN(J3:J{0})", i - 1);

You run into a #NAME? error in French Excel. Even though the Formula property is supposed to auto-translate English function names to the local Excel language, it fails here—likely because MEAN is an alias for the standard AVERAGE function, and Excel's translation engine doesn't handle aliases reliably across locales.

The Fixes #

Here are two solid solutions to get your formula working seamlessly in both English and French Excel:

1. Use the Standard English Function Name

Swap MEAN for AVERAGE (the official standard function name in English Excel). Excel's translation engine reliably converts this to MOYENNE (the French equivalent) when opening the file in French Excel:

xlWorkSheet.Cells[i, "J"].Formula = string.Format("=AVERAGE(J3:J{0})", i - 1);

2. Use FormulaR1C1 for Reliable Cross-Language Support

If you need to stick with MEAN or want a foolproof method that works regardless of function aliases, use the FormulaR1C1 property. This property uses R1C1-style references and requires English function names, and Excel automatically handles translation to the local language:

// R3C refers to row 3, current column (J in this case); R[-1]C refers to the row above the current cell
xlWorkSheet.Cells[i, "J"].FormulaR1C1 = "=MEAN(R3C:R[-1]C)";

This avoids any issues with A1-style reference translation and ensures the function name gets converted correctly to MOYENNE in French Excel.

Why This Works #

• Formula relies on Excel's translation of both function names and A1-style references, which can break with non-standard function aliases.
• FormulaR1C1 uses a universal reference format and strictly requires English function names, so Excel's built-in translation works consistently across all language versions.

内容的提问来源于stack exchange，提问作者Moon Rat