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

1. ## 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. ## 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.

3. ## Re: Finding the MIN of the MOD to a range of cells

Thank you Eric. Works great for my needs.

4. ## 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. ## Re: Finding the MIN of the MOD to a range of cells

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.

6. ## Re: Finding the MIN of the MOD to a range of cells

Originally Posted by Eric W

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. ## Re: Finding the MIN of the MOD to a range of cells

Originally Posted by James006
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.

8. ## 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

9. ## Re: Finding the MIN of the MOD to a range of cells

Try:

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

10. ## Re: Finding the MIN of the MOD to a range of cells

Yes Eric that does it. Thank you.