how to fix #VALUE!

The format change alone will not fix this, and that is the key point. If a number was typed as text, changing the cell format to Number after that does not turn it into a number. The answers above already cover that part. The next step is to find which input cell is the problem.

Work in a copy of the file, so your original stays safe. In a blank helper column, test each cell your formula uses. Type =ISNUMBER(cell) for each one, using the real cell reference each time. A result of TRUE means Excel sees a number. A result of FALSE means Excel sees text, a logical value, or an error. That FALSE cell is the one to fix.

Also check for a hidden character. Type =LEN(cell) for the same cells. If the length is higher than the characters you can see, there is a hidden space or character in that cell. In your copy, try =TRIM(CLEAN(cell)) for that cell and test the result again.

Then use the built in error check. Select the error cell. Go to Formulas, then Error Checking, then Trace Error. That draws arrows to the cell that feeds the error into your formula.

Do not just wrap the formula in IFERROR. That hides the error. It does not fix the wrong input.

If you share the exact formula and tell us which cell returns FALSE, we can point to the exact fix.

This answer was prepared with help from Muse.

Microsoft reference: IS functions: https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665

Contrary to popular misconception, once digits are entered as Text reformatting the cell as Number does not convert the content of the cell. There are several methods you can use:

• If a red triangle appears in the upper left corner of the selected cell click the adjacent ⚠️ & select Convert to Number, or
• Select the cell then on the Data tab click the Text to Columns tool & click the Finish button, or
• Type the number 1 into a blank cell, copy that cel then use Edit> Paste Special – Multiply

Other methods are available as well.

If the formula looks correct and the cell format is already set to Number or Accounting, the issue may be related to the value or formula being interpreted differently in that specific cell.

You can try these steps:

Check the formula in the Formula Bar and compare it character-by-character with a working cell.

Press F2, then press Enter to force Excel to recalculate the cell.

Check whether the cell is formatted as Text before entering the formula. If so, change it to General or Number, then re-enter the formula.

Try copying the formula from a working cell and adjusting the cell references.

If the error remains, please provide the exact error message (for example, #VALUE!, #NAME?, or #REF!) and the formula you're using. The specific error code will help identify the cause.