+ Reply to Thread
Results 1 to 8 of 8

find smallest Number in a col

  1. #1
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    find smallest Number in a col

    Something that is driving me to drink is to find a way to find the smallest number in a row.
    I am using the following code
    HTML Code: 
    However this will find the smallest number proving it is positive
    e.g.
    10
    9
    8
    7
    6

    it finds 6 as the smallest But if you add negatives to the list
    10
    9
    8
    7
    6
    -5
    It still only finds 6 and forgets about the minus 5

    How can I get the code to show the negative if it exists in the Range

    As always assistance appreciated




    amllest h

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: find smallest Number in a col

    =MIN function will give you negative
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: find smallest Number in a col

    Hi bnwash,

    The first part of the formula is telling Excel to only work with numbers greater than zero. See if the following (which is untested) is what you're after (I have just removed the IF statement from the formula):

    =MIN(OFFSET(Calculation!$A$2,0,MATCH(Chart!$B$21,Calculation!$B$2:$G$2,0),12,1))

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  4. #4
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: find smallest Number in a col

    As you can see by the Formula example I have included the MIN but it does no find any figure that is a minus

  5. #5
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: find smallest Number in a col

    Sorry Robert I was responding to the first reply
    I will try your suggested code and see what happens
    Thanks

  6. #6
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: find smallest Number in a col

    That's fine. Also make sure your range is correct as the MIN function will return -5 in your posted example.

  7. #7
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: find smallest Number in a col

    hi bnwash. there are many questions:

    1. why is this in the VBA section?

    2. why are you testing a range if it's greater than 0?
    OFFSET(Calculation!$A$2,0,MATCH(Chart!$B$21,Calculation!$B$2:$G$2,0),12,1)>0
    testing a range will give multiple results & becomes an array. if that's what you did, then you definitely won't get -5, because your logical test above will make the negative a FALSE. say you select the portion of the formula above & press F9 to calculate, it will give you:
    {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

    1st TRUE is the header on row 2. not sure what is yours. my simulated scenario is a text. the next 5 values are what you gave me (the positive values), so those are TRUE (>0). the next FALSE is the -5. it is not greater than 0. so when it's not greater than 0, it will not be under consideration:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    you can select the above portion of the formula & press F9 again. you'll get:
    {"header";10;9;8;7;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
    notice my -5 comes out as FALSE. so the MIN is only finding those >0.

    you should be using <>0, supposing you don't want 0 when there aren't negative figures or blanks (considered as 0)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    a sample excel file would help if i got it wrong

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  8. #8
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    Re: find smallest Number in a col

    appreciate your help thanks

+ 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] 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
  2. Replies: 3
    Last Post: 12-24-2010, 06:19 AM
  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