Finding the MIN of the MOD to a range of cells
Results 1 to 10 of 10

Thread: Finding the MIN of the MOD to a range of cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Posts
    989
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Finding the MIN of the MOD to a range of cells

    I have the range below and would like to find the MIN to a range of cells for the MOD of the value. In H9 I'm looking for the MAX, but in H10 I would like the MIN.

    Here is the formula I'm using for the MAX. Is this the best method to use? Cells H6 and H7 are actually 0.00. I just have the custom format to hide 0.00.

    =MAX(MOD(H2,1),MOD(H3,1),MOD(H4,1),MOD(H5,1),MOD(H6,1),MOD(H6,1),MOD(H7,1))

    Data Range
    H
    1
    FRAC
    2
    0.06
    3
    0.20
    4
    0.30
    5
    0.44
    6
    7
    8
    1
    9
    0.44
    10
    0.20

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,479
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Finding the MIN of the MOD to a range of cells

    Try:

    =AGGREGATE(14,6,MOD(H2:H7,1)/(H2:H7<>0),1)

    for the max, and

    =AGGREGATE(15,6,MOD(H2:H7,1)/(H2:H7<>0),1)

    for the min.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Posts
    989
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding the MIN of the MOD to a range of cells

    Thank you Eric. Works great for my needs.

  4. #4
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,302
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Finding the MIN of the MOD to a range of cells

    Hello,

    Not sure to understand your basic Mod function ...

    =Mod(H2,1) should result into H2 ... no ...???

  5. #5
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,479
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Finding the MIN of the MOD to a range of cells

    FryGirl, Glad to help!

    James, the MOD function in Excel has an odd quirk. If you use it on a number with decimals, MOD(12.34, 1) will return the decimals, or .34.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  6. #6
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,302
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Finding the MIN of the MOD to a range of cells

    Quote Originally Posted by Eric W View Post

    James, the MOD function in Excel has an odd quirk. If you use it on a number with decimals, MOD(12.34, 1) will return the decimals, or .34.
    Granted ...

    But her list does not show a single integer ... !!!

  7. #7
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,479
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Finding the MIN of the MOD to a range of cells

    Quote Originally Posted by James006 View Post
    Granted ...

    But her list does not show a single integer ... !!!

    Granted! And if that's the case, a simple MAX(H2:H7) and MIN(H2:H7) will work. I didn't make that assumption though, although it might have been worth asking.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  8. #8
    Board Regular
    Join Date
    Nov 2008
    Posts
    989
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding the MIN of the MOD to a range of cells

    That is correct. I could have a value of 6.67 so I need the .67.

    How about a fellow up question.

    Data Range
    Y
    3
    1.80
    4
    2.70
    5
    4.00


    With this range, I would like to find as the MIN, .70, not the 4.00
    Last edited by FryGirl; Aug 23rd, 2019 at 03:05 PM.

  9. #9
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,479
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Finding the MIN of the MOD to a range of cells

    Try:

    =AGGREGATE(15,6,1/(1/MOD(H2:H7,1)),1)
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  10. #10
    Board Regular
    Join Date
    Nov 2008
    Posts
    989
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding the MIN of the MOD to a range of cells

    Yes Eric that does it. Thank you.

Some videos you may like

User Tag List

Tags for this Thread

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
  •