converting letters to numbers

I am using Excel 2013.

I have a grade book with letter grades (not like school). I need to know how to convert to a letter into a specific number.

I took averages and using the IF function converted the averages into letters (like school). I tried to use the same process and simply reverse the command but that did not work.

Please help.

Thank you.
PC

this pattern continues until the program is completed so it could continue for several more rounds.

Week 1 Week 2
# Score 1 Score 2 Score 3
1 D D D
2 A B A
3 A B A
4 A B A
5 A A A
6 A F A
7 F A A
8 A B F


Letter
Grade
Key Score 1 Score 2 Score 3
A 5 8 11
B 4 7 10
C 3 6 9
D 2
F 1

Until I can figure out how to upload a sample file the above example is the best I can do. My apologies.

I need to be able to code into a cell a range of number which would represent certain letters. For example, in column 1: A=5, B=4, C=3, D=2, F=1. In column 2: A=8, B=7, C=6. In column 3: A=11, B=10, C=9. In column 4:A=14, B=13, C, 12. this pattern repeats for any further columns.

Any suggestions?

Does this help?

Whoa.

Thank you (you probably know how long I have been looking for this). I'll let you know if it works, especially if I am able to key in future columns now without available data (as if the top chart was unfilled-- see attachment).

Okay, question:

In your spreadsheet you have the "D" in column 2 ranked as a 5, but it should be an 2. And in column 3 you have "D" as 11, but it should be 2 (because "D"'s and "F"'s never advance beyond their last number (hence the reason for the gray spot on the middle chart).

How do I configure ranges based on IF THEN? Or can I?

Hopefully this gives further clarity of what I am trying to accomplish. The middle table is the conversion between the top and the bottom tables.

(please note that a score of "D" or "F" in columns 2-forward correspond with the last number given in the previous column)

The top table is not complete because I do not have the data yet but I would like the bottom table setup so when I enter the letter in the top , the bottom table automatically populates.

Please let me know what you think. Thank you.

I did.
Do I just need to cut and paste the first cell and drag it across to make it apply to all columns?

Thank you for all your help.

Man, that is so close!

The future "D"s and "F"s need to retain the number of the previous column, not the original score. But I am going top play with it and see if I can make it adapt.

Do you know anything about using linear regression, poison, etc. in Excel?

Quick example:

In the attached example uuencoded example, #'s 1 and 2 under the Score 11 column remain the same as the previous number in the Score 10 column (because "D" and "F" do not advance beyond the previous number until Score 12 column where the next "C" or higher exists. Please see attachment.

How do I augment the code to reflect this progression of specific information?

Thank you for your continued assistance.

To give additional example:

Columns "G24" through "U24" should all be number "2", as is the last highest score available, per the middle chart.

Columns "G25" through "U25" should all be number "11, and so on, until the top table is filled in. Once it is (with either letters "A,B,C,D,F"), the bottom table should reflect the corresponding number according to the middle chart. Example: if column "G4" is a "C", then column "G24 should be "12", and so forth.

Please let me know what you think.

Thank you.
PC

This was really close. However the "D"s and "F"s for some reason code back to the two first columns (unless I did something wrong).

Please see the attachment for more clarity.

I need the "D"s and "F"s to hold the value of the previous cell (example: so column "U24" has an "F" value in the top table, therefore in the bottom table it should have the value "51" because the last progressive value, in the top table, was "C" in column "T4", which provided the corresponded value of "51" in column "T24"-- same for column "N24" which should be "32" in the bottom table to correctly correlate with the top table where in column "M4" has a value of "A" so in the bottom table column "M24" has a value of "32" to correspond with the middle chart).

Does this explain better? The formula in the bottom table needs to be able to recognize that when a value of "D" or "F" is present that the numerical value in the bottom table cannot advance beyond the last given number for that row, until a value of "A"/"B"/"C" is given in the top table (and when "A"/"B"/"C" is then given, the new value in the bottom table must correspond correctly to the middle chart).

Thank you for you assistance.