Formula That Reads Grade And Then Ranks These By 1st-5th Place According To Score
Feb 10, 2014
I'm creating a Speech and Debate calculater that presents winners names by place according to their score. If you look at Tab HSSE Results it will read the inputed information from HS Speech English. I want the names of the students to appear in the correct ranking order in the respected grade rankings chart. I currently have a countif formula which reads the grade, but I think I need to link that with an index formula which I'm having problems with.
View 8 Replies
ADVERTISEMENT
Aug 10, 2009
I want to see if i can make a formula that works out how a player got a score based on a scoring system. See the attached file for more details. I want this to show that the player scored or didn't score, played a full match or was a substitute, was booked or wasn't booked and was sent off or wasn't sent off.
View 5 Replies
View Related
Jul 10, 2013
I am compiling a master spreadsheet to analyse progress of students. I have their potential grades in one column and their latest progress grade in another.
I would like an automatic look up of the potential to the progress grade so that if their current grade is BELOW their potential that gets highlighted one colour, if it is above, another colour.
One issue is that the grades are all letters (S, A, B, C, etc.) and that some potential grades are 'dual' (i.e. A/B, S/A) which complicates the issue somewhat.
View 7 Replies
View Related
Mar 16, 2007
On cell K7, i have this formula:
=IF(ISERROR(I7-J7),"",I7-J7)
On cell L7 i have this formula:
=IF(ISERROR(RANK(K7,$K$7:$K$29,0)),"",(RANK(K7,$K$7:$K$29,0)))
The issue is that in cell K7 a dash (-) shows up and ranks this as the number one, even when i have no data in I7 and J7.
MTD Conv
Conv Goal
B/W Goal
RK
-
1
29.3
31
(1.70)
12
21.8
25.0
(3.20)
15
View 9 Replies
View Related
Jun 13, 2006
my Excel teacher doesn't even know how to do this! The chapter is Working with Multiple Worksheets and Workbooks, and we went into linking workbooks, linking worksheets, using the VLOOKUP function, and saving workbooks as templates. This is Case Problem 2 and i'll attaching the file.
It says:
1. On the Grading Criteria worksheet, enter the text "Exam Average" in cell A1
and the text "Grade" in cell B1.
2. In the range A2:B6, create a lookup table for the range of grades specified by Professor Templeton. (Hint: Each letter grade should be matched up with the lowest score possible for that grade.)
Here are the specified grades:
0 to 49 = F
50 to 59 = D
60 to 74 = C
75 to 89 = B
90 to 100 = A
3. Insert a formula in Column E of each worksheet to calculate the overall score of each student based on the grade table you entered in the Grading Criteria worksheet.
View 9 Replies
View Related
Aug 18, 2014
The situation requires some context. I have a spreadsheet that is pulling data in from another location. It is simply a 'Location!A1' link - the source data updates daily as it is a Business Objects report that I dump out and I want to then bring that info into another spreadsheet.
My challenge is attempting to order the data as it comes in without having to manually sort the Business Objects report everyday. Because the link to the BO report is simply a cell look up, I have to take the data as it comes.
I've tried the rank formula according to a price (as this is what it needs to be ranked by) but because some products are the same price as each other, it simply doubles up (so if there's two products at $10, it ranks them both the same e.g. 4). Once I get the right rank, I can then vlookup the right order in a way I can then reference properly. EXAMPLE 180814.xlsx
View 5 Replies
View Related
Sep 17, 2009
i have a formula that reads off a single cell at the moment.
=IF(N38="MB","KL","MB") this works fine. I need the this to read off a range of cells from N38 to N43.
View 9 Replies
View Related
Mar 30, 2008
I have a range of cells C11:C29,G12:G18,G20:G23,G25:G26,G28:G29,C33:C42,G33:G42,C46:C47,G46:G47,C51:C54,G51:G59,C58:C59 that contains letter grades (egs. A+,C- etc). I need the code for a macro that checks each cell from this range and if the value of the cell is A+ then the cell's value changes to 100, if the cell's value is A then change the cell's value to 98, if cell's value is B+ then change to 88 ...and so on...you get the idea (i will complete the rest of conditions but i just need the basic code).
View 6 Replies
View Related
Feb 20, 2013
Here in "Grade" Column wants put a formula so that it will show First,Second,third and Fail. If the Mark is more than or equal 40 but less than 50 will reflect Third,if the mark is more than or equals to 50 and less than 60 will reflect Second, if the mark is more than or equals to 60 will reflect First and remaining less than 40 will reflect fail.
View 2 Replies
View Related
May 12, 2007
FromToGr Rate
90100A14.0
8689A23.6
8085B13.2
7279B23.0
6471B32.8
5663C12.6
This is part of a marks to grade and rating table. I have a list of students and the marks they obtained in an exam. I need a formula to put the grade and rate beside each name e.g. John; 92 marks; A1; 4.
View 9 Replies
View Related
Apr 6, 2007
I would like to prepare mid-term grades for my history class, and have three categories, which I would like to insert into a spreadsheet to calculate the grades, as follows:
Mid-term 1: 30%
Mid-term 2: 40%
essays: 30%
each of the three grades is a number from 1-100; the final answer will be a number which I can then convert to a letter grade.
I'm just not sure what function to use, in that last column, to get Excel to calculate the final answer/grade.
Let's just assume that Mid-term 1 is in column A; Mid-term 2 is in column B; and the essay grade is in column C. How can I create a function that will allow me to give the above percentages to the respective assignments, when calculating a grade?
View 9 Replies
View Related
Jan 14, 2007
I would like to have a student's current overall grade be automatically calculated and displayed as I enter the test scores and extra credit points over the semester, first in percent and then in letter grade.
I have it set up so that the percent is calculated based on the total number of points likely to be obtainable at the end of the semester. But if they have only taken two tests and one extra credit assignment, it takes the total of those and divides it by the semester's total possible, instead of just the two tests and extra credit total possible. (ps: I do not know how many extra credit columns or number of possible points I will have this early in the semester)
Also, how then do I set up a formula to translate that percentage into a letter grade based on A=90-100, B=80-89, etc.?
View 7 Replies
View Related
Nov 14, 2008
I have a decent working knowledge of excel, but I'm stuck... I teach and I have created an excel spread sheet to help me keep track of grades, attendance, etc.
I'm trying to figure out how to get excel to figure out the assignments a student has missing (cells with no data input), then take the assignment name (column title with missing data) and students name (first column of data) and put that information in another worksheet. Basically I would like excel to tell me what students are missing work and what assignments they are missing so I don't have to scroll through hundreds of lines of data whenever I have midterms or a student wants a list of "missing assignments". BTW---I can get it to list the number of assignments a student has missing, but not list the specific assignment titles.
I know theoretically this is possible, but I'm having trouble figuring out how to do it. Any help would be appreciated as it will save a lot of time and help out my students! If you can give me a "fake" formula, I should be able to update it for my purposes...
View 10 Replies
View Related
Jul 15, 2006
I have a very basic Excel Gradesheet that's designed to simply record letter grades, and show the grade average as the year progresses. I've hit a snag with problem in the formula which shows a grade of "F" - when the grade range can only be that shown on the table (A+ to E-). I've attached the Excel sheet so you can see what I mean.
View 8 Replies
View Related
Jul 10, 2014
When speaking with parents I want them to enter in their child's birthday i.e. 12/1/1998 in one cell and then automatically generate what grade they should be in the next cell. If the grade year cutoff date is Sept 1.
View 2 Replies
View Related
Oct 3, 2006
Need to write a formula that would assign a letter grade (in row W) to the average in row V, based on the school's scale. So, row V contains my averages (in % form) and I want row W to have the letter equivalents.
93+=A
85-92=B
75-84=C
67-74=D
<66=F
View 2 Replies
View Related
May 31, 2012
I currently have a sheet of data with names and scores (1-10) on e.g.
NameScoreJohn10Smith5Steve4John9
and what i am wanting is a formula that will read these columns and tell me the amount of 1's, 2's, 3's, 4's, 5's, 6's, 7's, 8's, 9's and 10's the corosponding name has gotten.
for example:
JohnSmithSteve123415167891101
I have been playing around with COUNTIF formulas but cannot seem to work this out.
View 5 Replies
View Related
Oct 29, 2008
I'm helping out a community that puts out a role playing game by making a character sheet for their gaming system. It's normally a pen-and-paper type affair, but a few people have reached out to me and asked me to make it. Now, one thing that I really thought would be nifty would be to make it persistent. I know of the Excel basics, creating basic formulas and the such, but this is where I hit a brick wall.
For the character sheet, they normally have basic attributes on a 1-20 scale. These attributes are accomanied by a bonus. The only thing is those, while these bonuses go across a linear path (For attribute 1, it's -30, for 2 its -20, for 3 its -10, for 4 its -5 for 5 its 0....), I cant seem to figure out how to create some kind of If/Then formula for it....
Heres my example:
Brad makes a character that has a score of 9 for one of his attributes. The bonus given to someone with a 9 is a +10. What I'm looking to do is create a table that sees that 'If (c9)=9, Then (d9)=+10'...
And my second related issue...
Is there any possible way to do the above mentioned formula in relation to a drop down selection bar? It's the same thing here; To break it down simply, It would be excellent if there was a way for it to say If (a10)=Warrior, then (c10)= +10.
View 3 Replies
View Related
Apr 13, 2009
I have a paper-based scoring sheet that we use to determine a rating category. I cannot figure out how to convert this scoring sheet into a lookup region and use some type of LOOKUP formula to determine the rating. I have looked at the various Grading examples on the Internet that show you how to look up a grade, based on a score, but they only look up a grade, based on a single input (the score).
Here I have two values going in--the person's age and their score, and I need to look up the rating, which is the column heading.
Can anyone point me in the right direction?
I've attached a sample spreadsheet which shows the original scoring sheet.
View 7 Replies
View Related
May 25, 2008
I am using Excel 2002 in Xp and also want to use the spreadsheet on my Pocket PC using Pocket Excel.
I have a column of 10 numbers with rows 1,3,5,7 & 9 representing the individual gross scores of the 5 team members. Rows 2,4,6,8 & 10 have either a 0 or -1 (calculated) in them representing the strokes (or discount) the player receives for that hole. So the net scores for each player will be row 1 + row 2 and so on.
I need a formula that will calculate the total of the 2 lowest net scores and 1 low gross score with the score for each player only being used once which will then represent the team score for that hole.
A sample column would have 4,0,3,0,4,0,3,0,5,-1 in it.
View 9 Replies
View Related
Jul 24, 2014
I want to create a system based on the value of a cell appearing as a grade in the adjacent cell. The cell could contain any value from -10 to +10.
the criteria would be
value greater than +2 = 3
value between 0 and +2 = 2
value between -2 and zero =1
value below -2 =0
I can get a formula to work for a single instance but not multiple conditions.
View 3 Replies
View Related
Nov 19, 2009
I have a table of data where each row is a person, and each column is a group that person might belong to. In the intersecting cell of a person row and group column is text showing what position they hold in that group (eg Director, Head, member etc). In many cases the cell is empty because the person is not involved with the group.
There are then two lookup tables which have scores; one for the group name and one for the position type. The group name is always the column header. I have used these lookup tables as I would like the scores for each group or position to be easily adjustable.
Probably best that I just attach an example. I tried to solve this using SUMPRODUCT. VLOOKUP on the two lookup tables would have been good but it seems it doesn't work with arrays the way I expected. My attempt (which doesn't work obviously!) is given.
View 6 Replies
View Related
Mar 25, 2003
how can i make a worksheet/formula that automatically look for the equivalent score in a given transmutation table.
example: i have an exam of 10 items, what will be my formula to so that it will automatically look for the equivalent score under the transmutation table of 10 items
View 9 Replies
View Related
Feb 22, 2007
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
View 2 Replies
View Related
Feb 7, 2014
I have a problem ranking a large dataset(more than 30000 rows, 16 different columns need to be ranked). My problem is that I dont want the ranks to have gaps when there are ties.
See how it should be in table below.
Ext P$
Rank
Should be
2,128.34
1
1
[Code]...
I do have a working solution with an array formula similar to this, but it slows down my macro (30 minutes instead of 10 seconds) as I need it to calculate 16 times
Code:
=SUM(1/COUNTIF(A$2:A$35000;A$2:A$35000)*(A$2:A$35000>A2))+1
I was thinking of using a for next loop to rank sorted columns but I dont know how to set it up properly.
View 3 Replies
View Related
May 10, 2007
I have a list of data where some of the cell values are the same. I want to rank them uniquely but when I use the RANK function the cell values that are the same are given the equivalent rank. Is there any way of assigning the equivalent values a sequential ranking that is unique?
So in this example rather than producing the rank as this:
100 1
99 2
98 3
98 3
98 3
97 4
96 5
95 6
It would produce it as this:
100 1
99 2
98 3
98 4
98 5
97 6
96 7
95 8
View 9 Replies
View Related
Oct 22, 2008
i am using this formula with military ranks as the primary value to look up. i know i am doing it correctly because it works with looking up values in another excel workbook when the rank is CPT, PFC, PV2, SPC, SGT, SSG, etc... but it does not work for any rank that is abbreviated with a number as the first character, such as 1LT, 2LT, or 1SG. instead i get a #N/A, which is false. i have looked all over the internet to include this website but cannot find an answer.
View 9 Replies
View Related
Apr 12, 2009
Spreadsheet 1: So in column "A" I have all of the dates written out. Then i need column "B" to put the appropriate name next to the date that person chose in spreadsheet 1.
Spreadsheet 2: I have a live form that feeds data into a different spreadsheet.
I am using google spreadsheets.... The formulas are the same as in excel
View 14 Replies
View Related
Apr 11, 2013
How to write a sub that read all cells in the matrix ( a block of cells let's say: A5:F15).in two dimensionals. This is what I've done so far, but i got nothing:
Sub myarray()
Dim myarray() As Variant
myarray = Range("A5:f15").Value
End Sub
View 2 Replies
View Related
Mar 12, 2007
I have a rowfilter on the location (so i can view who took the test by location nad whether or not they passed) - the only problem is that when it selects by location - it chooses the correct name, personellnum, location row but it then picks the test values ABOVE that row - not below it - so It is choosing the complete wrong values to go with the associated person -
View 14 Replies
View Related
