P
pkaraffa
I have been trying to figure out an excel formula that will convert
test scores. I figured it out algebraically but not in Excel. First I
will give the formula. Next the scenario.
Test 1: has as passing point of 70 and a max of 100
Test 2: has a passing point of 50 and a max of 70
To do a linear transformation
Call test1 y, and test2 x
Substitute into y = ax+b
At the passing score we get: 70 = a50+b
At maximum score we get: 100 = a70+b
Solve we a = 1.5 and b = -5
To convert test2 scores to a scale somewhat equivalent to test1 using
the formula : 1.5x-5
So a score of 60 on test 2 will transform to a score of 85
y=(1.5) 60 - 5 = 90- 5= 85.
What I would like to do is put Test1 scores in Range A. Test 2 scores
in Range B. Then In range e1: 70, In range F1:100 In range E2: 50 and
rangeF2: 70. Then have a formula in Range C that will give me the
scores.
test scores. I figured it out algebraically but not in Excel. First I
will give the formula. Next the scenario.
Test 1: has as passing point of 70 and a max of 100
Test 2: has a passing point of 50 and a max of 70
To do a linear transformation
Call test1 y, and test2 x
Substitute into y = ax+b
At the passing score we get: 70 = a50+b
At maximum score we get: 100 = a70+b
Solve we a = 1.5 and b = -5
To convert test2 scores to a scale somewhat equivalent to test1 using
the formula : 1.5x-5
So a score of 60 on test 2 will transform to a score of 85
y=(1.5) 60 - 5 = 90- 5= 85.
What I would like to do is put Test1 scores in Range A. Test 2 scores
in Range B. Then In range e1: 70, In range F1:100 In range E2: 50 and
rangeF2: 70. Then have a formula in Range C that will give me the
scores.