Assign Consistent Letter Value To Grade Percentage

Oct 9, 2008

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

View 12 Replies


ADVERTISEMENT

IF THEN Statements: Assign A Letter Grade To A Number

May 9, 2006

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

View 3 Replies View Related

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

View 2 Replies View Related

Change Letter Grade To Number Grade

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

View 6 Replies View Related

Turning Grade % Into Letter Grades

Aug 11, 2007

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.

View 9 Replies View Related

Converting A Numeric Cell To A Letter Grade

Feb 14, 2009

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.

View 11 Replies View Related

Excel 2010 :: Letter Grade Calculator

Dec 29, 2013

I created an Excel 2010 macro enabled workbook (.xlsm) which calculates the letter grades of test scores between 1 and 100.

To use the workbook:

1. Put the scores in one column of the open worksheet.

2. Select the first score with the mouse (i.e. make this the "active cell").

3.Click on the "Calculate Grades" button on the sheet.

Public Function LGrade(ByVal score As Variant) As String
'Returns the letter grade based on a test score.
On Error GoTo ErrHandler

Select Case score
Case Is > 100: LGrade = "A+"
Case 95 To 100: LGrade = "A"
Case 90 To 94: LGrade = "A-"
Case 87 To 89: LGrade = "B+"

[code].....

View 4 Replies View Related

Excel 2007 :: Highest Letter Grade In Range

Nov 28, 2012

Listed below are the grades for a student. Some columns have blanks. I have to find the highest grade (A being the highest) in the range.

I'm going up with the opposite (F) with this formula:
=CHAR(MAX(INDEX(CODE(A22:M22&CHAR(1)),0)))

How do I get A?

I'm using Excel 2007

A
B
C
B

D
E
A

A

B
F

View 6 Replies View Related

Assign Folder Based On First Letter Of Name

Jan 2, 2014

I have a routine that looks a variable's first letter and then assigns it to a specific folder. I have tried using just the first letter (firstletter) and the ASCII value of the first letter but neither seem to work - see below.

firstletter = Left(rng.Value, 1)
iletter = Asc(firstletter)
MsgBox iletter
If iletter < 71 Then cmfolder = "CM A-F" Else
If iletter > 70 And iletter < 77 Then cmfolder = "CM G - L" Else
If iletter > 76 And iletter < 83 Then cmfolder = "CM M - R" Else cmfolder = "CM S - Z"
MsgBox cmfolder

View 1 Replies View Related

Assign Numerical Values By Letter Across A Range

Dec 17, 2007

I have a formula that counts all instances of the letters a, b and c in a range and assigns them a value of 1 unless they are in the M column in which case a, b or c counts 4.

=SUM(COUNTIF(J2:AB2,{"a*","b","c"}),OR(LEFT(M2)={"a","b","c"})*4)

This worked well but now I need to modify the weightings across this range. My new goal is to count all instances of a,b and c in the range J2:AB2 but have M column a,b, c's = 2 and N:R column a,b,c's equalling 4. Letters other than a,b or c count as 0.

Example:

J2...K2...L2...M2...N2...O2...P2...Q2...R2...
A....B.....B.....C.....B.....D....B.....C.....E

the output of the above sequence should be

1+1+1+2+4+4+0+4+4+0 = 21

View 4 Replies View Related

Compare Actual Grade To Potential Grade And Highlight Cells

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.

View 7 Replies View Related

Formula To Assign Word To A Percentage

Mar 24, 2012

In cell B77 i have a percentage formulated from other information in the sheet. I want cell B78 to show a word based on the percentage score.

As follows:

0%-25%- Poor
25%-50% - Competent
50%-75% - Professional
75%-100% - Expert

View 4 Replies View Related

Calculate Final Grade Based On Grade Table

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

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.

View 9 Replies View Related

IF Formula: Assign A Specific Letter To Each Rating, Depending Of The Obtained Number

Jun 15, 2009

I have some delivery ratings from suppliers, and I want to assign a specific letter to each rating, depending of the obtained number. Criteria is:.........

View 4 Replies View Related

Assign A Score Value To The Cell That Contains A Percentage Depending On A Range

May 2, 2007

I have a spreadsheet with several columns that have percentages as a whole number and with a decimal format. I need assign a score value to the cell that contains a percentage depending on a range.

For example:

If cell A1 has a value between 97%-100% then A2 should display "15", however if A1 has a value of 90%-96% then it should display "12" and if A1 has a value of less than 89% then it should display "0".

View 9 Replies View Related

Consistent Layout

Feb 28, 2009

I have MS Office 2003 with Excel and am comfortable creating formulas and have fun making spreadsheets, but have little experience with multiple tabs. They frustrate me. I'd like to set up a file that would essentially be a year of bookkeeping. Everything in view would be income, outgo, anticipated expenses, and running cash flow for a month. I'd like 12 or 14 tabs (1 per month plus overview and recap capability). If I make any format changes in row height or column width, I'd like those changes to apply to all tabs without having to copy the new layout to each of the other tabs. I think that would run the risk of also copying one months expenses/income erroneously to another month. Is this possible? I'd like to stretch a column, add column or row and have the changes apply to all tabs.

Also, most of the time, I like to have a tinted background behind my lists, but if a cost item needs to be copied from a green "expense list" to a blue "accounts paid" list, the color comes along. Can I copy only the text, not the format?

View 6 Replies View Related

Sum Data That Is Not Consistent & Changes

Aug 31, 2006

i have attached a sample spreadsheet that i am having a real problem with. each period i have to produce a report showing the week and period-to- date figures for various sites. the info is held in Adaytum and a view is created in excel that pulls in the data. i then run various formulae to create the report.

the cells in blue are a linked view from Cognos/Adaytum (an external database program). the cells in green contain a SUMIF formula that works correctly until the Adaytum view is changed, whereupon all formula with cell references within the blue area turn to REF#. don't ask me why, it seems to be a quirk of this version of Adaytum i use

so, in the example attached if i change from period 06 to period 07 in the Adaytum view, my criteria cells (B2:E2) would go to REF# and the green cells (J5:J8) go to REF# also.

the only way i have found to get around this is to use a vlookup, and extend the lookup range beyond the blue cells, ie. =VLOOKUP($H5,$A$5:$F$8,2,0). for some reason this works!

now, for a single weeks info this works ok, as the lookup is referencing a single offset column but i can't think of a way of then saying, "do the lookup and then sum columns B+C, or B+C+D"

does anyone have any ideas how this might work? can the Sum or Offset functions be used within a Vlookup?

View 9 Replies View Related

Division Formula Not Consistent

Nov 9, 2009

In the attached file I am simply trying to get a percentage for a given group based on the column "annual starts". When I try and copy down the formula for the other cells it gives me an error because its using the wrong denominator (the errors show up in the excel sheet as #div/0).How can I make this consistent?

View 2 Replies View Related

VBA Not Following Consistent Rounding Rules?

Jun 13, 2014

This Subroutine:

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

Seems to behave differently each time it is called from my main Loop. As an example the first loop finds:
TotalTubes = 6
TubeRows = 4
TopRowTubeCount = 2
Where 6/4 = 1.5 and is rounded up to 2

The controlling variable in my code for Length is TopRowTubeCount and it is a Public Variable Dimensioned as an Integer

However on the next loop it finds:
TotalTubes = 210
TubeRows = 4
TopRowTubeCount = 52
Where 210/4 = 52.5 and is rounded down to 52?

I even used the round function for the TopRowTubeCount calculation and the erratic behavior continues.

View 3 Replies View Related

Consistent Decimal Places

Nov 30, 2006

Is it possible to apply some kind of formatting to a range, that will force all numbers entered into that range to have the same number of decimal places as the cell with the maximum number of decimal places?

View 9 Replies View Related

Keep Rows Number In Table Consistent

Jan 4, 2007

I have 3 columns of data "Store", "Week" and "Cust" and use the code below to ensure that it was always 150 consecutive rows long regardless of whether any data is in the row.

This is fine if I just have one week to sort, but I find I now have 52 weeks of data and each week could have a different number of stores. I just need the macro below to run each time a new week starts. I can easily put a space (or something else) at the start of each new week so that a " loop" can check for it.

For i = 1 To 150
If ActiveSheet. Cells(i, 1).Value <> i Then
ActiveSheet.Rows(i).Insert
End If
Next

View 4 Replies View Related

Convert Lower Letter To Capital Letter Automatically

Mar 2, 2009

When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?

View 9 Replies View Related

Return Column Letter Based On The Letter In A Cell.

Dec 31, 2009

For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).

When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.

=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")

View 9 Replies View Related

Rotate Range, Keep Defined Names Consistent.

Dec 1, 2009

as the title says, i need to rotate a range but to keep all defined names (single cell or range) consistent.

i.e.
lets say i have the following cells filled:

1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
now i have a few defined ranges. lets say:
range name, refers to
mycell1row1, A1
mycell90roanything, B1
mysomething, C1
mysomthingrange, A1:F1

etc.

what i need in the end is this:

1 1 1 1 1 1 1
2 2 2 2 2 2 2
3 3 3 3 3 3 3
4 4 4 4 4 4 4
and for defined names:
mycell1row1, A1
mycell90roanything, A2
mysomething, A3
mysomthingrange, A1:A7

View 9 Replies View Related

Lookup #N/A! Error: Copy And Consistent Throughout The Sheet

Jan 11, 2007

I cannot determine why I am getting an N/A error in Cells I11:L11. The formula is copied and consistent throughout the sheet. The Table referenced in the formula appears to be fine. I just cannot figure this out.

View 5 Replies View Related

Excel 2003 :: Get Dates To Remain In Consistent Format

Apr 11, 2012

I have been struggling to get dates to remain in a consistent format within my Excel 2003 worksheets. I would like to work out how to keep them permanently in the "dd/mm/yyy" format. I have tried several formatting options. The current method I will explain below:

I have several forms into which the 'date' is automatically entered using the - Format(Date, "dd/mm/yyyy") code. This is then saved into cells on a worksheet using the code

ws.Cells(iRow, 1).NumberFormat = "dd/mm/yyyy"
ws.Cells(iRow, 1).Value = CDate(Me.Date1.Caption)

At this point if I do a search for a date, lets say 23/03/2012 I can retrieve it and everything related to it - all seems fine (also all dates are visually in the correct format in my cells etc).

The problem arises when I exit Excel and save the workbook. When I go back into the workbook at a later time and try and search for the same date it won't work. Although the dates in the cells still read for example 23/03/2012, the search requires me to now use the format 03/23/2012 to retrieve the information (It seems like in the background the date has reverted to the US date format upon saving and yet visually it is still how I've set it up).

View 1 Replies View Related

Change Letter Case Of Cells First Letter Only?

Mar 7, 2014

I need to change a few hundred cells (one column) where the first letter may be a capital letter to a lowercase letter.

So:
GetAwardfromBid to getAwardFromBid
or
SmallLertter to smallLetter

View 3 Replies View Related

Gross Margin Formula That Maintains Consistent Markup Price Across Varying Costs

Jan 8, 2014

So I can easily put together a formula that maintains a consistent markup price across varying costs. Is there a formula that will give me a consistent gross margin % as I drag down a series of costs?

So if the cost is 158 a 42% mark up would be 224.36 but the Gross Margin % is only 29.58. I want to be able to hit a 42% mark up across the board.

View 5 Replies View Related

Convert Human-readable Text Strings Into Consistent Number Of Weeks From Today Date?

Dec 17, 2011

Is it possible to do the following with a formula in Excel...

I have a list of users with the dates they first logged into a system and the date they last logged in. I'm trying to group them together into segments so I can analyse them using a Pivot table and chart to see how often different groups are using the system, e.g. New customers in December, November, October, etc.

The trouble I am having is trying to convert the different human-readable text strings into a consistent number of weeks from today's date, e.g.

USER FIRST LOGGED IN LAST LOGGED IN
user 1 2 years 20 weeks 54 sec ago
user 2 44 min 7 sec 1 min 37 sec ago
user 3 49 weeks 2 days 17 min 3 sec ago
user 4 5 weeks 2 days 33 min 32 sec ago
user 5 38 min 9 sec 38 min 9 sec ago
user 6 5 weeks 3 days 1 hour 7 min ago
user 7 2 hours 17 min 2 hours 11 min ago
user 8 45 seconds ago 45 seconds ago

Is there a formula I can use to convert these human readble text strings into a number of weeks elapsed since today's date?

View 9 Replies View Related

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.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved