Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 <atyahoo<dotcom ---- "nate" wrote in message m... 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. |
#3
|
|||
|
|||
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 <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |