Converting Test Scores to somewhat of a Linear Transformation

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

 

[MartinW]

Hi,

I couldn't follow your exact problem here but I think you are
looking for SLOPE and INTERSECT.

In the equation y = mx +c
m is your gradient (SLOPE in Excel)
c is your y-intersection (INTERSECT in Excel)

So your equation becomes
y = SLOPE(Known Y's,Known X's)x + INTERSECT(Known Y's,Known X's)

with your Known Y's + Known X's being your range of data.

Does that help?
Martin

 

[MartinW]

Whooops!! That should read INTERCEPT not INTERSECT

 

[MartinW]

Hi again,

Rereading your post wouldn't it be easier to express them in terms
of percentages.

Test 1 with a maximum of 100 is already in percent
so pass is 70%

Test 2 pass = 50/70*100 = 71.43%
Actually the equivalent pass point of 70% is 49 not 50

a score of 60 =60/70*100 = 85.71%

HTH
Martin

 

[MartinW]

OK forget the percentage we'll go back to Linear regression.
With E1:-70 F1:-100 E2:- 50 F2:-70
putting this formula in C1 will return B1 adjusted by linear regression.

=SLOPE($E$1:$F$1,$E$2:$F$2)*B1+INTERCEPT($E$1:$F$1,$E$2:$F$2)

HTH
Martin

 

[pkaraffa]

OK forget the percentage we'll go back to Linear regression.
With E1:-70 F1:-100 E2:- 50 F2:-70
putting this formula in C1 will return B1 adjusted by linear regression.

=SLOPE($E$1:$F$1,$E$2:$F$2)*B1+INTERCEPT($E$1:$F$1,$E$2:$F$2)

HTH
Martin

Thanks Martin,

That did the trick. I appreciate the time that you took to review my
post and the time that you put in to get me an answer. Thanks Again!

 

[MartinW]

Thank you for the feedback, I should have paid more
attention when reading your original post.

Regards
Martin