How to correct a #NUM! Error

In this article, we will learn about How to correct #NUM! Error in Excel.

Excel generates error with a name starting with number sign(#) as soon as you complete a formula.

There are 7 different types of common occurring errors in Excel.

  1. #DIV/0 error : division by zero error
  2. #N/A error : No value available
  3. #NAME? : Excel doesn’t recognize a name.
  4. #NULL! : You specified an intersection of two cell ranges whose cells don’t actually intersect
  5. #NUM! : Problem with a number in the formula
  6. #REF! : Invalid cell reference
  7. #VALUE! : Wrong type of argument in a function or wrong type of operator

Here we will just focus on #NUM error as it can be solved using various explained methods below.

Let’s understand this taking one method at one time

  1. A formula results in a number that’s too large or too small to be shown in Excel. Then its generates a #NUM error
    To fix this, change the formula so that its result is between -1*10307 and 1*10307.
  • Calculation error generates a #NUM error.
    For example square root of a negative number gives #NUM error. So we what we need here is a function that converts the negative numbers to positive before providing the argument to SQRT function.
  • 358
    Use ABS function here.
    359

  • Incorrect information provided as an argument to the function.
    For example where 2 different dates are arguments to a function and excel requires you to input dates in order. End_date cannot be older than start_date.
  • A formula uses a function that iterates, such as IRR or RATE, and it can’t find a result.

    To resolve this enable iteration calculation in Excel.

  • So these are some of the ways to avoid #NUM error in excel.

    Hope you understood how to fix #NUM error in Excel. Explore more articles on Excel function errors here. Please feel free to state your query or feedback for the above article.

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

    Terms and Conditions of use

    The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

    Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube