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

[sporenta]

I have a list of names in column A and a list of scores (between 1 an
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 thei
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

 

[GS]

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

 

[sporenta]

;1613349']> I have a list of names in column A and a list of score
(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 tha 3
so a total of 3 formulas. The following formulas copied down thei
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)



={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 an 4.

Any ideas?

Try specifying..

AND(<4,>3

...as your criteria

--
Garr

Free uenet access at http://www.eternal-september.or
Classic VB Users Regrou
comp.lang.basic.visual.mis
microsoft.public.vb.general.discussio

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

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

 

[GS]

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

 

[Claus Busch]

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.

 

[sporenta]

Hi,

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

={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.

Claus,

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

=INDEX($A$1:$A$150,SMALL(IF(AND($B$1:$B$150>3,$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 number
that are often updated, and this is just the first stop on a longe
process. It's easier to have other parts of the workbook look a
presorted columns

 

[Claus Busch]

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.

 

[Claus Busch]

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.

 

[Claus Busch]

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.

 

[sporenta]

Hi,

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

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.

Claus,

Thanks for the suggestions. That last one is returning non-erro
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 an
4

 

[Claus Busch]

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$150>4),ROW($1:$150)),ROW(A1)))
and CTRL+Shift+Enter


Regards
Claus B.

 

[sporenta]

Hi,

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



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$150>4),ROW($1:$150)),ROW(A1)))
and CTRL+Shift+Enter


Regards
Claus B.

That did it! Woo-hoo!

Thanks for your help