Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

VALUE Function in Excel

VALUE Function in Excel

Table of Contents

  • VALUE in Excel
  • Value Formula in Excel
  • How to Use?

What is VALUE in Excel?

VALUE in Excel is a built-in Function in Microsoft Excel. It converts text into a numeric value. The function converts text entered in a recognized format into a numeric value.

Value Formula in Excel

The Value Formula in Excel is as follows:

Value Formula

The Value function in Excel has only one argument and this is the required one. The Value formula returns a numeric value.

Where,

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

How to Use VALUE in Excel?

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

VALUE Function in Excel Worksheet

Let’s look at the eight different examples given below. Each example covers a different use case implemented using the VALUE in Excel.

Example #1 – Convert TEXT into Number using VALUE function.

VALUE Example 1

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

Example #2 – Convert TIME of the day into a number using VALUE function.

VALUE Example 2

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

Example #3 – Mathematical Operations using VALUE function

VALUE Example 3

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

Example #4 – Convert DATE into Number using VALUE function

VALUE Example 4

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

Example #5 – Error in VALUE

VALUE Example 5

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

Example #6 – Error in NAME

VALUE Example 6

In this example, cell D2 has a VALUE formula in excel associated with it. So, D2 is a result cell. The argument of VALUE function is ppp which is the text in an inappropriate format i.e. without double quotes (“”) and hence the value cannot be processed. 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! as a result.

Example #7 – Text with NEGATIVE VALUE

VALUE Example 7

In this example, cell D4 has a VALUE formula associated with it. So, D4 is a result cell. The argument of 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

VALUE Example 8

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 0.89 is returned by the Excel VALUE function.

Things to remember about VALUE Function in Excel

  • The VALUE function in Excel converts the text into a numeric value.
  • It converts the formatted text such as date or time format into a numeric value.
  • However, the text to number conversion is normally taken care of by Excel 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 the 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 which is to be converted into the number must be enclosed within double quotes. If not done so, #NAME! is returned indicating the error with the NAME entered.
  • If a non-numeric text such as alphabets is entered as the parameter, then the same cannot be processed by the VALUE function in Excel and returns #VALUE! as a result, indicating the error is with the VALUE generated.

You can download the sample Excel file containing an example of the VALUE function in Excel Worksheet – VALUE Function Excel Template

Recommended Articles

This has been a guide to VALUE Function in Excel. Here we discuss the VALUE Formula in excel and how to use Excel VALUE Function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • FORECAST Excel Function
  • NETWORKDAYS Excel Function
  • TRANSPOSE Excel Function
  • PERCENTILE Excel Function

The post VALUE Function in Excel appeared first on Learn Investment Banking: Financial Modeling Training Courses Online.



This post first appeared on Free Investment Banking Tutorials |WallStreetMojo, please read the originial post: here

Share the post

VALUE Function in Excel

×

Subscribe to Free Investment Banking Tutorials |wallstreetmojo

Get updates delivered right to your inbox!

Thank you for your subscription

×