First Value in defined Range

[saadh]

Could some one please help me with a formula that can give the smallest value within a defined range.

So let's say in column 1 there are vales and in column 3 I have manually copied the smallest values between 1-1.9999, 2-2.9999 and 3-3.9999. Is there a formula I could write in column 3 cells that would allow me to do this?



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1.456[/TD]
[TD][/TD]
[TD]1.456[/TD]
[/TR]
[TR]
[TD]1.896[/TD]
[TD][/TD]
[TD]2.215[/TD]
[/TR]
[TR]
[TD]1.985[/TD]
[TD][/TD]
[TD]3.333[/TD]
[/TR]
[TR]
[TD]2.215[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.665
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.899[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.333[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.885[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.985[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help will be appreciated. Cheers

 

[JackDanIce]

Try:

=MIN($A$1:$A$9,1.9999)

 

[saadh]

Try:

=MIN($A$1:$A$9,1.9999)

Thanks JDice... This pulls out the value for the first range nicely.. Is there a way to put a lower limit too in the formula (so 2-2.999) for the second row/ value in column 3?

Cheers

 

[Tetra201]

Try the following array formulas (to be entered using Ctrl+Shift+Enter, not just Enter):

=MIN(IF((A1:A9>=1)*(A1:A9<2),A1:A9))
=MIN(IF((A1:A9>=2)*(A1:A9<3),A1:A9))
=MIN(IF((A1:A9>=3)*(A1:A9<4),A1:A9))

 

[admiral100]

Hi,

Ctrl+Shift+Enter not just Enter and drag down as needed

=MIN(IF(($A$1:$A$9>=ROW(A1))*($A$1:$A$9 < ROW(A2)),$A$1:$A$9))<row(a2)),$a$1:$a$9))<row(a2)),$a$1:$a$9))<row(a2)),$a$1:$a$9))<row(a5)),$a$1:$a$9))< html=""></row(a2)),$a$1:$a$9))<row(a2)),$a$1:$a$9))<row(a2)),$a$1:$a$9))<row(a5)),$a$1:$a$9))<>

 

[saadh]

Try the following array formulas (to be entered using Ctrl+Shift+Enter, not just Enter):

=MIN(IF((A1:A9>=1)*(A1:A9<2),A1:A9))
=MIN(IF((A1:A9>=2)*(A1:A9<3),A1:A9))
=MIN(IF((A1:A9>=3)*(A1:A9<4),A1:A9))

Works beautifully Tetra.. Thanks