Finding the MIN of the MOD to a range of cells

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,022
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
Joined
Aug 18, 2015
Messages
8,811
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.
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,670
Hello,

Not sure to understand your basic Mod function ...

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,811
FryGirl, Glad to help! :cool:

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
Joined
Apr 4, 2009
Messages
3,670
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
Joined
Aug 18, 2015
Messages
8,811
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
Joined
Nov 11, 2008
Messages
1,022
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:

Forum statistics

Threads
1,078,461
Messages
5,340,440
Members
399,375
Latest member
alwayssunny

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top