The VALUE function was introduced in Excel 2007 and is available in all subsequent Excel versions.

#Value Excel: Understand Meaning and Value Error Quickly! 

What Does #Value Mean In Excel?

The VALUE function is found under Excels “Text functions”. This function will convert a text string that represents a number into a number. 

Therefore, the function will convert text that you see in a recognized format (a number, date, or time format) into a numeric value.

In analyzing financial things, the VALUE function is not used much, as Excel automatically converts text to numeric values. 

That being said, it is important for anyone who wants to master the full extent of Excel functions.

How To Use The VALUE Function In Excel?

To see the usages of the function, let us consider a few examples: 

Example 1

Let’s see how this function will perform when we give the following formulas:

Look at the results below:

Example 2

Let’s say an HR team wants to assign a penalty point for an employee who is tardy. For assigning a penalty based on an amount of time delay, you can make use of a nested IF formula.

If we are given the data below:

The right formula to use is:

And let’s see the results below:

The formula initially checks the tardiness by time in cell D5 to see if it’s 0, then zero points are assigned. If the result of the logical test above is FALSE, the formula checks to see if cell C3 is smaller than the next threshold, which is a value of 5 minutes. It will then assign 1.

The same pattern will repeat itself at each threshold. As the tests are run in order, from small to big, there is no need for more complicated bracketing.

The VALUE function is used to make Excel treat the time value at each threshold as a numerical value or number instead of next.

#Value Error

The #VALUE! error shows up when a value is not the expected type. Such cases can occur when cells are left blank, when a function that is expecting a number is given a text value, and when dates are evaluated as text by Excel. Correcting a #VALUE! error is usually just easily rectified by entering the right kind of value.

The #VALUE error is a bit confusing as some functions automatically ignore invalid data. For example, the SUM function just ignores text values, but regular adding or subtracting with the plus (+) or minus (-) operating function will return a #VALUE! error if any values are text.

See examples below to learn formulas that return the #VALUE error, along with options to resolve.

Example #1 – Unexpected Text Value

In the example below, cell C3 consists of the text “NA”, and F2 returns the #VALUE! error:

One option to fix is to put in the missing value in C3. The formula in F3 then works correctly:

Another option in this situation is to shift instead to the SUM function. The SUM function automatically ignores text values:

Example #2 – Errant Space Character(s)

In certain cases, a cell with one or more errant space characters will throw a #VALUE! error, as seen in the screen below:

Notice how cell C3 looks completely empty. However, if cell C3 is selected, it is possible to see the cursor sits just a bit to the right of a single space:

Excel returns the #VALUE! error because a space character is text, so in reality, it is actually just another case of Example #1 above. To fix this error, make sure the cell has no values assigned or is empty by selecting the cell, and pressing the Delete key.

TIP: if you have trouble determining whether a cell is truly empty or not, take the help of the ISBLANK function or LEN function to test it.