return multiple values when within range (greater than and less than)
 

I have a list of names in column A and a list of scores (between 1 and 5) in column B. I want to sort the names out into three categories: scores greater than 4, scores between 3 and 4, and scores less than 3, so a total of 3 formulas. The following formulas copied down their columns return all names with the appropriate scores:

={INDEX($A$1:$B$150,SMALL(IF($B$1:$B$150=4,ROW($B $1:$B$150)),ROW(1:1)),1)}

and

={INDEX($A$1:$B$150,SMALL(IF($B$1:$B$150<3,ROW($B$ 1:$B$150)),ROW(1:1)),1)}

...but I can't make a formula work that returns values between 3 and 4.

Any ideas?

return multiple values when within range (greater than and less than)
 

I have a list of names in column A and a list of scores (between 1 and
5) in column B. I want to sort the names out into three categories:
scores greater than 4, scores between 3 and 4, and scores less than 3,
so a total of 3 formulas. The following formulas copied down their
columns return all names with the appropriate scores:

={INDEX($A$1:$B$150,SMALL(IF($B$1:$B$150=4,ROW($B $1:$B$150)),ROW(1:1)),1)}

and

={INDEX($A$1:$B$150,SMALL(IF($B$1:$B$150<3,ROW($B$ 1:$B$150)),ROW(1:1)),1)}

...but I can't make a formula work that returns values between 3 and 4.

Any ideas?

Try specifying...

AND(<4,3)

...as your criteria.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Not sure if I have the syntax correct, but I get #NUM when I try the following:

={INDEX($A$1:$B$150,SMALL(IF(AND($B$1:$B$150<4,$B$ 1:$B$1503),ROW($B$1:$B$150)),ROW(1:1)),1)}

return multiple values when within range (greater than and less than)
 

Just curious why you don't just sort the range on Col B?

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

return multiple values when within range (greater than and less than)
 

Hi,

Am Wed, 14 Aug 2013 01:41:39 +0100 schrieb sporenta:

I have a list of names in column A and a list of scores (between 1 and
5) in column B. I want to sort the names out into three categories:
scores greater than 4, scores between 3 and 4, and scores less than 3,
so a total of 3 formulas. The following formulas copied down their
columns return all names with the appropriate scores:

={INDEX($A$1:$B$150,SMALL(IF($B$1:$B$150=4,ROW($B $1:$B$150)),ROW(1:1)),1)}

try:
=INDEX($A$1:$A$150,SMALL(IF($B$1:$B$150=4,ROW($1: $150)),ROW(A1)))
and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus,

That works for finding values greater than 4, and when I change it to find values less than 3, it works too. It's finding values in-between 3 and 4 that I still can't get to work. So, working off of your formula I tried:

=INDEX($A$1:$A$150,SMALL(IF(AND($B$1:$B$1503,$B$1 :$B$150<4),ROW($1:$150)),ROW(A1)))

CNTRL+Shft+Enter

But I still get a #NUM error.

Garry, that would be a easy way to go! I'm crunching lots of numbers that are often updated, and this is just the first stop on a longer process. It's easier to have other parts of the workbook look at presorted columns.

return multiple values when within range (greater than and less than)
 

Hi,

Am Wed, 14 Aug 2013 13:24:08 +0100 schrieb sporenta:

That works for finding values greater than 4, and when I change it to
find values less than 3, it works too. It's finding values in-between 3
and 4 that I still can't get to work.

filter your column B with number filter "is not equal 3"


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

return multiple values when within range (greater than and less than)
 

Hi again,

Am Wed, 14 Aug 2013 15:25:11 +0200 schrieb Claus Busch:

filter your column B with number filter "is not equal 3"

or try:
=INDEX($A$1:$A$150,SMALL(IF(($B$1:$B$150<3),ROW($ 1:$150)),ROW(A1)))
and enter the formula with CTRL+Shift+Enter


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

return multiple values when within range (greater than and less than)
 

Hi,

Am Wed, 14 Aug 2013 15:30:27 +0200 schrieb Claus Busch:

=INDEX($A$1:$A$150,SMALL(IF(($B$1:$B$150<3),ROW($ 1:$150)),ROW(A1)))

or try:
=INDEX(A$1:A$150,SMALL(IF((B$1:B$150<3)+(B$1:B$150 =4),ROW($1:$150)),ROW(A1)))


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus,

Thanks for the suggestions. That last one is returning non-error results, but too many, unfortunately. When I SHFT+CNTRL+ENTER:

=INDEX(A$1:A$150,SMALL(IF((B$1:B$150<4)+(B$1:B$150 =3),ROW($1:$150)),ROW(A1)))

...it returns all the names, not just the ones that fall between 3 and 4.

return multiple values when within range (greater than and less than)
 

Hi,

Am Wed, 14 Aug 2013 18:08:54 +0100 schrieb sporenta:

Thanks for the suggestions. That last one is returning non-error
results, but too many, unfortunately. When I SHFT+CNTRL+ENTER:

=INDEX(A$1:A$150,SMALL(IF((B$1:B$150<4)+(B$1:B$150 =3),ROW($1:$150)),ROW(A1)))

..it returns all the names, not just the ones that fall between 3 and
4.

I thought you want the names <3 and =4.
For the names between 3 and 4 try:
=INDEX($A$1:$A$150,SMALL(IF(($B$1:$B$150=3)-($B$1:$B$1504),ROW($1:$150)),ROW(A1)))
and CTRL+Shift+Enter


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

That did it! Woo-hoo!

Thanks for your help.