How to fix problem with #value

The #VALUE! error means Excel cannot calculate the formula because at least one referenced cell has text, spaces, or special characters instead of a clean numeric or date value, even if it “looks” the same as other cells.

To fix it in this kind of table:

1. Check for hidden text/special characters in the source columns (for example the date or total columns feeding the “Criação”, “Criação MTH”, “Criação YR” fields):
   • In an empty helper column, use =ISTEXT(<cell>) on the cells that feed the formulas (for example =ISTEXT(H2)), then fill down.
   • Any row that returns TRUE has text or a special character and can cause #VALUE!.
2. Clean or re‑enter the problematic values:
   • For cells that should be numbers/dates but are detected as text, delete the contents and retype the value manually.
   • Or use CLEAN/REPLACE in a helper column to remove special characters, then copy → Home > Paste > Paste Special > Values back over the originals.
3. Remove stray spaces in ranges used by the formulas:
   • Select the column with suspect values.
   • Press Ctrl+H (Replace), set Find what to a single space, Replace with empty, then choose Replace All if all spaces are unwanted.
   • If the error persists, turn on a filter (Home > Sort & Filter > Filter), filter for Blanks, then select any “blank” cells that appear and press Delete to clear hidden characters.
4. Use functions that ignore text where appropriate:
   • If formulas currently use operators like + or * across mixed data (numbers plus text), replace them with functions that ignore text, for example:
     • Replace =A2+B2+C2 with =SUM(A2:C2).
     • Replace =A2*B2 with =PRODUCT(A2,B2).
5. Evaluate one of the failing formulas step by step:
   • Select a cell showing #VALUE!.
   • Go to Formulas > Evaluate Formula > Evaluate repeatedly.
   • Excel will show exactly which part of the formula becomes " " (space) or text, indicating which input cell must be cleaned.

After cleaning or retyping the non‑numeric/non‑date cells that the “Criação” columns depend on, the #VALUE! errors will be replaced by the correct calculated results.

References:

• How to correct a #VALUE! error