CODEWITHSUNDEEP
excelnormalization
Matjaz Lipus
Matjaz Lipus

Reputation: 711

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

Upvotes: 6

Views: 46607

Answers (4)

user8333064
user8333064

Reputation: 11

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.

Upvotes: 1

Jpoony
Jpoony

Reputation: 1

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)))

Upvotes: 0

IITian
IITian

Reputation: 21

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)

Upvotes: 2

Dr. belisarius
Dr. belisarius

Reputation: 61026

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

Upvotes: 7

Related Questions