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.

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

Whooops!! That should read INTERCEPT not INTERSECT

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

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

On Feb 11, 2:28 am, "MartinW" wrote:
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!

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

Regards
Martin