Excel VALUE Function

Syntax

The Value formula is as follows:

The VALUE function has only one argument, which is the required one. The VALUE formula returns a numeric value.

Where,

  • text = the text value that is to be converted into a number.

Examples to use VALUE Function in Excel

The VALUE function is a Worksheet (WS) function. As a WS function, it can be entered as a part of the formula in a worksheet cell. Refer to the examples given below to understand better.

Example #1 – Convert TEXT into Number

In this example, cell C2 has a VALUE formula associated with it. So, C2 is a result cell. The argument of the VALUE function is “$1,000,” the text to be converted into the number. The result is 1,000.

Example #2 – Convert the TIME of the day into a number

In this example, cell C4 has a VALUE formula associated with it. So, C4 is a result cell. The argument of the VALUE function is “14:00,” which is the time of the day. So, the result of converting it into a number is 0.58333.

Example #3 – Mathematical Operations

In this example, cell C6 has a VALUE formula associated with it. So, C6 is a result cell. The argument of the VALUE function is the difference between the two values. For example, the values are “1,000” and “500”. So, the difference is 500, and the function returns the same.

Example #4 – Convert DATE into Number

In this example, cell C8 has a VALUE formula associated with it. So, C8 is a result cell. The argument of the VALUE function is “01/12/2000,” which is the text in the date format. So, the result of converting it into the number is 36537.

Example #5 – Error in VALUE

In this example, cell C10 has a VALUE formula associated with it. So, C10 is a result cell. Unfortunately, the argument of the VALUE function is “abc,” which is the text in an inappropriate format. Hence, we cannot process the value. As a result, #VALUE! is returned, indicating the error in value.

Example #6 – Error in NAME

In this example, cell D2 has a VALUE formula in Excel associated with it. So, D2 is a result cell. Unfortunately, the argument of the VALUE function is ppp, which is the text in an inappropriate format, i.e., without double quotes (“). Hence, we cannot process the value.

As a result, #NAME! is returned, indicating the error is with the name provided. The same would be valid even if a valid text value is entered but not enclosed in the double quotes. E.g., VALUE (123) shall return #NAME! Error as a result.

Example #7 – Text with NEGATIVE VALUE

In this example, cell D4 has a VALUE formula associated with it. So, D4 is a result cell. The argument of the VALUE function is “-1,” which is the text containing a negative value. As a result, the corresponding value -1 is returned by the VALUE function Excel.

Example #8 – Text with FRACTIONAL VALUE

In this example, cell D6 has a VALUE formula in Excel associated with it. So, D6 is a result cell. The argument of the VALUE function in Excel is “0.89,” which is the text containing a fractional value. As a result, the corresponding value of 0.89 is returned by the VALUE function.

Things to Remember

  • The VALUE function converts the text into a numeric value.It converts the formatted text such as date or time format into a numeric value.However, Excel normally takes care of the text-to-number conversion by default. So, the VALUE function is not explicitly required.It is more useful when the MS Excel data is to be made compatible with other similar spreadsheet applications.It processes any numeric value less or greater than or equal to zero.It processes any fractional values less or greater than or equal to zero.The text entered as a parameter to be converted into the number must be enclosed within double-quotes. The #NAME! Error is returned if not done, indicating the error with the NAME entered.Suppose a non-numeric text such as alphabets is entered as the parameter. In that case, the same cannot be processed by the VALUE function in Excel and returns #VALUEThe VALUE Function In Excel And Returns #VALUE#VALUE! Error in Excel represents that the reference cell the user has either entered an incorrect formula or used a wrong data type (mostly numerical data). Sometimes, it is difficult to identify the kind of mistake behind this error.read more! as a result, indicating the error is with the VALUE generated.

This article has been a guide to VALUE Function in Excel. Here, we discuss the VALUE formula, how to use it, an Excel example, and a downloadable template. You may also look at these useful functions in Excel: –

  • Value Property in VBAValue Property In VBAIn VBA, the value property is usually used alongside the range method to assign a value to a range. It’s a VBA built-in expression that we can use with other functions.read moreExcel Shortcut Paste ValueExcel Shortcut Paste ValuePasting values is a common procedure that allows us to eliminate any formatting and formulas from the copied cell and paste them into the pasted cell. “Alt + E + S + V” is the shortcut key for pasting values.read moreNETWORKDAYS FunctionNETWORKDAYS FunctionThe NETWORKDAYS function is a date and time function that determines the number of working days between two given dates  and is widely used in the fields of finance and accounting. When calculating the working days, NETWORKDAYS automatically excludes the weekend (Saturday and Sunday).read morePERCENTILE Excel FunctionPERCENTILE Excel FunctionThe PERCENTILE function is responsible for returning the nth percentile from a supplied set of values. read moreConfidence Interval In ExcelConfidence Interval In ExcelConfidence Interval in excel is the range of population values that our true values lie in. read more