The Definition of a Value in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office 20 20 people found this article helpful
The Definition of a Value in Excel
There are a few different meanings for the term value
By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on April 21, 2020 Tweet Share Email Tweet Share Email In This Article
Expand Jump to a Section Value Types Displayed vs. Actual Error Values #VALUE Errors Constant Values VALUE Function In a Microsoft Excel spreadsheet, values can refer to text, dates, numbers, or Boolean data. The type of value depends on the data to which it's referring. Before spreadsheet software was invented, the term "value" in relation to a spreadsheet meant only numeric data. In the case of digital spreadsheets, like Microsoft Excel, the term "number data" is used to describe numeric data instead. Definitions in this article apply to Excel 2019, Excel 2016, 2013, and 2010, as well as Excel for Mac, Excel for Microsoft 365, and Excel Online. Types of Values in Excel
If someone refers to a value in Excel, they may be referring to any of the following types of data: Text: String data, such as "High" or "Low."Dates: A calendar date, such as "20-Nov-2018."Numbers: Numeric data, such as 10 or 20.Boolean: A result of a logical comparison, such as TRUE or FALSE. A "value" might also refer to a condition you define in a spreadsheet filter to view only data that you care about. For example, you may set a filter to see only rows where the value in column A is the name of an employee, like "Bob." Displayed Value vs Actual Value
There are three things that define the value that gets displayed in a cell: the formula for that cell, cell formatting, and the result When you view an Excel spreadsheet, you'll see only the result in each cell. However, if you click on the cell, you'll see the formula for that cell in the formula field at the top of the spreadsheet. Formulas and formatting determine what results get displayed in each cell in different ways. A formula uses various Excel functions to perform a calculation. The result of that calculation is displayed in the cell. You can format cells in Excel to display values in financial, decimal, percentage, scientific, and many other formats. You can also set how many decimal points get displayed. For example, the formula for cell A2 that divides number values in A1 and B1 would be =A1/B1 with a result of 20.154. However, you may set the formatting for cell A2 to display only two decimal points. In this case, the value in A2 would be 20.15. Error Values
The term "value" is also associated with error values, such as #NULL!, #REF!, and #DIV/0!, which are displayed when Excel detects problems with formulas or the data in the cells they reference. These are considered values because you can include them as arguments for some Excel functions. For example, if you create a formula in cell B3 that divides the number in A2 by the blank cell A3, this results in the value #DIV/0!. This is because the blank cell is treated as zero, which results in an error value #DIV/0!. This error value means "divide by zero error." #VALUE Errors
Another error value is #VALUE! This error occurs when a formula includes references to cells containing data types that are incorrect for the formula you're using. If you use formulas that perform an arithmetic operation such as addition, subtraction, multiplication, or division, but you reference a cell with text instead of a number, the result will be the #VALUE! error value. For example, if you type the formula =A3/A4 where A3 contains the number 10 and A4 contains the word "Test," the result will be #VALUE!. This is because Excel can't divide a number by a text value. Constant Values
Excel also has a set of special functions that return fixed values. These formulas don't require you to reference any other cells as arguments. Some examples include: PI: Returns the constant value of Pi (3.14).TODAY: Returns today's date.RAND: Returns a random number. The value data type returned by these functions depends on the function. For example, the TODAY() function returns a date value. The PI() function returns a decimal value. The VALUE Function
One more definition of the term "value" in Excel is in reference to the VALUE function. The VALUE function converts text into a number, as long as the text represents a number in some form. The input argument for the VALUE function can be text inserted directly into the function, or you can reference a cell in the spreadsheet that contains the text you want to convert. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Round Numbers Down in Excel With the ROUNDDOWN Function Use Excel's EOMONTH Function to Add or Subtract Months How to Use the Round Function in Excel How to Use Excel's MROUND Function Ignore Error Values When Finding the Average in Excel Use the Excel RIGHT Function to Extract Characters How to Use the Excel MID Function How to Use the DAY function in Excel Round up Numbers in Excel With the ROUNDUP Function Perform Multiple Calculations With Excel Array Formulas How to Use the Excel DATE Function Relative, Absolute, and Mixed Cell References in Excel and Sheets How to Use the Excel TRUNC Function How to Use the IF-THEN Function in Excel How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT How to Calculate Weighted Averages in Excel With SUMPRODUCT Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies