"There's a problem with this formula"
Excel refuses to save the formula and leaves the cursor in it. It does not say where the mistake is; it is almost always in the separators, parentheses or quotes.
What the message says
The formula does not follow Excel's grammar: Excel cannot read it, so it cannot calculate it.
Why it appears
- The separator from another language:
=SUM(A1;A2)in an English Excel, which expects,. It is the first cause for a formula copied from a colleague whose Excel is in French or German. - A parenthesis opened and never closed, or closed once too often.
- Text without closing quotes:
=IF(A1="Yes,1,0). - An operator with no value:
=A1+.
Making it go away
- Count the parentheses: Excel colours them in pairs as you type.
- Replace semicolons with commas if the formula comes from a European Excel.
- Prefix the formula with an apostrophe to keep it as text while you fix it.
The tool that handles it
Explain a formula reads the formula and shows the exact spot where it stops being readable; Translate a formula moves it from one Excel to the other, separators included.
Frequently asked questions
- The formula works on a colleague's computer: why not on mine?
- Because your Excels are not in the same language, or do not have the same regional settings: the argument separator and the function names change.