Formula To Extract Test Scores
Apr 11, 2008
I teach history to four different groups of students that, in my spreasheet, I have labeled "1", "2", "3", and "4." The number of the group to which each student belongs is listed in CK2:CK100
Each quarter, I give all students a test and list the corresponding score next to each student's name. The scores are listed in CM2:CM100.
The name of each student appears in CL2:CL100.
I am looking for the formulas to extract the highest five scores in each group starting at CS2. So CS2 would contain the value of the highest score obtained by a student in group 1, CS3 the second highest score in that group and so on through group 4.
View 9 Replies
ADVERTISEMENT
Jan 9, 2008
I have got students mark list in Excel. I want the grading as under in the last column.
73% and above = A+
63% to 72% = A
53% to 62% = B
44% to 52% = C
35% to 43% = D
30% to 34% = E
<30% = F
Is it possible with VBA?
View 8 Replies
View Related
Oct 13, 2013
I was planning to use some combination of INDEX,MATCH and/or the SUMPRODUCT function to complete this task.
In the past I've used the SUMPRODUCT function creatively { ex. =SUMPRODUCT(--(repname=$C$2),--(question=C5),--(test=$E$3)) }
However, since the criteria I need to consider are not organized in individual records, the SUMPRODUCT function did not work.
See the attached work book for details on the test grade data.
Test Data Workbook.xlsx
The workbook also includes a mock-up of the summary and detail I'm trying to create.
My specific questions are on the "Questions" tab.
View 1 Replies
View Related
Jun 5, 2014
I have 3 test scores for each student. Currently, I have 3 students.
For example :
John 95 90 85
Cindy 50 60 100
Dan 87 86 90
I have a chart that that plots 3 lines, one for each student.
However, I want the chart to dynamically update if there's another student. In other words, I would like the chart to add another series while the legend updates to include another student.
I am trying to do this in Excel 2003.
View 2 Replies
View Related
Jan 10, 2012
This formula in AN63 TO AN75 is an array formula
=IF(AO63="","",INDEX($B$47:$B$58,LARGE(IF($AA$47:$AA$58"",IF($AA$47:$AA$58=AO63,ROW($AA$47:$AA$58)-ROW($AA$47)+1)),COUNTIF($AO$63:AO63,AO63))))
This in AO63 - AO75 is a standard formula
=IFERROR(LARGE($AA$47:$AA$58,ROWS($A$1:A1)),"")
Basically my aim is to extract the top scores and agent.
Now i wanted not have an array formula so to convert the array formula to a non array formula.
View 3 Replies
View Related
Jan 21, 2009
I have a table of data where i want to rank scores. I know the general formula for this would be =RANK(B2,$B$2:$B$7) as an example however the range of data is not next to each other and I have tried this formula =RANK(B2,B2,B4,B6) but this comes up with an error.
View 3 Replies
View Related
May 24, 2007
I have a grid set up to add scores of which there are none. The boxes each have a formula, ie: =IF(FIXTURES!D1="","",FIXTURES!D1 ) this produces a blank square until a score is registered. At the end of each column, down and across there is a formula for adding up the scores. Downwards is: = SUM(C2:C25), this works fine.
Across however is: =B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3+AF3+AH3+AJ3+AL3+AN3+AP3+AR3+AT3+AV3 This doesn't work, just gives the answer TRUE. It did work before the original formula was added.
View 4 Replies
View Related
Jul 31, 2007
what formula would be used to rank scores if you had the following
name score
participant 1 14
participant 2 27
Participant 3 19
to get this result
Participant 2 27
Participant 3 19
Participant 1 14
View 9 Replies
View Related
Oct 10, 2008
I'm basically creating a scoring worksheet, where there are a list of questions. Each question has a weighting as some are more impotant than others. Each question also has a range of answeres as some may not be clear yes/no answers. Also not all questions apply to all assessments so some may be left blank.
I basically need a formula which does the following to produce a total score:
[Total awarded score] / [Total available score of questions scored]
which equals
[(score <> "" / range)*weighting] / [if score <> "" sum weightings]
I've attached an example document where I think I'm getting close, just not sure if its possible.
View 4 Replies
View Related
Apr 20, 2007
I have a formula that looks up a chart showing scores referenced against each other as shown below. (Scaled down version)
******** ******************** ************************************************************************>Microsoft Excel - Table Template.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1B1C1D1E1=
ABCDE12006-2007Altofts JnrsCarleton JnrsEmpire ColtsHemsworth Terriers2Altofts Jnrs- 3Carleton Jnrs12-3-4-0 4Empire Colts - 5Hemsworth Terriers -Results
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The problem is when the score is i.e. 12-3 the formula is only looking at 2-3. Hence this actually then shows as a defeat. The formula causing this problen is an array and shown as
=SUM(IF(NOT(ISERROR(LEFT(resultsHome,FIND("-",resultsHome)-1))),
--(LEFT(Results!$B3:$K3,FIND("-",Results!$B3:$K3)-1)>MID(Results!$B3:$K3,FIND("-",Results!$B3:$K3)+1,9))))
Question. How can the formula be changed to reflect scores like 12-3 to be shown as a home win.............
.
View 9 Replies
View Related
Jul 17, 2008
I have a list of team members and their scores for team building events. I need to create a list of the top ten scores (including any ties). The list will have to show the top ten scores as well as any additional names whose score ties with one of the top ten scores.
I tried large and also a pivot table using the advanced but cannot get it to show the top 10 unique scores as well as any duplicate scores.
View 9 Replies
View Related
Aug 21, 2013
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
View 3 Replies
View Related
Aug 17, 2009
There are scores in B1:B10 (10 scores). I want to take the top 8 scores. So I used this formula which works just fine.
=SUM(LARGE(B1:B10,1),LARGE(B1:B10,2),LARGE(B1:B10,3),LARGE(B1:B10,4),LARGE(B1:B10,5),LARGE(B1:B10,6) ,LARGE(B1:B10,7),LARGE(B1:B10,8))
But I'm trying to make the formula a bit more dynamic. Is there anyway to have a set of data in B1:B50 (50 scores will probably be the most) and take the top x scores? X will be set in a separate cell (lets just say A1)?
View 5 Replies
View Related
Feb 13, 2008
I have a raw score for each case. I need to map those raw scores to scale scores. For example, a raw score of 0 to 0.49 maps to a scale score of 120, a raw score of 0.5 to 0.99 maps to a scaled score of 110, etc. There are a total of 13 possible scaled scores (120, 110, 100, ... 20, 10, 0). Is there a way to do this in Excel2003? (Note, if anyone on the list is 'bilingual' (Excel and SPSS), I'm looking for the Excel equivalent of the SPSS command RECODE raw (lo thur .49=120), (.5 thru .99=110), etc. INTO scale.
View 2 Replies
View Related
Jun 27, 2013
From a large worksheet I'm trying to find out if a particular value is used in that formula. E.g. formula: (In cell C1)=A1+B1*.75
I want to check if .75 is used in the formula (which is 75%) There are 1000's of record would it be possible to check the entire column C
View 6 Replies
View Related
May 11, 2007
I'm having an intelligence lapse and I need help with a formula
Simply need to return a "1" if value in Cell X is equal to or greater than -(minus)4% and a "0" if value is less than -(minus)4%.
View 9 Replies
View Related
Jan 25, 2007
I am creating a spreadsheet to monitor horse race betting but my formulas aren't coming out right.
The problem is to do with adding winnings to a running balance or minusing each bet's stake money from the running balance if the bet loses.
I have two formulas that are giving me a problem, both are in the P column in the attached spreadsheet. The first one, located in row 2, being slightly different as it is the first data row and so contains the starting balance in the formula.
=IF(OR(J2="W",J2="P",300+S2),IF(J2="L",300-O2,"")).
This produces a blank or error result.
=IF(OR(J3="W",J3="P",P2+S3),IF(J3="L",P2-O3,""))
This produces an error whatever is in the J column
View 9 Replies
View Related
Dec 28, 2008
I need to take a specific action when a cell has an actual formula in it versus when it just has a "value". Is there a procedure or command which will allow me to identify if a certain cell has a "formula" (like =sum(a1: a5) ) or just a value.
View 3 Replies
View Related
Sep 10, 2008
Is there formula to use if I want an IF statement to add a date "TODAY()" to a cell if a corresponding cell is highlighted? I would search for this in the archives if I had a clue what to search for. "Conditional formatting", maybe?
In Col J "Appt. Date", I want to add today's date if the cell in Col C of that row is green.
How would that work? My worksheet is posted below: ......
View 10 Replies
View Related
Feb 19, 2014
I'm trying to create a formula that will determine the bonus (%) for sales made. Here's the table:
<10000 sales = 0% bonus
>=10000 sales but <=29999 sales = 5% bonus
>=30000 sales but <=to 44999 = 7% bonus
>50000 sales = 10% bonus
Since the sales and bonus figures are subject to change each year I'd like the formula to point to the numbers but that part seems easy if I can get the formula right.
View 3 Replies
View Related
Nov 6, 2007
I have my test cases in below format and I would like to calculate # of test cases passed or failed using formula.
-------------------------------------------------
Test case #Step #Result
-------------------------------------------------
Test case 1Step 1pass
Test case 1Step 2pass
Test case 1Step 3fail
Test case 2Step 1pass
Test case 2Step 2pass
Test case 2Step 3pass
Test case 2Step 4pass
Test case 3Step 1fail
Test case 3Step 2fail
I need below result using formula:
# of test cases - Pass = 1
# of test cases - Fail = 2
View 9 Replies
View Related
Apr 1, 2006
I need a function that will use a column of text values and test these values
to see if one or more of the values exist in a single cell. If it does I need
the function to return true or false.
Ie. cell A1 contains the text "Jim Smith" the B column contains the test
names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".
Because Jim is in the cell A1 I would need the function in C1 to return the
value "true". If A1 contained the text "bob smith" then function in C1 would
return the value "false".
View 10 Replies
View Related
Feb 2, 2010
On Sheet1, Row 2 I have my columns named January-December, which correspond to the names of the other 12 sheets in my workbook. I want to test and sum the same range of cells in each sheet by simply coping and pasting the formula. I am using INDIRECT and SUMPRODUCT, but is there a better way? It seems to have caused the sheet to run slowly. Perhaps it's just the amount of data. Here is an example of the formulas I am using:
=SUMPRODUCT((INDIRECT("'"&B2&"'!$B$3:$B$200")=$A$2)*(INDIRECT("'"&B2&"'!$C$3:$C$200")="Yes")*(INDIRE CT("'"&B2&"'!$E$3:$E$200")))
This gives me the intended result. B2 in this case is January. C2 is February and so forth.
View 3 Replies
View Related
Mar 19, 2014
Using Excel 2013,
It trying to see if the Month() of a date is in a Array / Range
I tried =IF(MONTH(E8)={2,6,9,11},"Yes","No") where E8 = 9/30/12
View 2 Replies
View Related
Nov 13, 2008
I'm trying to use the following IF formula:
=IF(J2>$J$143 and J2<$J$144,"1","0")
But it doesn't work - I obviously have the formula wrong. The problem arises with the inequality. how to have two conditions within the "logic test".
View 4 Replies
View Related
Jul 7, 2009
Is it possible to make a top 5 scores section?
Ive been reading up on Arrays and how to use Arrays etc so i believe that it could be done with an array but i could be complete wrong.
what i have is players names in A4 and there averages in H4. i have 22 players listed but i want to take the top 5 scores from h4 and display them in a different section with there as so: NAME(A4): AVERAGE(h4) repeated 5 times and updates Automatically.
View 12 Replies
View Related
Jan 31, 2012
I have the following names in a column...
Chapman, Daniel S
Cooper, David
Reed, John Lee
I am trying to extract the first name with the following formula, however it is not working:
=MID(A1,FIND(",",A1)+1,FIND(" ",A1))
Instead I get the following...
Daniel S
#value!
John Lee
View 9 Replies
View Related
Jan 30, 2009
The first one contains the string "THOMAS J MALONEY 11/30/88M"
The data in the cell does not include the s (I just placed them in there so it would show the info. The string of data consists of 49 characters. I need a formula that would extract the name only so it would appear as THOMAS J MALONEY.
The second formula that I need help with is in regards to the string TAMPA FL33615 Once again the data in the cell does not include the s. This string of data consists of 33 characters and I would like a formula so that it appears at TAMPA, FL.
View 9 Replies
View Related
May 9, 2007
What is the best way to look up and return the 10 best performers out of a list of say, 40. I have columns representing people's names, their school's name, their age and their total score. The following formula is to add up the 4 highest scores for each school: SUM(LARGE(($A$2:$A$499=Y2)*($C$2:$C$499),{1,2,3,4})) (entered as an array) Now I want to display the 10 best performing schools (who will be determined by calculating their 4 best scores).
View 4 Replies
View Related
Apr 16, 2014
I have list of item numbers which some of them contain letter "C" or "S" at the end. For example,
8002
8002C
8002S
85555C
825S
Is there a way to select number only?
View 2 Replies
View Related
