Occurs when the wrong type of argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) or operand (operand: Items on either side of an operator in a formula. In Excel, operands can be values, cell references, names, labels, and functions.) is used.
- Click the cell that displays the error, click the button that appears
, and then click Trace Error if it appears.
- Review the possible causes and solutions.
Possible causes and solutions
Entering text when the formula requires a number or a logical value, such as TRUE or FALSE
Microsoft Excel cannot translate the text into the correct data type. Make sure the formula or function is correct for the required operand or argument, and that the cells that are referenced by the formula contain valid values. For example, if cell A5 contains a number and cell A6 contains the text "Not available", the formula =A5+A6 will return the error #VALUE!.
Entering or editing an array formula, and then pressing ENTER
Select the cell or range of cells that contains the array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.), press F2 to edit the formula, and then press CTRL+SHIFT+ENTER.
Entering a cell reference, a formula, or a function as an array constant
Make sure the array constant (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.) is not a cell reference, formula, or function.
Supplying a range to an operator or a function that requires a single value, not a range
Using a matrix that is not valid in one of the matrix worksheet functions
Make sure the dimensions of the matrix (matrix: A rectangular array of values or a range of cells that is combined with other arrays or ranges to produce multiple sums or products. Excel has predefined matrix functions that can produce the sums or products.) are correct for the matrix arguments.
Running a macro that enters a function that returns #VALUE!
Make sure the function is not using an incorrect argument.
United States
Email this link
Printer-Friendly Version
Share
Hide All
