Hi, please note that I am a novice excel user, so very explicit directions are appreciated.
I am hoping to track students' reading level progression through the duration of the school year with an excel spreadsheet so it can be easily visually represented with a line plot. Fountas and Pinnell reading levels are assigned letters (i.e. beginning readers are A, B, C, and more advanced readers are X, Y, Z).
Ideally, column A is for students' names, B is for the baseline reading level, C is Quarter 1, D is Quarter 2, E is Quarter 3, and F is Quarter 4.
If possible, I want column G to show a numerical progression that expands its field of input as more data is added (e.g. as the quarters progress). For example, if a student's baseline (column B) is N, and their quarter 1 level is R (column C), I want column G to indicate "4" for the four levels the student has progressed. But then, at the end of quarter 2 (column D) with a level of S, I want column G to indicate the difference between column D and B. And so on...
Then I want column H to indicate (with color coding) whether the student is on, above, approaching, or below grade level based on the most recent data point, or just column F (e.g. in fourth grade, students are expected to be reading at a Q, R, or S level. Therefore, based on the most recent Quarter or the end of quarter 4, I want column H to indicate A through M to be "below grade level," N through P to be "approaching grade level," Q through S to be "on grade level," and T through Z be "above grade level."
Also, I want to plot the reading level progression for each student on a line graph.
Therefore, to complete the desired functions in columns G and H and pull the data for a graph, I am assuming that the letters used to represent reading levels in columns B through F need to be assigned numerical values. How do I accomplish this?!
THANK YOU!!!
I am hoping to track students' reading level progression through the duration of the school year with an excel spreadsheet so it can be easily visually represented with a line plot. Fountas and Pinnell reading levels are assigned letters (i.e. beginning readers are A, B, C, and more advanced readers are X, Y, Z).
Ideally, column A is for students' names, B is for the baseline reading level, C is Quarter 1, D is Quarter 2, E is Quarter 3, and F is Quarter 4.
If possible, I want column G to show a numerical progression that expands its field of input as more data is added (e.g. as the quarters progress). For example, if a student's baseline (column B) is N, and their quarter 1 level is R (column C), I want column G to indicate "4" for the four levels the student has progressed. But then, at the end of quarter 2 (column D) with a level of S, I want column G to indicate the difference between column D and B. And so on...
Then I want column H to indicate (with color coding) whether the student is on, above, approaching, or below grade level based on the most recent data point, or just column F (e.g. in fourth grade, students are expected to be reading at a Q, R, or S level. Therefore, based on the most recent Quarter or the end of quarter 4, I want column H to indicate A through M to be "below grade level," N through P to be "approaching grade level," Q through S to be "on grade level," and T through Z be "above grade level."
Also, I want to plot the reading level progression for each student on a line graph.
Therefore, to complete the desired functions in columns G and H and pull the data for a graph, I am assuming that the letters used to represent reading levels in columns B through F need to be assigned numerical values. How do I accomplish this?!
THANK YOU!!!
-- I can't thank you enough for your help on this project! 
