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 RepliesNeed 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
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 ma going to have a couple of tricky (to me at least) table/range look ups, so let me start with this one:
.......a........b...................c.....................d....................e
1............Dive 1......................................School Grand Totals
2..team.....diver nr......... points...............Boys...............girls.
3 md b.......1..................3.....................md b...8..........md g...4
4 md b.......2..................3.....................jb b....7
3 jb b........3..................3................
6 md g,......4..................3
7
8..............Dive 2
9..team.....diver nr......... points...
10 md b.....1..................1...........
11 md b......2.................1...........
12 jb b......3.................4
13 md g......4................1
Notes about above. The naming is like this. "md b" is "martin county boys". "md g" would be "martin county girls". "jb b" is "jensen beach boys".
I need to add up each school grand totals for all the dives, and break them down to boy or girl for each school. this score will eventually go into another total which totals 11 swimming events by boys or girls.
So, in essense, thre are going to be several totals for the meet:
1 Diving (6 dives) by School by Boy (note: only showing 2 dives here)
2 Diving (6 dives) by School by Girl
3 Swimming, (11 events) by School by Girls
4 Swimming, (11 events) by School by Girls
5 Grand total by school (boys and girls scores combined)
Texaslynn (I believe that was her handle) helped me previously with totaling a grand total. However, now I discovered I need the total by boys and girls and then the combined (boys and girls) school total.
I have a string of payments and want to calculate the Net Present Value. Also, what should be used as a discount rate?
View 7 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.
I have a table with employees' persinal data including birthdays. My aim is to insert an indicating field that would show whether the birthday is gonna come soon.
It shold have several states:
number of days left if the birthday is current or next month
"not soon enough" if the birthday will come in more than 2 months (later than next month);
"happy birthday" if it is today;
"the past weekend" if it was the past weekend and it is Monday today.
What can you suggest?
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 RelatedI wrote a formula to recognise when a child equalled or bettered a club record (CR) in an event. However what I neglected to do and cannot work out is for the formula to recognise the new time as the new club record that will then have to be equalled or bettered from then on to be recognised as a club record. For example CR for 70 metres is 9.28 secs. Formula I was working with was IF(ISBLANK(B3),""),IF(B3
View 9 Replies View RelatedHere 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 would like to link students for the 1st Nine Week list to the correct grade level as shown. I have attached a file.
View 2 Replies View RelatedI 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?
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.?
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 View RelatedI 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...
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 need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance:
KEYPerformance
2010,000
2120,000
2230,000
2340,000
I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges:
KEYMin FeeFee 1Fee 2Fee 3Fee 4
201000.10.20.30.4
211000.10.20.30.4
221000.10.20.30.4
231000.10.20.30.4
For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows:
Fee Range 10-9999
Fee Range 210000-19999
Fee Range 320000-29999
Fee Range 430000-99999999
So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest.
How can this be combined into a lookup/range/low-high formula to extract the correct values????
I have a spreadsheet that has staff id in one column and the work items number that they have done in a daily basis in another column.
The actual list is very long. I need to summarize in another column how many work items that they have completed in a daily basis.
I have attached a sample spreadsheet as an example. I would need to summarize in column H based on the staff ID. Some work items are shared by two staff but it will have to be counted as one work item completed for each staff. If work item B123466 is completed both by staff M56 and M54, then it will be counted as one for each.Currently, I am doing this manually with the filter function which is very tedious and often has mistakes. I would like to formularize this task.
I have a scorecard that looks something like this:
90% or greater=3
80%-89%=2
70%-79%=1
0%-69%=0
I need the "cell" to react accordingly and I'm lost.
I need a formula to calculate age today based on a person's date of birth. I used to know this but I have not used it for awhile.
View 4 Replies View RelatedI would like to have something that looks like a running total, but isn't.
I have in a colomn 'rate' and after that a column for each month in a year.
I would like to have at the bottom a total cost, so rate x hours.
have a look at the picture.
is there a formula for this?
ratemayjunejuli
$10 2,02,01,0
$12 2,03,00,0
$10 3,02,05,0
$15 4,02,53,0
total cost $134,0 $113,5 $105
I am looking for a formula that can change the price of some of the items on sheet1 in column C by the amount found on Sheet2 in Column D. I would like it to base the calculation on column C in sheet2 (so I can choose if I want to add, subtract, multiply, divide, or make the price exact). I would like all prices that don't match these UPC codes to remain unchanged.
Intended Results can be seen on Sheet1 in Column E. Not sure if I should a formula with the Vlookup function or a macro, or maybe there is even a better solution.
Example spreadsheet may be viewed at http://spreadsheets.google.com/ccc?k...WLMyhNJLiPLTfA.
i am trying to make an excel spreadsheet so i can keep track of my hours and pay at work... i know how to do mult and add but i wanted to know how to do the 2 together.... for example
Hours ST OT Total Hrs Gross Pay
8 2 10 ?
i would like help figuring out the gross pay if say for example my st time rated is 21.21 an hr, and the ot rate is 31.82 how can i get the 8 times 21.21 = 169.68 and the OT 2hrs times 63.64 to show up in the gross pay as a total of 233.32 - what would the formula look like?
I only want the formula to operate when there are values to calculate and then ONLY refer back to the last calculation. If there are gaps between the calculations I would like those to remain blank. Is that possible?
I have attached an example of what I am trying to do.
I have in column A a list of investment fund names. In columns B to D is respectively for each fund its Market Value, the Rate of Return and the Rank (1, 2, 3 or 4). I am trying to create a formula that will calculate the market value-weighted rate of return for each ranking.
For example, the 3 funds that rank first have market value weights of 10%, 12% and 13%, and rates of return of 1%, -3% and 5%. The total market-value weighted return for the top-ranked funds would therefore be 0.39% (i.e. 0.10*0.01-0.12*0.03+0.13*0.05)
The formula would first need to look in the ranking column to identify those funds with a particular rank (column D), then calculate their weights by dividing their market value by the total market value of all funds (column B), and finally multiplying these weights by the rate of return (column C), before summing the results to say, for example, that the total market-value weighted return for the top-ranked funds is 0.39%
I'm looking for a function that calculates a fee deduction based on the 28th of each month.
I'm paying back £200 on 28th of every month starting 28th Feb and was hoping that a formula could keep track of this...
A
B
C
D
1
£1,300.00
=TODAY ()
2
-£200.00
28/02/13
FUNCTION
How to do it but basically I'm trying to put a formula in D2 as follows:
If Today's date (C1) equals C2 I need B1 to reduce by the amount in B2
How to continue it calculating reductions per month by duplicating the formula...
I have a file to calculate bonus based on productivity. The bonus percentage is dependant on productivity percentage and the category of employee. I have given in the below attached file, the range of productivity% and the bonus % for each category. I want to build a formula, to get the bonus % in a seperate table. I tried to do the same using "If" condition, but its not working, as the number of nested IFs are going beyond 7. I have highlighed the column where I want to calculate the formula.
View 5 Replies View RelatedI have a form in work which acts as a tick sheet for tasks complete on a construction site. When a task is complete I copy & paste from a key of dates i.e. week ending 11.05.15, this allows me to see what week a task was complete.
Generally I only marked off tasks which were 100% complete but my manager wants me to enter the % of the task complete also, i.e. 80% in the box.
So my question is how can I have the formula assume that any box with a date & shading is 100% and any cell with a number i.e. 80 is only 80% complete. I need it to monitor around 150 cells and give me the overall % complete based on what i have discussed.
I've got a spreadsheet where the start date is amendable and this is what I wanted. E.g. start date is Jun-14 and I want the formula to to calculate annual salary divided by 12 in each month from June onwards until Mar-15 (Financial year is April to Mar) and the same way to not show anything in April or May as they started in June... How can i achieve this please as I tried IF statements and it doesn't recognise dates and years...
Link: [URL] ....