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

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### 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,095,185
Messages
5,442,889
Members
405,205
Latest member
Marc__

### This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...