Forecasting Formula using non mathematical data

[Doofus1]

Hi Folks,
I have been shown a formula for forecasting a test score using prior test scores and dates on which the tests were taken.
Thus, the formula, for what it is worth, uses multiple criteria to forecast a test score. The formula is:
{=IF(E5=E4, "", IF(COUNTIF($E$6:$E$50000,E5)=1, I5, FORECAST(B5, IF($E$6:$E$50000=E5, $I$6:$I$50000), IF($E$6:$E$50000=E5, $D$6:$D$50000))))}

However, I have two problems:
1. Does anyone know a better way to write this formula (to theoretically, make it more accurate)
2. I need to write a part of this formula to display a number based on a letter grade .

I have uploaded an excel file to explain my problem - Please see column L.

Any help will be appreciated.
Thank you

[FlameRetired]

@ #1 I don't know.
@ #2
I believe you will need a lookup table for that. I put the lookup below in U5:V7, and then this array formula in L5 and copied down.

Please Login or Register  to view this content.

A	5
B	4
C	3

[Doofus1]

Hi FlameRetired,
Thank you.
This almost works. However, I get an error message for a student who has only taken C level/grade tests. I have re-uploaded a new file to give you or anyone else who is willing to offer a solution, - a visual of the problem.
The student JIMMY, produces the error message. Jimmy has only taken C level/grade tests so far. He will be taking another C level test again today.
The formula appears to forecast a number for student's that have taken different grades of tests.
However, it produces an error for Jimmy.
I don't know how to re-write the formula to incorporate/calculate the forecasted test score for a student that has never taken any other grade of test.
Thank you

[AliGW]

It seems to me that the error is because all of Jimmy's test levels in column J are the same: change just one of them, and you get a result. So you need to decide how you want the forecast to be calculated in these cases and incorporate that into your formula as an error trap. What do you want that calculation to be? An average of all test scores to date?

[6StringJazzer]

With regard to #1 in the first post, FORECAST uses linear regression to do forecasting. How did you arrive at the decision to use this method to forecast test scores? In forecasting it is important to use a mathematical model that best matches the real-world behavior you are trying to forecast. Does your past data, or studies, show that student performance increases linearly over time?

It would also be helpful to show the R² value to give you some insight as to how well your forecast fits the existing data. Because your formulas are more complicated than just a straight use of FORECAST I would have to spend a little time to figure out how to do that for your specific case. I'll come back if I figure it out.

[Doofus1]

Hi AliGW,
In this instance, it so happens that student JIMMY has only taken C level tests ( The tests are ranked from A to C based on level of difficulty).
The test grade, cannot be changed. Changing it means that the student took different grade test.
The tests can (theoretically) reveal the student's level of comprehension of the subject matter.
The tests are also, given at random - with the student having no knowledge of what level of test is to be given or even when.
The forecast figure is a theoretical exercise to see how the student is likely to perform.
For a student such as this, can the formula be written to produce a simple forecast (when the test levels are all the same) while also, keeping the forecast formula for the other students that have taken other tests at different levels?
I hope this makes sense.
Also, please look at my response to 6StringJazzer as well (in case it helps to clarify my dumb thought process).
Thank you

[Doofus1]

Hi 6StringJazzer,
I decided to use this method because the basic forecast formula was easy to explain the concept to others. From there I looked at how I could use multiple variables in this formula - in this instance, the past data is (1) the prior test scores (2) the level of difficulty of the test (A,B & C) [A=most difficult, B = intermediate, C = easy] (3) today's level of difficulty.
ForumGuru FlameRetired offered a suggestion for dealing with the letters associated with the grade of the race.
But when I incorporated that suggestion into the formula he produced, I noticed this event occurring for a few students that showed the issue like it showed for student JIMMY.

Past data, (while in hindsight, is accurate) is enough to offer insight into the student's level of comprehension of the subject matter.
The tests can (theoretically) reveal the student's level of comprehension of the subject matter.
The tests are also, given at random - with the student having no knowledge of what level of test is to be given or even when.
The forecast figure is a theoretical exercise to see how the student is likely to perform.

In this instance, the formula, theoretically, is used to predict the likely result today for the current test grade/level/type given past test scores and past test grades/levels/types.
This (again, theoretically) works fine - as long as the students have, all, previously taken various levels of tests.
The problem occurs when a student has only taken tests at a single test level and is going to be given another test at the same level as all his/her previous attempts.
(As an FYI - the tests are given at random - but student JIMMY has been lucky enough to keep getting tests at the same level).
I hope this makes sense.
The trick would be to incorporate this possibility into the current formula.
Thank you