Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: MIN Formula question

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does anybody know how to use the MIN formula and EXCLUDE the value of 0?

    I have a column that when a user enters a value, a zero can be the end result. I want it to display the lowest value other than 0.

    Thank you,
    Jason

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-19 12:26, jasono wrote:
    Does anybody know how to use the MIN formula and EXCLUDE the value of 0?

    I have a column that when a user enters a value, a zero can be the end result. I want it to display the lowest value other than 0.

    Thank you,
    Jason
    Ok. I enter 0 in A10 as user. Where do I get the lowest value other than what I entered?

    Care to clarify?

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am doing a spreadsheet on Euclids Formula (using Mod's).

    I have to have enough cells filled with the formula to compute several different circumstances (the GCD might only have 4 divisons on it or it could have 10).

    I would like to take the GCD and display the answer. The problem comes from I don't know which cell will have the final answer in it.

    Sound confusing? I think it does

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Number 1 Number 2
    Enter your numbers >>>>>> 77777 889

    GCD>>> 77777 889
    889 434
    434 21
    21 14
    14 7
    7 0
    0 0
    0 0
    0 0
    0 0
    0 0
    0 0 is>>>>

    Here is what the spread sheet looks like. In this case I would need to return a value of 7. In other cases the final number might be in any column on the left. MIN returns the lowest value which is 0.


    [ This Message was edited by: jasono on 2002-04-19 12:52 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-19 12:51, jasono wrote:

    Number 1 Number 2
    Enter your numbers >>>>>> 77777 889

    GCD>>> 77777 889
    889 434
    434 21
    21 14
    14 7
    7 0
    0 0
    0 0
    0 0
    0 0
    0 0
    0 0 is>>>>

    Here is what the spread sheet looks like. In this case I would need to return a value of 7. In other cases the final number might be in any column on the left. MIN returns the lowest value which is 0.


    [ This Message was edited by: jasono on 2002-04-19 12:52 ]
    Array-enter:

    =MIN(IF(Range-of-GCD-numbers,Range-of-GCD-numbers))

    Insert the true range in the above formula.

    In order to array-enter a formula, hit control+shift+enter at the same time, not just enter.

    Aladin

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 12:51, jasono wrote:

    Number 1 Number 2
    Enter your numbers >>>>>> 77777 889

    GCD>>> 77777 889
    889 434
    434 21
    21 14
    14 7
    7 0
    0 0
    0 0
    0 0
    0 0
    0 0
    0 0 is>>>>

    Here is what the spread sheet looks like. In this case I would need to return a value of 7. In other cases the final number might be in any column on the left. MIN returns the lowest value which is 0.


    [ This Message was edited by: jasono on 2002-04-19 12:52 ]
    One way is to use an array formula:
    if A4:A13 have your left-column figures,
    use
    =MIN(IF(A4:A13>0,A4:A13))
    [you need to enter this with Control-Shift-Enter instead of just Enter]

    Another way is to modify what you have in column B:
    B5 right now has = MOD(A4,B4)
    use this instead = IF(MOD(A4,B4)=0,B4,MOD(A4,B4))
    ...and then use that in each row. It keeps the right-hand column from hitting 0 in the first place.

    HTH

    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

Some videos you may like

User Tag List

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
  •