+ Reply to Thread
Results 1 to 7 of 7

Find the smallest number in a column that has some #N/A in it.

  1. #1
    Registered User
    Join Date
    09-26-2012
    Location
    socal
    MS-Off Ver
    Excel 2010
    Posts
    48

    Find the smallest number in a column that has some #N/A in it.

    I have a column of numbers and a few of them have #NA in them and I need to find the smallest number in the column. The next cell looks to see if there is more that one of that number.

    =SMALL(B163:B238,COUNTIF(B163:B238,0)+COUNTIF(B163:B238,"<0")+1) This is the formula I use to find the lowest number, but because of other programing changes the column now has #N/A in some of them.

    =IF(COUNTIF(B163:B238,B239)>1,"NO","YES") This is in the next cell and it tells me if there is more than one of the smallest numbers.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Find the smallest number in a column that has some #N/A in it.

    Maybe:
    Please Login or Register  to view this content.
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    This will convert all values <=0 and errors into big number (10^10 ). If it returns 10^10, that means there is no number greater than 0 exist.
    Quang PT

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Find the smallest number in a column that has some #N/A in it.

    Array Formula - Requires CTRL+SHIFT+ENTER

    =SMALL(IF(ISERROR(B163:B238),99^99,B163:B238),SUM(COUNTIF(B163:B238,{"<=0",""}))+1)

    Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Find the smallest number in a column that has some #N/A in it.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This is an array formula, has to be entered with Ctrl + Shift + Enter.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  5. #5
    Registered User
    Join Date
    09-26-2012
    Location
    socal
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Find the smallest number in a column that has some #N/A in it.

    Sixthsense That did the job. Can you help with the second formula, it needs to count the same column and see if there is only one of the smallest number and return a yes or no.

    =IF(COUNTIF(B163:B238,B239)>1,"NO","YES")

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Find the smallest number in a column that has some #N/A in it.

    Try this...

    =IF(COUNTIF(B163:B238,SUMIF(B163:B238,">0",B163:B238))=1,"YES","NO")

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find the smallest number in a column that has some #N/A in it.

    So you want the smallest number greater than 0?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] find smallest Number in a col
    By bnwash in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-12-2013, 10:26 PM
  2. [SOLVED] How to find the smallest number for each category
    By BNCOXUK in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-07-2013, 01:13 PM
  3. Replies: 1
    Last Post: 04-20-2010, 02:34 PM
  4. find the smallest number greater than Zero
    By carsto in forum Excel General
    Replies: 3
    Last Post: 10-06-2006, 04:25 PM
  5. find smallest number not equal to 0
    By dave in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-20-2005, 06:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1