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

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.

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.

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?

## Random Assignments

Oct 30, 2009

I have a worksheet that has weekly meeting dates in column A and in columns B through M different responsibilities/task. Each week, I have to schedule 1 person to perform each task. A person can only perform 1 task each week. I have a list of 40 people that are eligible to do this.

Currently I am assigning each person to a task while trying to evenly assign people and space out the timing as to how often they're assigned. Of course, being human has led my results to both favor and overlook some people. Is there a way to randomly assign any 12 people from my list into each week?

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

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

## Remove External Path From Macro Assignments?

Jul 15, 2014

I need to remove the external paths in my macro assignments. I only use shapes. (no active x/form controls)

I tried that, but it won't work for me: [URL]

example of an assignment: 'G:10_excel oolshowPPIupdate.xlam'!button_last12 => should be button_last12 after i run the desired macro

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

## Move All Worksheets From Book B Behind All Sheets In Book A?

Jul 18, 2014

I made the following code to merge 2 workbooks together.

The code is to be executed when the user has Workbook A opened. (All sheets in workbook KPISWD are supposed to be moved after all worksheets in workbook KPICustomers).

I keep getting a debug error on the code that is supposed to do the actual move and loop until it is finished with all of the sheets in Workbook B.

Code:
Dim KPICustomers, KPISWD As String
KPICustomers = ActiveWorkbook.Name

Workbooks.Open Filename:= _
"W:FacturatieKPI per periode SWD.xls"

KPISWD = ActiveWorkbook.Name

[Code] ..........

## Copy Data From Book To Book Error

Jun 22, 2007

I have the below code, which now looks to see if a file is open or not, if it is, then copy and past 'Data' and if not open the book and copy 'Data'.

I think the code is sort of right, but im missing something, as i keep getting runtime error when i try and copy. Here is the
Sub PrintSaveKPIUpdate()

Dim sFilName As String
sFilName = "C: estCashSales_KPI.xls"
Set Main = ThisWorkbook

If IsOpen(sFilName) Then

' Book is Open.

Worksheets("Setup Data"). Range("Data").Activate

## Call Macro In Book Two From Book One

Aug 29, 2007

i have done a search on this topic and found many similar answers to many similar questions. All specify using Application.Run "workbookname.xls!macroname".

In my code, the filename has an assigned value as the master code goes out to many secondary files - the user selects the particular one they want at the start of the macro. The variable assigned is called "UpdateKAMsFile".

how do i get the macro called KAMsRandomColour to run in the workbook designated by UpdateKAMsFile?

## Data Lookup From Offline Book With A Formula

Feb 24, 2010

where do i put the indirect.ext to below formula so that that Test.exe file be checked offline .. this below works only if the test.exe is open.

=IF(A2="";"";COUNTIF('C:Folder[Test 24.02.10.xls]Table1'!A\$1:A\$65536;A2))

Since the Test file is generated every day..

how can i make the Test file shown every day with different date.. this 24.02.10 is not permanent.. everyday changes.. meaning tomorrow it checks Test 25.02.10.exe, Friday it checks 26.02.10 etc.

I could make a cell for date input .. and from that cell it could be added up to enter into above formula automatically..

## Macro To Highlight Data Inside Other Data Or Move From Book To Book

Jul 16, 2014

so to start off im not able to attach things due to security reasons, what i need is either 1 of 2 macros. if its possible, both are related. one possible is as follows: i need it to go through a certain column (say I for example) and look at the information in it, this information can vary from peoples names along with dates and other stuff, i want it to look through these and if anything has a date today and prior i need the cell to be highlighted. problem is sometimes it might have 2 dates, or no dates. it should have todays date, their name, other information, and future date of something. but not everyone does, this is the macro i dont think that can exists.

2nd macro possibility the other macro uses the first sheet, AFTER been highlighted, normally by hand, and takes it to another workbook and puts in in certain spots. so the first sheet has names of everyone in column K. what i need is it to look at column I and if its highlighted take entire row to other book, and put into sheet under the person name in their tab. the second book has a tab for each person (at this time 18 tabs) which can fluxuate, and each tab is the persons first and last name, without spaces. since when i put sums on main page it didnt want to work with the spaces i had to omit them. again im not sure this is possible.

## Formula On Making A Statement

Jan 20, 2014

I need a formula to look at a cell and if it is =< 102 say "Flat Bed", or =120 "Double Drop"

## Making A Formula Static

Jul 13, 2008

I have 2 sheets in a work book. In the first sheet in A27 I have a value. In the secend sheet I have a formula "=SHEET1!\$A\$27".

Problem is when I insert a new row in sheet 1 the formula in sheet 2 changes to "=SHEET1\$A\$28" and I want the formula in sheet 2 to stay the same that is, always =SHEET1!\$A\$27.

## Making An Exception Formula

May 17, 2006

I am trying to get 2 different formulas to run depending on the value of the 2 cells.
Example: if cell "A" is equal to cell "B" then one formula, if it is different than a second formula.

I hope I did this right, if not please let me know and I will ask the question in a different manner.

## Compare Two Columns, If Missing Insert Missing Data

Jul 8, 2008

I have two columns which i want to compare, they contain text data such as A123.

what I'd like is if its in column A and not in Column B then add to bottom of column A.

Once its in column A i can do the vlookup's to draw the other data, costs etc, over but don't know how to identify, and add, the missing codes to the list.

## Making HLookup Formula Dynamic?

Jun 20, 2014

I'm currently using an HLookup formula to create an output tab for a huge set of input data, but whenever I add new rows to the input tab, I have to adjust the formulas for each individual output cell.

For example, my output formula is currently "=HLOOKUP(B\$4,'Inputs'!\$B\$2:\$BJ\$384,116,FALSE)," but if I add a new row to the input data, I have to adjust the table array and the row number for the formula. So, if I added a row before the 116th row, but wanted the output to be the same for this cell, I would have to update the formula to "=HLOOKUP(B\$4,'Inputs'!\$B\$2:\$BJ\$385,117,FALSE).

Is there any way to adjust this formula so that it will automatically update to produce the same output without manually updating the formula each time I edit the number of input rows? Would an index/match formula work, and if so, what would it look like?

## Making A Cell With A Formula Appear Empty

Mar 16, 2009

I have a chart with blank values in some cases. I have set up the chart to show those values as gaps, which relies on the blank cell being empty. However, my blank cells have a formula along the lines of: if (condition,value,"")

So the chart does not consider them blank, presumably either because the formula is in there, or because "" is not the same as blank. How can I make this cell appear blank for the purposes of the chart?

## Making Formula Calculate At The Top Of Hour

Apr 19, 2014

I have a spreadsheet that I use to track my stocks. I have installed a third party add-in that goes out to the internet and grabs various stock information. But, when you are pulling info on about 30 stocks and getting info like name, price, dividend, div pay date and such, it slows the spread sheet down tremendously. Is there a way to keep the value in the cell and change it only during a certain time period such as 5 minutes before the top of the hour to five minutes after the top of the hour.

## Formula For Missing Digits

Dec 4, 2012

I have this formula :

=SUM(LARGE((1-ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},AQ10)))*{1,2,3,4,5,6,7,8,9,0},{1,2,3,4,5})*10^{1,2,3,4,5})/10^ISNUMBER(FIND(0,AQ10))

But sometime the number 1 is missing ? see example :

3890 the formula return : 24567 so I'm missing the digit 1 ?

same for this one :

2340 the formula return : 56789 so I'm missing the digit 1 again ?

and for this one it's good :

2389 the formula return : 145670

## Making Dropdown Menu That Will Change Formula?

Dec 10, 2013

At the moment if a pupil has done foundation listening then I write their numerical result in, for example C3 or if they have done higher listening I write it in F3 (and it works out the UMS and then the grade), but I wondered if there would be any way to just put a dropdown menu in a column for each name, or change it so that I can type in H or F that will change which data is used? Because the UMS and grades are different depending on whether the pupil has done higher or foundation I am unsure how to format it

## Making A Formula To Automatically Fill A Label

Jun 8, 2009

As I am a fresh user of excel 2007 I do not know how hard it is to solve the problem I am facing but hopefully there is a solution that the readers here are willing to share with me.

I have a list of **-s that I have recorded as a hobby with my friends (karaoke, free concerts...) and I have them coded in a specific way, so that I can find them on a shelve easily. The list is built up in a form that is seen on the example file that I added.
So far I have used my own handwriting and a sticker to label my ** boxes. Itīs not a very good looking combo to be honest. Since I already have all the data in my excel tabel it would be good to use excel to take all the things from there and just print it out.
How to do that? The front cover is easy, I used VLOOKUP function. All I have to do is enter a number into the column C1 and the front label gets filled.

The problem for me is the back cover that should take the right track nr, title and length from several rows (VLOOKUP uses only one row at a time)? How to make it work, that excel would know that on the first album there are 3 tracks and when chaging a number in C1 there would be 2 tracks on a second one.

## Formula- Making A 'sports Day' Spreadsheet For A Project

Aug 28, 2007

I am making a 'sports day' spreadsheet for a project and i have one problem, the spread sheet is meant to have the possiblitlity of mistakes reduced to a minimum.

I have 6 teams in the sports day and under each i put 1st, 2nd,3rd,4th,5th,6th.

[url]

basically i need the rows to only accept 1st 2nd 3rd..... only once. I need something like an error message to come up if a value is entered twice on a row.