Posted to microsoft.public.excel.worksheet.functions
|
|
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
|