Page 1 of 6 123 ... LastLast
Results 1 to 10 of 58

Find lowest value in range excluding 0

This is a discussion on Find lowest value in range excluding 0 within the Excel Questions forums, part of the Question Forums category; I have been trying to enter a formula to find the lowest value in a range, but I want to ...

  1. #1
    New Member
    Join Date
    Oct 2002
    Posts
    5

    Default

    I have been trying to enter a formula to find the lowest value in a range, but I want to exclude 0's. I haven't been able to figure it out. Any suggestions?

  2. #2
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default

    On 2002-10-11 10:09, karini4 wrote:
    I have been trying to enter a formula to find the lowest value in a range, but I want to exclude 0's. I haven't been able to figure it out. Any suggestions?
    array enter (that is, hit enter while control and shift are depressed)
    =MIN(IF(A1:A4,A1:A4))

    for a range A1:a4. If you array enter correctly the formula will look like this
    {=MIN(IF(A1:A4,A1:A4))}

    good luck

  3. #3
    New Member
    Join Date
    Oct 2002
    Posts
    5

    Default

    Thanks, this worked, but I'd like to know why in case I need to do this again! How does this exclude 0 values?

  4. #4
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default

    Take a look at
    http://www.mrexcel.com/tip011.shtml
    for a good overview of array or CSE formulas.

    The formula can also be written as
    =MIN(IF(A1:A4<>0,A1:A4))
    This basically says you want the minimum of non zeros number.
    =MIN(IF(A1:A4>=0,A1:A4))
    would give you the minimum of postive number (including zeros).

    Back to the first formula, if you had 9,0,-12, and 7 in a range the formula would do this:
    =MIN(IF({TRUE;FALSE;TRUE;TRUE},{9;0;-12;7}))
    =MIN({9;FALSE;-12;7})

    It will only take the minimum of the true values, which -12 in this case.

    Hope that helps a little. I can tell what time it is, but don't always know how to make a watch.

    [ This Message was edited by: IML on 2002-10-11 10:40 ]

  5. #5
    New Member
    Join Date
    Nov 2010
    Posts
    1

    Unhappy Re: Find lowest value in range excluding 0

    I have used the method of find lower number but its applicable for vertical ( coloum) not in horizental ( Row )

    Do i need to add any prefix or sufix parameter to count the lower figure in ROW ( area

    Exp.



    ABCDEF
    221.00220.00190575290180

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    24,851

    Default Re: Find lowest value in range excluding 0

    Welcome to the MrExcel board!

    Do you just need this?
    =MIN(A1:F1)

    If not, please try to give some more detail about what you want to achieve.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  7. #7
    New Member
    Join Date
    Dec 2010
    Posts
    8

    Default Re: Find lowest value in range excluding 0

    Very helpful board, kudos to the moderators.

    My question expands upon this. i have two columns, the first column is a list of numbers (random, 0 through 42). I want the second column to find the corresponding cell that is closest to 0 (sometimes 0 does not exist) and make the value of that corresponding cell to be "3". Does that make sense?

    --------
    | A | B|
    --------
    | 2 | |
    --------
    | 4 | |
    --------
    | 0 | 3|
    --------
    | 9 | |
    --------

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    24,851

    Default Re: Find lowest value in range excluding 0

    Quote Originally Posted by lukeMV View Post
    Very helpful board, kudos to the moderators.

    My question expands upon this. i have two columns, the first column is a list of numbers (random, 0 through 42). I want the second column to find the corresponding cell that is closest to 0 (sometimes 0 does not exist) and make the value of that corresponding cell to be "3". Does that make sense?

    --------
    | A | B|
    --------
    | 2 | |
    --------
    | 4 | |
    --------
    | 0 | 3|
    --------
    | 9 | |
    --------
    Welcome to the MrExcel board!

    If negative numbers are not possible in column A then try this formula copied down.

    Closest to 0

     AB
    22 
    34 
    413
    59 

    Spreadsheet Formulas
    CellFormula
    B2=IF(A2=MIN(A$2:A$5),3,"")


    Excel tables to the web >> Excel Jeanie HTML 4



    If negative numbers are possible, please advise.


    Also, have you considered what result you want if there is more than one number equally close to zero. For example, changing A2 in my sample above to 1 will cause both B2 and B4 to return 3. Is that what you want?
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  9. #9
    New Member
    Join Date
    Dec 2010
    Posts
    8

    Default Re: Find lowest value in range excluding 0

    Actually, that works perfectly. I was thinking it had to be more complex than that.

    There is an instance where I have a negative value. How would you recommend considering that? I would want "0" to be chosen, and if there is no zero to consider the next highest positive integer.

    Thoughts?

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    24,851

    Default Re: Find lowest value in range excluding 0

    This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

    Closest to 0

     AB
    72 
    85 
    9-5 
    1013

    Spreadsheet Formulas
    CellFormula
    B7{=IF(A7=MIN(IF(A$7:A$10>=0,A$7:A$10)),3,"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

Page 1 of 6 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com