how to correct commen errors Excel 2007

Author: mety Labels::

Correct a ##### error

This error indicates that a column is not wide enough to display all of its content, or that a negative date or time is used in a cell.

Causes

  • The column is not wide enough to display the content.
  • Dates and times are negative numbers.

Resolution

  • Increase the width of the column to fit the text by doing the following:
    1. Select the column by clicking the column header.
    2. On the Home tab, in the Cells group, click Format.

      Excel Ribbon Image

    3. Under Cell Size, click AutoFit Column Width.
  • Shrink the text size of the contents to fit the column by doing the following:
    1. Select the column by clicking the column header.
    2. On the Home tab, in the Alignment group, click the Dialog Box Launcher Button image next to Alignment.

      Excel Ribbon Image

    3. Under Text control, select the Shrink to fit check box.
  • Apply a different number or date format.

    In some cases, you can change the number or date format of a cell to make its contents fit within the existing cell width. For example, you can decrease the number of decimal places after the decimal point or switch from a Long Date to a Short Date format.

  • If you are using the 1900 date system, dates and times in Microsoft Office Excel must be positive values.
  • When you subtract dates and times, make sure that you build the formula correctly.
  • If a formula that you use to calculate dates or times is correct but results in a negative value, do the following to display that value in a format that is not a date or time format:
    1. On the Home tab, in the Number group, click the Dialog Box Launcher Button image next to Number.

      Excel Ribbon Image

    2. In the Category box, click a number format that is not a date or time format.

Correct a #DIV/0! error

Excel displays the #DIV/0! error when a number is divided either by zero (0) or by a cell that contains no value.

Causes

  • Entering a formula that performs explicit division by zero (0) — for example, =5/0.
  • Using a reference to a blank cell or to a cell that contains zero as the divisor in a formula or function that performs division.
  • Running a macro that uses a function or a formula that returns the #DIV/0! error.

Example

When you copy the example data to a blank worksheet, the formulas in cells A3, A4, and A5 all return a #DIV/0! error.

ShowHow do I copy an example?

  1. Select the example in this article.

    Important Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  2. Press CTRL+C.
  3. In Excel, create a blank workbook or worksheet.
  4. In the worksheet, select cell A1, and press CTRL+V.

    Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.



1
2
3
4
5
A
1
0
=A1/0
=A1/A2
=QUOTIENT(A1,A2)

Resolution

  • Make sure that the divisor in the function or formula is not zero (0) or blank.
  • Change the cell reference in the formula to another cell that does not contain a zero or a blank value.
  • Enter the value #N/A in the cell that is referenced as the divisor in the formula.

    Entering #N/A will change the result of the formula to #N/A from #DIV/0! to indicate that the divisor value is not available.

  • Prevent the error value from being displayed by using the IF worksheet function. You can then display 0 or any string as the result.

    For example, if the formula that produces the error is =A1/A2, use =IF(A2=0,"",A1/A2) to return an empty string, or =IF(A2=0,0,A1/A2) to return 0.

Correct a #N/A error

This error indicates that a value is not available to a function or formula

Causes

Resolution

  • Optionally, if error checking is turned on in Excel, click the button that appears next to the cell that displays the error Button image, click Show Calculation Steps if it appears, and then click the resolution that is appropriate for your data.

    Tip Review the following resolutions to help determine which option to click.

  • If you manually entered #N/A in a cell, replace it with actual data if that data is now available. For example, if you entered #N/A in cells where data is not yet available, formulas that refer to those cells also return #N/A instead of attempting to calculate a value. If you enter a value instead, the error should be resolved in the cells that contain the formulas.
  • Make sure that the lookup_value argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) that you entered in a HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function is the correct type of value. For example, verify that you entered a value or a cell reference instead of a range reference.

    For information about using the correct arguments with functions, see HLOOKUP function, LOOKUP function, MATCH function, or VLOOKUP function.

  • By default, functions that look up information in tables must be sorted in ascending order. However, the VLOOKUP and HLOOKUP worksheet functions contain arange_lookup argument that instructs the function to find an exact match even if the table is not sorted. To find an exact match, set the range_lookup argument to FALSE.

    The MATCH worksheet function contains a match_type argument that specifies the order the list must be sorted in to find a match. If the function cannot find a match, try changing the value of the match_type argument. To find an exact match, set the match_type argument to 0.

  • If an array formula has been entered into multiple cells, make sure that the ranges that are referenced by the formula have the same number of rows and columns, or enter the array formula into fewer cells. For example, if the array formula has been entered into a range that is 15 rows high (C1:C15) and the formula refers to a range that is 10 rows high (A1:A10), the range C11:C15 will display #N/A. To correct this error, enter the formula into a smaller range (for example, C1:C10), or change the range to which the formula refers to the same number of rows (for example, A1:A15).
  • Enter all required arguments in the function that returns the error.
  • Make sure that the workbook that contains the worksheet function is open and that the function is working properly.
  • Make sure that the arguments in the function are correct and are used in the correct position.

Correct a #NAME? error

This error occurs when Microsoft Office Excel does not recognize text in a formula.

Causes

  • The EUROCONVERT function is used in a formula, but the Euro Currency Tools add-in is not loaded.
  • A formula refers to a name (name: A word or string of characters in Excel that represents a cell, range of cells, formula, or constant value.) that does not exist.
  • A formula refers to a name that is not spelled correctly.
  • The name of a function that is used in a formula is not spelled correctly.
  • You may have entered text in a formula without enclosing it in double quotation marks.
  • A colon (:) was omitted in a range reference.
  • A reference to another sheet is not enclosed in single quotation marks (').
  • A workbook that you open calls a user-defined function (UDF) that is not available on your computer.

Resolution

Correct a #NULL! error

This error occurs when you specify an intersection of two areas that do not intersect. The intersection operator is a space character between references.

Causes

  • You may have used an incorrect range operator.
  • Ranges do not intersect.

Resolution

  • Optionally, if error checking is turned on in Excel, click the button that appears next to the cell that displays the error Button image, click Show Calculation Steps if it appears, and then click the resolution that is appropriate for your data.

    Tip Review the following resolutions to help determine which option to click.

  • Make sure that you use a correct range operator by doing the following:
    • To refer to a contiguous range of cells, use a colon (:) to separate the reference to the first cell in the range from the reference to the last cell in the range. For example, SUM(A1:A10) refers to the range from cell A1 to cell A10 inclusive.
    • To refer to two areas that don't intersect, use the union operator, the comma (,). For example, if the formula sums two ranges, make sure that a comma separates the two ranges (SUM(A1:A10,C1:C10)).
  • Change the reference so that the ranges intersect. An intersection is a point in a worksheet where data in two or more ranges cross, or "intersect." An example of a formula that includes intersecting ranges is =CELL("address",(A1:A5 A3:C3)). In this example, the CELL function returns the cell address at which the two ranges intersect — A3.

    When you enter or edit a formula , cell references and the borders around the corresponding cells are color-coded.



    Color-coded cell references

    Range Finder color-codes precedent cells

    Callout 1 The first cell reference is B3, the color is blue, and the cell range has a blue border with square corners.
    Callout 2 The second cell reference is C3, the color is green, and the cell range has a green border with square corners.



    • If there are no squares at each corner of the color-coded border, the reference is to a named range.
    • If there are squares at each corner of the color-coded border, the reference is not to a named range.

    Do one of the following:

    • Change references that are not to a named range by doing the following:
      1. Double-click the cell that contains the formula you want to change. Excel highlights each cell or range of cells with a different color.
      2. Do one of the following:
        • To move a cell or range reference to a different cell or range, drag the color-coded border of the cell or range to the new cell or range.
        • To include more or fewer cells in a reference, drag a corner of the border.
        • In the formula, select the reference, and type a new one.
      3. Press ENTER.
    • Change references that are to a named range by doing the following:
      1. Do one of the following:
        • Select the range of cells that contains formulas in which you want to replace references with names.
        • Select a single cell to change the references to names in all formulas on the worksheet.
      2. On the Formulas tab, in the Defined Names group, click the arrow next to Define Name, and then click Apply Names.
      3. In the Apply Names box, click one or more names.

Correct a #NUM! error

This error indicates that a formula or function contains invalid numeric values

Causes

Resolution

  • Optionally, if error checking is turned on in Excel, click the button that appears next to the cell that displays the error Button image, click Show Calculation Steps if it appears, and then click the resolution that is appropriate for your data.

    Tip Review the following resolutions to help determine which option to click.

  • Make sure that the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) that are used in the function are numbers. For example, even if the value that you want to enter is $1,000, enter 1000 in the formula.
  • Use a different starting value for the worksheet function.
  • Change the number of times that Excel iterates formulas by doing the following:
    1. Click the Microsoft Office Button Button image, click Excel Options, and then click the Formulas category.
    2. Under Calculation options, select the Enable iterative calculation check box.
    3. To set the maximum number of times that Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time that Excel needs to calculate a worksheet.
    4. To set the maximum amount of change that you will accept between calculation results, type the amount in the Maximum Change box. The smaller the number, the more accurate the result and the more time that Excel needs to calculate a worksheet.
  • Change the formula so that its result is between -1*10307 and 1*10307.

Correct a #REF! error

This error occurs when a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) is not valid.

Causes

  • You may have deleted cells that were referred to by other formulas, or you may have pasted cells that you moved on top of cells that were referred to by other formulas.
  • You may have used an Object Linking and Embedding (OLE) link to a program that is not running.

    Note OLE is a technology that you can use to share information between programs.

  • You may have linked to a Dynamic Data Exchange (DDE) topic (a group or category of data in the server part of a client/server application), such as "system," that is not available.

    Note DDE is an established protocol for exchanging data between Microsoft Windows-based programs.

  • You may have run a macro that enters a function on the worksheet that returns a #REF! error.

Example

Copy the example data to a blank worksheet, and then delete column D (the entire column). The formulas, which were originally in column E, shift to column D and they all display a #REF! error. If you select cell D2, Excel displays the formula =SUM(B2,C2,#REF!) in the formula bar.

ShowHow do I copy an example?

  1. Select the example in this article.

    Important Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  2. Press CTRL+C.
  3. In Excel, create a blank workbook or worksheet.
  4. In the worksheet, select cell A1, and press CTRL+V.

    Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.



1
2
3
4
5
ABCDE
Region2006 Sales2007 Sales2008 SalesFormula
East227002420011000=SUM(B2,C2,D2)
North174001780010200=SUM(B3,C3,D3)
South18600185009600=SUM(B4,C4,D4)
West218002330010300=SUM(B5,C5,D5)

An error is displayed because the formulas in column E referred to column D and, because column D was deleted, the formula is no longer valid. Instead of repairing the formulas to refer to a different cell — which may not be what you want anyway — Excel displays this error to prompt you to correct the formulas yourself so that you don't get unexpected results. In this case, you repair the formulas by removing ",#REF!" from the formula in D2, and then dragging the formula down to the cells below.

Resolution

Correct a #VALUE! error

Excel can display the #VALUE! error if your formula includes cells that contain different data types. If smart tags are turned on and you position the mouse pointer over the smart tag, the ScreenTip displays "A value used in the formula is of the wrong data type." You can typically fix this problem by making minor changes to your formula.

Causes

  • One or more cells that are included in a formula contain text, and your formula performs math on those cells by using the standard arithmetic operators (+, -, *, and /).

    For example, the formula =A1+B1, where A1 contains the string "Hello" and B1 contains the number 3, returns the #VALUE! error.

  • A formula that uses a math function, such as SUM, PRODUCT, or QUOTIENT, contains an argument that is a text string instead of a number.

    For example, the formula PRODUCT(3,"Hello") returns the #VALUE! error because the PRODUCT function requires numbers as arguments.

  • Your workbook uses a data connection, and that connection is unavailable.

Example

When you copy the example data to a blank worksheet, the formulas in cells A4 and A5 return a #VALUE! error; however, cells A6 and A7 return the correct value of 30.

ShowHow do I copy an example?

  1. Select the example in this article.

    Important Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  2. Press CTRL+C.
  3. In Excel, create a blank workbook or worksheet.
  4. In the worksheet, select cell A1, and press CTRL+V.

    Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.



1
2
3
4
5
6
7
A
10
Hello
20
=A1+A2+A3
=SUM(A1+A2+A3)
=SUM(A1,A2,A3)
=SUM(A1:A3)

Resolution

  • Instead of using arithmetic operators, use a function, such as SUM, PRODUCT, or QUOTIENT to perform an arithmetic operation on cells that may contain text, and avoid using arithmetic operators in the function. Instead, separate the arguments by using commas.
  • Ensure that none of the arguments in a math function, such as SUM, PRODUCT, or QUOTIENT, contain text as an argument.
  • If your workbook uses a data connection, take the steps that are required to restore the data connection or, if it is possible, consider importing the data.

0 comments |

Labels

Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2