Choose value in a range of data

[dgavin]

Hello,

Column H3-H30 contain values which are changing continuously.

I need a formula for A1 that will......

find the search column H for a value that is betweem 15-20, if there are more than one in the cloumn then choose the lowest value between 15-20.

Thanks

 

[Richard Schollar]

Try:

=SMALL(H3:H30,COUNTIF(H3:H30,"<15")+1)

 

[Richard Schollar]

Unfortunately, the above doesn't restrict to the range 15 to 20. This array formula does (enter using Ctrl+Shift+Enter):

=MIN(IF(H3:H30>=15,IF(H3:H30<=20,H3:H30)))

It will return 0 if there is no value within the specified range).

 

[dgavin]

Thanks Richard.

Now I need a formula in B2 which

row number the lowest value between 15-20, is in

 

[dgavin]

Hi, this formula is working great

=MIN(IF(H3:H30>=15,IF(H3:H30<=20,H3:H30)))

however how can I adapt it so the range is not restricted to 15-20 but instead the range is anything greater than 15.

Thanks

 

[Aladin Akyurek]

Hi, this formula is working great

=MIN(IF(H3:H30>=15,IF(H3:H30<=20,H3:H30)))

however how can I adapt it so the range is not restricted to 15-20 but instead the range is anything greater than 15.

Thanks

Omit the 2nd condition and change >= to >...

=MIN(IF(H3:H30>15,H3:H30))