plotting a list of values (average) on a scale

[td3201]

I'm taking a list of values that are in the range of 1-20 and want to plot their average on a scale of 300-850. In other words, if all values are 20, the score should be 850. If all are 1, they should be 300. However, with my formula below, it's not working out exact. I'm bastardizing a formula I used in a different context with weights factored in so I'm likely off. Here's the formula where the list of values is in L2:L31

=(AVERAGE(L2:L31)
*(850-300)
/(1*20)-(1*1)
+300)

Thanks for your wisdom!

[MrShorty]

It looks more like an algebra error (which I think will end up being a misplaced parenthesis or two, but I haven't done the algebra for you).

Standard linear interpolation formula (I assume this is what you intend to use): slope2=slope3 -> (y2-y1)/(x2-x1)=(y3-y1)/(x3-x1) Solve for desired unkown (I usually solve for y3) -> y3=y1+(x3-x1)*(y2-y1)/(x2-x1)

Your formula y3=y1+x3*(y2-y1)/x2 -x1 which is not the same. A pair of parentheses around the last (x2-x1) might be all that is needed, but I'll leave that algebra exercise to you.

As many times as I have done linear interpolation, I still need to go over the algebra (I can do it in my head now, but early on I had to write it out each time) to make sure I get it right. I would recommend the same to you.

[td3201]

I'm having an issue understanding how my numbers map to the formula. Is this correct?
y1=1 (low end of existing scale)
y2=300 (low end of new scale)
x1=20 (high end of existing scale)
x2=850 (high end of new scale)
x3=30 (# of data points)

I think this is wrong but having issues w/ plotting my problem to the linear interpolation formula that you've provided.

[MrShorty]

I don't think that's right (it might still work -- there are several ways to think through linear interpolation). Here's how I usually see it:

y applies to the new scale, x applies to the old scale.
point 1 is the main reference point, point 2 is the other known point, point 3 is the point I want to find (given one of the two values for point 3).
So:
y1 is low for the new scale [300]
y2 is the high for the new scale [850]
y3 is the unknown value to solve for.
x1 is the low for the old scale [1]
x2 is the high for the old scale [20]
x3 is the average old scale score [average(L2:L31)]

[td3201]

Applying this to the formula where X3 = 20 (the max of X) gives me 822.5 and it should give me 850:

=300+20-1)*((850-300)/(20/1))

The low end works fine. It gives me 300 somehow:
=300+1-1)*((850-300)/(20/1))

[MrShorty]

Be careful of parentheses (among my favorite algebra mistakes). Also pay careful attention to operations:
300+(20-1)... -- note the missing open parenthesis. It appears you are calculating this correctly by hand, but if you consistently forget to write the parenthesis in, you will eventually forget that it should be there.
300+(20-1)*(850-300)/(20-1) I am not sure how you ended up with a division in the denominator here, but 20/1 is not the same as 20-1. A lot of algebra is just paying attention to these kinds of details.