# 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.

## Formula To Work Out Score Based On Score System

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.

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.

## Formula - (-) Shows Up And Ranks This As The Number One

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

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.)

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.

## Formula That Ranks Dynamic Unsorted Data

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

## Formula That Reads Off A Single Cell At The Moment

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.

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).

## Formula For Segregation Of Various Grade?

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.

## Formula To Put The Grade And Rate Beside Each Name

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.

## Entering A Formula To Determine A Grade

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?

## Formula To Show Current Grade Of Student

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.?

## Making A Grade Book--- Missing Assignments Formula

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...

## Formula Show The Grade Average As The Year Progresses

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.

## Formula To Calculate School Grade Of Child Based On Their Birthday?

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.

## Formula That Would Assign A Letter Grade (in Row W) To The Average In Row V, Based On The School's Scale

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

## Formula That Will Work Out Score Of Individuals

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.

## If / Then Formula: Makes A Character That Has A Score Of 9 For One Of His Attributes?

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.

## Converting Score Sheet To LOOKUP Formula?

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.

## Formula For 2 Low Net & 1 Low Gross Score In A 5 Man Golf Team

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.

## Formula / Conditional Format To Award Score From 0 To 4 Depending On Cell Value

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.

## Array Formula To Lookup Two Values And Produce A Total Score

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.

## Worksheet/formula That Automatically Look For The Equivalent Score In A Given Transmutation Table

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

## Macro To Copy And Place Data To Specific Place

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.

## Ranking With VBA - No Gaps Between Ranks

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.

## Unique Ranks Function

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

## VLOOKUP With Military Ranks

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.

## Formula To Place Name Next To Appropriate Date

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

## Writing Sub That Reads All Cells In Matrix?

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

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 -