Results 1 to 5 of 5

Thread: #num error

  1. #1

    #num error



    Register for a FREE account, and/
    or Log in to avoid these ads!

    wen type this formula i get an num error when ther is now data available in the cell ""=IF(ROWS(A$1:A36)>COUNTA('process standards LHS'!$E$5:$E$500),"",INDEX(('process standards LHS'!$E$5:$E$500),SMALL(IF('process standards LHS'!$E$5:$E$500<>"",ROW('process standards LHS'!$E$5:$E$500)-ROW('process standards LHS'!$E$5)+1),ROWS(A$1:A36))))""

  2. #2
    Take a look at the office help for the #NUM:

    Occurs with invalid numeric values in a formula or function.

    Possible causes and solutions
    Using an unacceptable argument in a function that requires a numeric argument

    Make sure the arguments used in the function are numbers. For example, even if the value you want to enter is $1,000, enter 1000 in the formula.

    Using a worksheet function that iterates, such as IRR or RATE, and the function cannot find a result

    Use a different starting value for the worksheet function.

    Change the number of times Microsoft Excel iterates formulas.

    Entering a formula that produces a number that is too large or too small to be represented in Microsoft Excel

    Change the formula so that its result is between and .

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,243
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Two options:

    1) Evaluate the formula by stepping through it. That should show you what cell area is causing your error. (Formulas-->Evaluate formula-->keep clicking the Evaluate button till you see the error pop up.
    2) Upload a copy of the workbook so we can see what is causing it.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    I have attached the file please check and Reply me .
    And i have mentioned the problem in the same file in sheet - operator interface . please do check and do the needful

    Thank You .
    Quote Originally Posted by Ken Puls View Post
    Two options:

    1) Evaluate the formula by stepping through it. That should show you what cell area is causing your error. (Formulas-->Evaluate formula-->keep clicking the Evaluate button till you see the error pop up.
    2) Upload a copy of the workbook so we can see what is causing it.
    test copy.xlsx

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,243
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    The target column you're looking at has "" in it, which is not blank. This means that your COUNTA is counting every row in it. Change that to COUNT and I think you'll be fine.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •