The #VALUE! error appears in Excel when you use a mathematical operator (+,-,*,/) in conjunction with a text value.
Mathematical Operators
In the example below the formula in row 8 returns a #VALUE! error because it is using a mathematical operator and it refers to a cell that contains text.
If the formula in row 8 had used the SUM function =SUM(A3:A7) the formula would work and would not display the error. It is only when a mathematical operators is used that the error occurs.
Spaces
If a cell contains a space this also counts as text, so although the cell may appear empty it can be the cause of the #VALUE! error.
In row 6 of the example shown below cell D6 contains a space, causing the formula in column F to display the #VALUE! error. Deleting the space in D6 will get rid of the error.
Array Formulas
Another cause of the #VALUE! error is when you should have used an array formula. In the example below I want to create a grand total for this order: I use the SUM function and multiply the two ranges Qty and Unit Price. This will sometimes return the #VALUE! error.
To perform the calculation properly I will need to create an array formula using the keyboard combination CTRL SHIFT ENTER rather than just ENTER to confirm the formula. An array formula is always surrounded by brace brackets eg {=SUM(H2:H8*I2:I8)}