I am trying to find a function to calculate the percentage grade as the semester progresses. I'm attaching an example of my sheet what I need is column "D" to show the grade percentage from columns "E"-"AK" as each grade is entered.
View 3 RepliesI 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.?
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 RelatedI 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.
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.
I am looking for a function that would convert any mark into a grade. For example in cell B4 I want to enter the total score, cell c4 the score that a student gets and in cell d4 the grade. It is like a grade calculator. I do not want to limit cell b4 the total score cell with a specific total score number. It can be 100, 50, 30 or 10. The moment I enter any total score and the mark that a student actually scored, the grade is given. I attached an excel sheet.
Marks and Grades Calculator.xlsx‎
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.
View 9 Replies View RelatedI 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 Relatedi'm trying to use HLookup to find an adjusted midterm grade that's given. but i have some conditions:
If student missed exam and has a zero – keep zero.
If student has a grade of 1-119 points, increase their grade 40 points.
If student has a grade of 120-125 points, increase their grade 35 points.
If student has a grade of 126-131 points, increase their grade 31 points.
If student has a grade of 132-139 points, increase their grade 27 points.
with these conditions, if my midterms grade is 120, how would i calculate it using HLookup? i worked on it but i keep getting the #NA! error.
=H4+HLOOKUP(H4,B24:D25,2).
A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)
View 4 Replies View RelatedI have a range of cells, for this example I will use 2.
Cell E17 = 77/170
Cell E18 = 8/9
Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))
This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"
Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.
Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....
I have a huge worksheet that contains four character payer code in column D for example: 9081, M897, 0235. I am looking for a function that will show the result in column AD
IF payer code in column D starts with a 9 (9???) - GOVT
IF payer code in column D starts with a 7 (7???) or M (M???) - MNGD
IF payer code in column D starts with a 2 or 0 (zero) – COMM
IF payer code in column D starts with either Z, I, C - PTR
I tried IF function, but it didn’t work for me.
I am trying to write Excel VBA on Mac. Need to use the Dir function to get the file name in my document folder, but it showed only one file in the folder "DS_Store". There are actually several Excel files in the directory just don't show up by using this function. I have done some research on this "DS_Store" file, it seems a hidden/system file for Mac. It's fine, but it really doesn't make sense that other files won't show. Also, when writing VBA for Windows, *.xls refers to any file with a .xls extension. How to express the same thing for Mac? I am new with Mac, it just works so different than Windows.
View 9 Replies View RelatedPossible to use concatenate function to enter a formula to a cell and have the cell show the result?
I enter the following in a cell:
=CONCATENATE("=LEN(""",A2,"""",")") 'A2 contains the string "Exchange rate: 925"
what is shown in the cell is:
=LEN("Exchange rate: 925")
If I manually enter the above len function in the next cell, the cell displays 18.
Is it possible to use the concatenate function, maybe combine with other functions, to display 18 in a cell?
I have an excel spreadsheet that has an instruction page for the first worksheet. I have included screen shots for clarification. One of the screen shots is of the Find/Replace window. Sure enough, when people started looking at this sheet, they tried to click on the picture of that window to use it. I would like to make it so when they click on this picture they either a.) Open that command window or b they can start using it right there and not be able to move it so the next person will always be able to use it from the start.
View 3 Replies View RelatedNormally, when we use the =MAX() function, figures / numbers will appear. Is there any formula that can show the row the highest data is in? Let's just say that the figure is 3080 and it is under 'Transportation' row. Generally, when I apply =MAX() fx, 3080 will appear. Is it possible for it to show Transportation by using formula instead?
View 4 Replies View RelatedI am trying to make a function which will take a date as input and display the season for that date. i.e. the input 1-Jan-1992 will return "Summer" (southern hemisphere seasons)
View 10 Replies View RelatedI have a spreadsheet which connects to a SQL Server and updates a data set.
Is there a way to place a function or a formula I can use to show when the data set was last updated?
I imagine this is pretty simple but for the life of me I can't figure it out. Here's what I want to do...
Find MIN in Column F
After finding the MIN in F (let's pretend F222 is the current min)
Show the text in the field next to it (in this case display text from E222)
I know there is something I am missing here and I can't really find the solution I need in past threads. I have a macro that calls about three macros in a row, then once it has done those tasks, I want it to show a particular userform using the userform.show statement. However, when it comes to show the userform I get an error saying that it cannot find the specified object? This is quite frustrating and I think I fixed it in another part a while ago, but I can't remember how.
View 2 Replies View RelatedBasically, I have a worksheet that shows test results for 6 separate tests. If an objective is met in a test then it is marked with an "X". If an objective is partially met then it is marked with a "/" and if it is not met then it is marked with a "." . I have now created a combined column where I want to collate whether an objective has been met at all across the 6 tests.
For e.g. if in the range H14:M14 there is an "X" then N14 should show "X". If there is no "X" but there is a "/" then N14 should show "/" and it there are neither values in the range then it will show "." .
I tried
[Code] ....
but it just came back with a formula error.
The following are on a sheet:
A1 = 5700
B1 = 235
C1 = 17:14
D1 = 5922
$E$1 = 09:01
$F$1= 3
$E$1 and $F$1 are (the only) absolutes/constants.
A2 contains the following:
=IF(A1>D1,B1,IF(A1+B1<D1,"d",IF(AND(C1<$E$1,B1<$F$1),"",B1))))
This translates as:
=IF(5700>5922,235,IF(5700+235<5922,"d",IF(AND(7:14<09:01,235<3),"",235))))
The result is a variable/number (235), "d" or a blank cell ("").
This formula is in a column and works fine where there are numbers in corresponding cells. The problem arises with corresponding cells which appear blank (show no values) but contain references to other cells: they result in a "d" when nothing should be displayed.
So while a corresponding blank cell is correct if it shows no values, it gives me this problem - I don't want "d" or anything.
For example if I were to take the data in cells d3($358), d4($321), d5($130), d6($82) and skip a cell to now make the same data into cells d3, d5, d7, d9. Next, the data in cells f3, f4, f5, f6 would be merge into the blank cells of d4, d6, d8, d10.
The final result would look like column B. I have over two years of sales data in two separate columns that I need to merged into one column. Is there easier way without a simple cut and paste one cell at a time as this would take an enormous amount of time to complete? I’m not that good with VBA codes so a formula works good, but if VBA is the way to go then tell how to enter it on my worksheet.
Why than going line by line on a document that has 60k rows!
I have a document with NAMES in column A and ROLES in column I. If the user has more than one role..their name will be listed in "A" for each role in "I". I'm trying to purge down a file that has 60k + rows. Lets say i want to see all the people and all their roles if they are in "PROGRAMMER_ANALYST" role...can this be done by macros? or formula?
I am trying to do is create a button that when pressed, will bring up the Find screen (which can be brought up by pushing ctrl + f)...I tried to record it as a macro but nothing came up.
View 9 Replies View Relatedi'm using Sendkeys Edit Find to open the Find dialog box (works well)...i would like to restrict the searching to only 4 worksheets (of 14) of the workbook...?
(i see the option to select Sheet or Workbook)
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 RelatedFromToGr 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.
I have a workbook which scores tender's, the calculations behind the front sheet work fine, I would now like to show where the tender ranks against a set of criteria. I have attached a sample and typed into the cell D12 what I am trying to with this list of criteria below starting in cell C15.
View 3 Replies View RelatedI have two worksheets that Data might be present,
Holdtickets and "another sheet"
Here is my code:
Code:
Dim i As Long
Dim LastRow As Long
Dim wsEachSheet As Worksheet
Const strDestSheetName As String = "Hold Tickets"
Const strDestSheetName2 As String = "Enter Order"
[Code] ........
I don't want to show the location as being the worksheet "Hold Tickets" so i had to remove it from the search, But the data is still important
I need a macro code that will locate the data in "another sheet" and return the values from there, but if it is also present in the worksheet "Hold tickets" then
This part of the code will need to look like this:
Code:
Sheet1.Range("B7").Value = wsEachSheet.Name & "Hold Tickets"