Thread: is this an IF function...?

In a cell at the bottom of a column I need to list students names from
column A who's scores in another column are greater or less than a target
score, located in a seperate cell.

A B C D
1 Stu. math read spell
2 Ed 77 82 71
3 Goe 33 45 87
4 Ty 88 80 72
5 Al 70 55 89
6
7 Target 70 65 75
8 Goe Goe, Ed,
9 Al Ty

In this sample in row 8 I need a list of students whos score was less than
the target for that subject, but I don't know what formula to use. I would
like to use 1 cell for each column using word wrap. I can do the
conditional formatting to show in the sheet who fits this criteria, but the
spreadsheets that I'm using are for an entire grade level with over 300
students, so a list would be easier. Thanks for any help.

One play ..

Put in G2: =IF(B2<B$7,ROW(),"")
Copy G2 across to I2, fill down to I5

Put in B8:

=IF(ISERROR(SMALL(G$2:G$5,ROWS($A$1:A1))),"",INDEX($A$2:$A$5,MATCH(SMALL(G$2
:G$5,ROWS($A$1:A1)),G$2:G$5,0)))

Copy B8 across to D8, fill down to D10
(i.e. by as many rows as there is student data)

The above should return what you're after
Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"nate" <notathome@hotmail.com> wrote in message
news:Yo_ze.8966$Tc6.8062@newssvr13.news.prodigy.com...
> In a cell at the bottom of a column I need to list students names from
> column A who's scores in another column are greater or less than a target
> score, located in a seperate cell.
>
> A B C D
> 1 Stu. math read spell
> 2 Ed 77 82 71
> 3 Goe 33 45 87
> 4 Ty 88 80 72
> 5 Al 70 55 89
> 6
> 7 Target 70 65 75
> 8 Goe Goe, Ed,
> 9 Al Ty
>
> In this sample in row 8 I need a list of students whos score was less than
> the target for that subject, but I don't know what formula to use. I
would
> like to use 1 cell for each column using word wrap. I can do the
> conditional formatting to show in the sheet who fits this criteria, but
the
> spreadsheets that I'm using are for an entire grade level with over 300
> students, so a list would be easier. Thanks for any help.
>
>
>
>
>

> Put in G2: =IF(B2<B$7,ROW(),"")
> Copy G2 across to I2, fill down to I5

Just some clarification:

G2 is copied across by as many cols as there are subject cols,
then filled down by as many rows as there are students

A slightly longer, but more robust formula
which could be used instead in G2 is:
=IF(OR(B2="",B$7=""),"",IF(B2<B$7,ROW(),""))
(G2 then filled across and down as before)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----