How To Get A SMALL Array That Only Pulls Values Betwen Two #'s

[aedctalk]

=SMALL((F42:AS42,F112:AS112),1) )

Is there a way to modify this above code to only show a value that is greater than -50 and under +50

If so how exactly would i do this? Thank you SOOO MUCH!

completely stuck

If the value isnt within this range then i dont want it to be considered... thanks :P

 

[Aladin Akyurek]

=SMALL((F42:AS42,F112:AS112),1) )

Is there a way to modify this above code

This not code. Rather a formula, probably not well-formed, unless both F42:AS42 and F112:AS112 are numeric...

to only show a value that is greater than -50 and under +50

If so how exactly would i do this? Thank you SOOO MUCH!

completely stuck

If the value isnt within this range then i dont want it to be considered... thanks :P

Work with small samples and desired results when posting questions... How about posting just:

F42:K42 and F112:K112

as sample along with the desired result?

 

[aedctalk]

that makes sense to do that. Thanks.

 

[T. Valko]

=SMALL((F42:AS42,F112:AS112),1) )

Is there a way to modify this above code to only show a value that is greater than -50 and under +50

If so how exactly would i do this? Thank you SOOO MUCH!

completely stuck

If the value isnt within this range then i dont want it to be considered... thanks :P

I'm assuming you mean the range greater than -50 and under +50 literally. Typically, when folks want a range "between" values what they really mean is:

>= the lower boundary and <= the upper boundary.

Not very elegant...

Use an intermediate cell to get a count of numbers that fall within the range.

A1:

=SUM(COUNTIF(F42:AS42,">-50")-COUNTIF(F42:AS42,">=50"),COUNTIF(F112:AS112,">-50")-COUNTIF(F112:AS112,">=50"))

Then test that cell to see if it's >0. If so then calculate the min value.

B1:

=IF(A1>0,SMALL((F42:AS42,F112:AS112),COUNTIF(F42:AS42,"<=-50")+COUNTIF(F112:AS112,"<=-50")+1),"")