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

#### FryGirl

##### Well-known Member
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​

<tbody>
</tbody>

#### Eric W

##### MrExcel MVP
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.

#### FryGirl

##### Well-known Member
Thank you Eric. Works great for my needs.

#### James006

##### Well-known Member
Hello,

Not sure to understand your basic Mod function ...

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

#### Eric W

##### MrExcel MVP

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.

#### James006

##### Well-known Member
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 ... !!! :wink:

#### Eric W

##### MrExcel MVP
Granted ...

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

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.

#### FryGirl

##### Well-known Member
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​

<tbody>
</tbody>

<strike></strike>
With this range, I would like to find as the MIN, .70, not the 4.00

Last edited:

#### Eric W

##### MrExcel MVP
Try:

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

#### FryGirl

##### Well-known Member
Yes Eric that does it. Thank you.

1,077,856
Messages
5,336,798
Members
399,103
Latest member
PX04