normalize data to scale from 1 to 10

I have a following data set:

A   B   N
1   3   10
2   3   5
3   3   1
3   6   5
10  10  1
20  41  5
20  120 9

I'm looking for an excel function that will normalize A and B to N on scale from 1 to 10.

In above example it would be

1 of 3 is best so N = 10
2 of 3 is in开发者_JS百科 the middle N = 5
3 of 3 is worst N=1
20 of 120 is in second decade N=9

A >= 1 && A <= B

B is natural number

1 <= N <= 10

Supposing your numbers are in cols A and B. Write at c1:

=MAX(CEILING((B1-A1)/(B1-1)*10,1),1)

And drag to the bottom of column C

You should use the trend function.....I don't know how good you really at solving equations but here is goes

Use trends function in excel.....

trends( Y set of values, x set of values, new x set of values, true)

now specify scale 1 and 10 in Y set of values

for x set of values specify B and 0

and then for new x set of values specify value that you want to normalize

A B
20 120

B =120

since it works on y=mx + c

(x1,y1) = (120,0) and (x2,y2) = (0,10)

any new x that you enter will be normalized on basis of this

so if I enter 20 in new x, then my answer will be 8.333 not 9 (it is wrong)

10*(Cell-MIN(Range))/(MAX(Range))-MIN(Range)). The question was really very simple. He has data that's not in the range of 1-10...and he wants the highest value to be equal to 10, and the lowest to be equal to 1, with the other values normalized between them.

"cell" is the actual cell where you are putting in the formula. The range is the data to be normalized.

If the data is "lower the better" try this Column D1 and drag down

1+(A1-MIN(A$1:A$7))*(0-10)/(MAX(A$1:A$7)-MIN(A$1:A$7)))