Finding the MIN of the MOD to a range of cells

[FryGirl]

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
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

FRAC​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

0.06​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

0.20​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

0.30​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

0.44​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

0.44​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

0.20​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

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]

Thank you Eric. Works great for my needs.

 

[James006]

Hello,

Not sure to understand your basic Mod function ...

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

 

[Eric W]

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.

 

[James006]

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]

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]

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

How about a fellow up question.

Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

Y​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1.80​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

2.70​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

4.00​

[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Eric W]

Try:

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

 

[FryGirl]

Yes Eric that does it. Thank you.