Normalize data to scale from 1 to 10

Question

I have the following dataset:

ABN 1 3 10 2 3 5 3 3 1 3 6 5 10 10 1 20 41 5 20 120 9 

I am looking for an excel function that will normalize A and B to N on a scale of 1 to 10.

In the example above, it will be

 1 of 3 is best so N = 10 2 of 3 is in 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 a natural number

1 <= N <= 10

Answer 1

Suppose your numbers are in columns A and B. Write in c1:

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

And drag the bottom of column C

Answer 2

You have to use the trend function ..... I don’t know how well you solve the equations, but here goes

Use the trend 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 the set of values ​​Y

for x set of values ​​specify B and 0

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

Ab
20 120

B = 120

since it works on y = mx + c

(x1, y1) = (120.0) and (x2, y2) = (0.10)

any new x you enter will be normalized based on this

so if I introduce 20 to the new x, then my answer will be 8.333 not 9 (this is wrong)

Answer 3

If the data is “lower, 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))) 

Answer 4

10*(Cell-MIN(Range))/(MAX(Range))-MIN(Range)) . The question was very simple. He has data that is not in the range of 1-10 ... and he wants the highest value to be 10 and the lowest to be 1, and the rest of the values ​​should be normalized between them.

"cell" is the actual cell into which you paste the formula. A range is data that needs to be normalized.