Excel avoids the generation of formula error values

Source: Internet
Author: User
Tags ranges

① error value: # # # # # # # #

Meaning: The data entered into the cell is too long or the result of the cell formula is too large to make the result appear in the cell. or the date and time format of the cell to do subtraction, there is a negative value.

Workaround: Increase the width of the columns so that the results can be fully displayed. If a negative value is caused by a date or time subtraction, you can change the format of the cell, such as changing to text format, and the result is a negative amount of time.

② error value: #DIV/0!

Meaning: An attempt to divide by 0. This error usually occurs when the divisor is 0, the divisor in the formula uses an empty cell or a cell reference that contains a 0-valued cell.

Workaround: Modify the cell reference, or enter a value that is not zero in the cell used as a divisor.

③ error value: #VALUE!

Meaning: Enter a mathematical formula that references a text item. If you use an incorrect parameter or operator, or you cannot correct a formula when you perform an AutoCorrect formula feature, an error message #value! is generated.

Workaround: You should confirm that the operator or parameter you want for the formula or function is correct, and that the formula refers to a cell that contains a valid number. For example, if cell C4 has a number or logical value, and cell D4 contains text, the system cannot convert the text to the correct data type when the formula =c4+d4 is calculated, thus returning the error value #value!.

④ error value: #REF!

Meaning: Deletes the range of cells that are referenced by the formula.

Workaround: Either restore the referenced cell range or reset the reference range.

⑤ error value: #N/A

Meaning: No information is available for the calculation to be performed. When you build a model, users can enter #n/a in the cell to indicate that they are waiting for data. Any cell that references a #n/a value will return #n/a.

Workaround: Fill in the data in the cell waiting for the data.

⑥ error value: #NAME?

Meaning: The formula uses text that is not recognized by Excel, such as a wrong name, or a deleted name, which is also generated if the text string is not enclosed in double quotes

WORKAROUND: If you use a nonexistent name to produce such an error, you should confirm that the name you are using does exist; if it is a name, the function name should be spelled incorrectly; enclose the text string in double quotes; Confirm that all the range references used in the formula use a colon (:).   For example: SUM (C1:C10). Notice that the text in the formula is enclosed in double quotes.

⑦ error value: #NUM!

Meaning: An invalid parameter is supplied to a worksheet function, or the result of a formula is too large or too small to be represented on the worksheet.

Workaround: Verify that the parameter types used in the function are correct. If the formula result is too large or too small, modify the formula so that the result is between -1x10307 and 1x10307.

⑧ error value: #NULL!

Meaning: Inserts a space between the two ranges in the formula to represent the intersection, but the two ranges do not have common cells. For example, input: "=sum (a1:a10 c1:c10)", this will happen.

Workaround: Remove the space between the two ranges. The upper type can be changed to "=sum (A1:A10, C1:C10)"

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.