Tracker
HOME    TRACKER    Excel

# Formula To Show Current Grade Of Student

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

Related Forum Messages:
LOOKUP With AND Requirements (lookup A Particular Student's Grade In A Particular Assignment)
I'm making my own gradebook (attached) and one of my sheets will list scores for each student in different assignments. I have one sheet which keeps track of all students and all assignments with other info. I would like to program cells in one sheet (the third in the attached file) to lookup a particular student's grade in a particular assignment. I figured trying a LOOKUP with an AND requirement might work but it keeps returning the message "could not find value".

My formula references the student's name and the assignment from the identifying cells so that it is easy to copy and paste. I wondered if it was this which resulted in the error, but doubt it.

Formula Show The Grade Average As The Year Progresses
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 Show Remaining Days Between Current And Given Date
how i can fix the formula below to show the number of remaining days between the given date and the current date.

Formula Is To Work Out The Class Of An Honours Degree For A Certain Student
=IF(H29=>70,"1st",IF(H29>=60AND>=69,"2:1",IF(H29>=50AND>=59,"2:2",IF(H29>=40AND<=49,"3rd"))))

The formula is to work out the class of an honours degree for a certain student.

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.

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 To Put The Grade And Rate Beside Each Name
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
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?

Making A Grade Book--- Missing Assignments Formula
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 That Would Assign A Letter Grade (in Row W) To The Average In Row V, Based On The School's Scale
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

Show Current Selection In Drop Down Menu
Hi guys i am in assistance of your brilliant brains, i have a working drop down menu which when selected i can select my data range such as below: ...

Show/Hide Toolbars For Current Workbook Only
I have a macro that starts when the workbook is open that hides many of the headers and toolbars. It is great and works fine unless you are working on another workbook and need to see those headers and tool bars. Is there a way to display and hide those items per workbook and not for the entire application. here is my failed attempt:

Dim wb As Workbook
Set wb = ThisWorkbook

wb.Application. ScreenUpdating = False

Dim wsSheet As Worksheet
Dim sSheetStart

Set sSheetStart = ActiveSheet

wb.Application.EnableEvents = False
For Each wsSheet In Worksheets
wsSheet.Activate

Update AutoFilter In VBA To Show Current Date
I have a similar issue, but it's with a date field. Every day, I go in and check the current day to show the current days data(on a Pivot table). Is there a way to modify this code so the AutoFilters update and "check" up to the current day?

Show Current Row Columns UserForm TextBox Controls
I have a spreadsheet that has a userform attached to it with text fields that enter additional data regarding each record. The userform takes these additional 5 fields and inserts them back into the spreadsheet, the final information to be exported back into Access. ( There are of course other ways to do this, but this is how my supervisor wants it done!)

My problem is that now that I can scroll through the spreadsheet without closing the userform, the userform doesnt show the current row's information as I scroll or change rows on the spreadsheet. It will only show the information that was in the row that it was on before i switched back to spreadsheet.

Show Current Date In The Cell,but Tomorrow- No Change
I have a little problem (but for me is like a break); I want, when I change some in a cell, in the other cell, show me current date. I try with "today()", but , next day when I open that workbook, in that cell, show me the date coresponding with the next day (normaly).

How ,I can rezolve this? I want to blocked that cell, only view, but no acces for change enithing.

Show Worksheets Tabs On Current Worksheet For Easy Access
You could use:

If ActiveWorkbook.Sheets.Count < 17 Then
Application.CommandBars("Workbook tabs").ShowPopup
Else
Application.CommandBars("Workbook tabs").Controls("more Sheets...").Execute
End If

The code doesn't do anything on my excel workbook which currently has like 20 workbooks, and it grows daily by like 4 or 5 worksheets.

Double Click In Cell To Show Current Date And Time
I would like to be able to track progress of various tasks/occurences by allowing users of a spreadsheet to just click in the appropriate column and in doing so, it would fill in the current time and date. I.E. Column headings would read, "Item Sent to Review", "Item Received From Review", "Item Sent to Specialist", etc. and I want users to be able to double click in a given column to populate with current date/time information,

Show Current Date Or Yersterdays Based On Time In Cell
I am trying to write a formula that will have the cell display todays date if it is after 2:00PM, and if it is before 2:00PM I need the cell to display Today()-1. I have tried several iterations and I'm stumped.

Show Only The Date That Corresponds With The Current Date?
How do I get cell B1 to show only the date that corresponds with the current date?

ex.

A1
15-Nov
26-Nov
29-Nov
2-Dec
4-Dec
4-Dec
5-Dec
6-Dec

If I enter 12/2, in one of the rows in column A it will display today dates in cell B1 since today is 12/2, but when tomorrows comes, since there is not a 12/3 date in column A, B1 cell will be empty, but when 12/4 comes, cell B1 will show 12/4 since that is a 12/4 date

Current Row In Formula
Here is the formula in question: =((C5/28)*(C12)+(D5/28)*(D12)+(E5/28)*(E12)+(F5/28)*(F12)+(G5/28)*(G12)+(H5/28)*(H12)+(I5/28)*(I12)+(J5/28)*(J12)+(K5/28)*(K12)+(L5/28)*(L12)+(M5/28)*(M12))+N12+O12

What I want to do is make all of the "12" entries equal to the current row value. In other words "C12" should be "Cx" where x is equal to the current row.

Current Count Formula
I need some guidance on creating a formula that will count a total based on 96 cells with numbers.

Cells E24:G56 are blank cells at the moment until data is added.

Cells E57:G152 are previous scores

What I’m trying to achieve is the latest 96 scores, even if I add data into the blank Cell E24:G24

Add A 3rd Lookup To A Formula With 2 Current Matches
a while back one of you gave me an awesome formula that pulls data from sheet 2 based on what was typed in A1 and A2 on sheet 1. Well I ran into a snag, is there a way we can add a 3rd match in the formula. See the attached file. I need to pull data from sheet 2 based on what is typed on sheet 1 in cell A1, A2 and A3.

Exp
Sheet 1, report to be printed
A1 = last name
A2 = first name
A3 = month

Sheet 2, data is held here
Need to pull data in column “M”

Enter Current Workbook Name In Formula
I have the formula:

I may however change the name of Book1, therefore rendering the hyperlink formula useless. Is there a way to enter something in place of Book1 that will return the current name of the workbook?

If there isn't, I will have to create a VBA program to update the hyperlinks, and will therefore have additional questions about that shortly.

Formula To Open Current Folder One Level Up
I have this formula that opens the current folder where my file is stored:

This works fine. I'd like it to open the folder one level up from the current folder, but I really don't understand formulas well enough to know if this is even possible.

Formula To Recognize Month And Current Year
finding a code that would automatically insert various phrases into a column depending on whether or not the person in that row was due to renew their membership in our organization. The code I received works fantastically, but I've come across one snafu:

Formula: Current Month Based On System Clock
Is There a formula for current Month based on system clock....

Formula: Calculate The Weeks Cover Of Current Inventory
Calculate the weeks cover of current inventory based on a sales forecast. the forecast is in weekly buckets so I was thinking using some form of loop statement within the function which basically starts with the inventory figure then subtracts the forecast week by week until the inventory figure is less than the weekly forecast. I should then be able to divide the inventory by forecast and add back the number of loops to give me the weeks stock figure.

I have done this using if statements but it will only allow me to use 10 statements in the function and I need to do this for a full years worth of forecast.

Does anyone have a suggestion as This problem is becoming quite frustrating.

Here's an example of how the spreadsheet looks

Forecast103050205040Inventory 20019016011090400Weeks Cover6.05.04.03.02.01.0

Automatic Current Date Formula Extracts From Specific Cell
formula that can automatically use the current month date to extract information from other cells on my spreadsheet.

For example: This formula should be located in cell C6. Cells D6, F6, H6, J6, L6, N6, P6, R6, T6, V6, X6, and Z6 (these cells correspond to the months of the year, January to December respectively) contain the values I need. The month names are labeled above in row five.

Let's say the current month is March, I need the formula in C6 to automatically know that it is the month of March and to pull the information from cell H6 (which is the March cell). Then when April comes along, it will know that it is April and to only pull the information from cell J6; and so on until the end of the year.

About conditional Formatting and relative referencing.

I need to change the colours of the cells depending if the pupil is meeting, over or under acheiving thier target grades.

I've got as far as highlighting the grades and using the formula in conditional formatting =ISTEXT(C7:C14) to display if the pupil is meeting their target grade.

I am aware that i will need to use relative referencing but am unsure how.

Its quite mind boggling to find out a way to see if they are over aceiving/under acheiving.

Getting An Average Of Letters. Grade Scores
I'm doing a course at the mo and get graded monthly. I have a spreadsheet with my grades on 'assignment1, grade A, assignment2, grade B and so on.

What i want to do is have a cell that has the aveage of all grades. So if i had...

the average is B.

Count Function :: For Students And Grade
I have a problem with counting the number of students assigned to each professor and determining the number of students who have passing grade. Let say that the passing grade is 80.

Percentiles Against Each Concatenated Grade And Region
I have a dataset (20,000rows) with Grade, Region and Salary. I need to calculate the 25, 50, 70 and 90 percentiles against each concatenated Grade and Region.

creating a function to turn a percent (0-100) into a letter grade, I am creating a gradebook spreadsheet.

I think VLOOKUP is the way to go here, but I do not understand vlookup at all no matter how many times I try to look at demos or read how-tos.

I created two columns, one with what the grade entails (93-100 , 90-93 and so on) and the next column has A, A-, etc. But I can't get vlookup to work, any suggestions? I am pretty noob when it comes to excel.

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?

Assign Consistent Letter Value To Grade Percentage
Newbie here. I have a very frustrating problem. I am using excel for my gradebook at school. I've tried several different ways to assign a letter grade to an number average. It works fine each different way I do it, VLOOKUP, IF, etc. But, I have about 5 grades out of 100 which give the wrong letter grade. For instance, my scale says that a grade of 85 should be lowest limit of a "B", but I get a "C" returned in the cell instead. Like I said, it only happens on a few grades. The biggest majority work fine. I can't figure out why. Any ideas? Attached is one of the "problems" with a student's grades. Note the Final Avg with a grade of "C", it should be "B".

Converting A Numeric Cell To A Letter Grade
I, too, am trying to use excel to the fullest. My first issue is, I would like to convert the numberic grade in one cell to a letter grade in another cell. I am not really good with all the vocabulary, but would love to learn it, and can copy a formula pretty well!!!! Not so good with functions and macros but would love to learn. All help appreciated.

I would like to link students for the 1st Nine Week list to the correct grade level as shown. I have attached a file.

IF THEN Statements: Assign A Letter Grade To A Number
I have figured out how to assign a letter grade to a number, but am having trouble assigning it the other way, a number to a letter grade. For instance: If a student gets an A, I want the column next to it to indicate that the A represents a 4; a B represents a 3; a C represents a 2; D a 1; and F a 0. This will allow an easy grade point average calculation.

A 4 History
C 2 Math
A 4 English
B 3 Physical Ed
D 1 Science

GPA 2.80

Convert The Grade To A Number But VLOOKUP Not Getting The Difference Between A And A*
I'm putting together a mark book for school and have a problem with VLOOKUP distinguishing between what used to be a top grade, ie A, and the new top grade, A*.
I want to convert the grade to a number but VLOOKUP can't tell the difference between A and A*. I've attached a sample worksheet.

Getting A Formula To Show Zero Until It Is Used
I have used excel to create a set of timesheeets and a total page, on the total page there is a column that will show you any varience on your hours each month (I.e if you have worked overtime or under your hours)

I have used the formula =(worked+hours+sick)-(weekly hours*week in month)

This works fine the only problem is in shows a minus number until the hours for that month have been entered so I cant create a total column, is they any way to get these numbers to show 0 until the times are entered?

Formula To Show A Change In A Row
I'm working through a huge file and trying to compact it down and make it more workable. I've already managed to get it down to what i need but i now need to add a few things so others can work it more easily also.

One of the ideas i had was the following, but for the life of me i dont know where to start.

The spreadsheet will be updated on a daily basis and at the end of each week we will need to submit any changes we've made to the marketing team.
However the change tracker thats built into excel isn't really what i want.

What i though was i should set up an IF formula that would return a True value if any data in that row was changed.
Then i could set up a macro to copy those rows only to a new sheet to be sent to the marketing team, this way the structure of the spreadsheet would be kept to make it easier for them.

Formula To Show Users Name
Is there are a formula that will result in showing the users name in a selected cell?

Formula To Show The Numbers When They Are Below 30
I need a formula to show the numbers as they are when they are below 30, but when they go above 30 I need it to display 30 every time. I have attched a file.

Show The Formula In Cell
I'm trying this code but its not showing the formula in cell Z1, what am i doing wrong here ??

If & And Formula: Show The Difference Between Col F And Col G
if the cells in column E AND column F are blank, then column G will be blank also (I dont want 0 showing), if not show me the difference between col F and col G.

I put in the following:-

=IF((E2="")AND(F2=""),"",E2-F2)

However, Excel informs me this is wrong and corrected it as

=IF((E2="")*AND(F2=""),"",E2-F2)

I guess my question is why do I need to put in "*" for this to work?

Show Copied Formula
I have a problem in copy formulas form one cell to another, my formulas are writen in one workbook in this way... '=Dog+Cat.

The problem is that I did a looking table code (vlookup) and when it shows the formula I have #żName?.

I would like to knw if there is a way to fix this using the vlookup or I'll be obligated to use special Copypaste

Show Formula As Text
show a formula as text in another cell

Only Show Formula Result If Not Zero
I'm having trouble with the attached example. An earlier member solved the odd-even problem, but I'd only like it to calculate if there is data in columns B to G.

Left & Right Formula To Show Number Not/as Well
I have numbers in a cell such as 1/25, 4/31, 12/35 etc I have the current formula to extract the left or right number but when there is only a single digit it also includes the / is there a way of eliminating this.

The numbers vary from 1 to 2 digits.

My current formula is =IF(C15<18,LEFT(E8,2))+IF(C15>18,RIGHT(E8,2))

I only want it to show the number not the forward slash

Show Below That Formula Cell In Two Rows
in my document column "L" have some formulas,it's like this.
=150*1.5+25*1.5
i need to make that formula to two part.it's like this.
=150*1.5
=25*1.5
that two part need show below that formula cell in two rows.