Normalize/scale data set

Question

I have the following data set:

dat<-as.data.frame(rbind(10,8,2,7,10,10,1,10,14,9,2,6,10,8,10,8,10,10,7,11,10))
colnames(dat)<-"Score"
print(dat)
Score
10
8
2
7
10
10
1
10
14
9
2
6
10
8
10
8
10
10
7
11
10

these are the test scores which students obtained, a student could get a maximum of 15 or a minimum of 0 in this test (by the way, nobody got the max or the min), however the lowest score obtained in this test was 1 and the highest was 14.

Now, I want to normalize/scale this data to the scale of 0 to 20. How to achieve this in excel? or in R? My final goal is to normalize the scores in this test to the above scale and to compare them with another set of data for which the max and min is 5 and 0 respectively. How to compare these two different scaled data sets correctly against each other?

What I tried: I went through many stuff on the internet, and came up with this:

which I got it from the wikipedia. Is this method reliable?

Answer 1

Here's a little r function which can help you run this if you need to repeat the operation and give you some flexibility on what you rescale to. Also one must be careful of NA values because min() and max() do not drop them by default which will then return NA. Therefore I provided an option on to handle NA values (drops them by default).

# function rescales data from 0 to 1 and optionally multiplies by new max
rescale <- function(x, new_max = 1, na.rm = T) {
  as.vector(new_max * scale(x, 
                  center = min(x, na.rm = na.rm), 
                  scale = (max(x, na.rm = na.rm) - min(x, na.rm = na.rm))))
  }

# old scores
scores <- c(10,8,2,7,10,10,1,10,14,9,2,6,10,8,10,8,10,10,7,11,10)

# new scores
data.frame(old = scores,
           new = rescale(scores, new_max = 20))
#>    old       new
#> 1   10 13.846154
#> 2    8 10.769231
#> 3    2  1.538462
#> 4    7  9.230769
#> 5   10 13.846154
#> 6   10 13.846154
#> 7    1  0.000000
#> 8   10 13.846154
#> 9   14 20.000000
#> 10   9 12.307692
#> 11   2  1.538462
#> 12   6  7.692308
#> 13  10 13.846154
#> 14   8 10.769231
#> 15  10 13.846154
#> 16   8 10.769231
#> 17  10 13.846154
#> 18  10 13.846154
#> 19   7  9.230769
#> 20  11 15.384615
#> 21  10 13.846154

^{Created on 2022-03-10 by the reprex package (v2.0.1)}

Answer 2

That is very simple. Due to the fact that both of those grades are linear, that a simple multiple ratio will do the work. Or in other word each grade in your set needs to be *20/15.

Answer 3

In Excel, if you want the normalized data to have a min of 0 and and max of 20, then we need to solve:

y = A * x + b

for two points.

Put the max of the raw data in C1:

=MAX(A:A)

Put the min of the raw data in C2:

=MIN(A:A)

Put the desired max in D1 and the desired min in D2. Put the formula for the A-coefficient in C3:

=($D$1-$D$2)/($C$1-$C$2)

and the formula for the B-coefficient in C4:

=$D$1-$C$3*$C$1

Finally put the scaling formula in B1:

=A1*$C$3+$C$4

and copy down:

Naturally, if you want the scaling to be independent of the raw max or min, you would use 15 in C1 and 0 in C2.

Answer 4

In your case I would use the feature scale formula you posted on your question. The (x - min(x)) / (max(x) - min(x)) will essentially convert your test marks to the range between 0-1.

Since your edges are indeed 0 and 15 and not 2 and 14, your min(x)=0 and your max(x)=15. Once you have your marks between 0-1 using the above, you just multiply by 20.

i.e.

tests <- read.table(header=T, file='clipboard')

tests2 <- (tests - 0) / (15 - 0) #or equally tests / 15

And multiply by 20 to get marks between 0-20:

> tests2 * 20
       Score
1  13.333333
2  10.666667
3   2.666667
4   9.333333
5  13.333333
6  13.333333
7   1.333333
8  13.333333
9  18.666667
10 12.000000
11  2.666667
12  8.000000
13 13.333333
14 10.666667
15 13.333333
16 10.666667
17 13.333333
18 13.333333
19  9.333333
20 14.666667
21 13.333333

The results are intuitive and the function is reliable. For example the person who scored 14/15 should get the highest mark (and very close to 20) which is the case here (after the transformation they scored 18.6666).

Answer 5

The math way is fairly straightforward if the floor for all scales is 0.

new_value = new_ceiling * old_value / old_ceiling

The next formula will account for different floors on each scale:

new_value = new_floor + (new_ceiling - old_ceiling) * ((old_value-old_floor)/(old_ceiling-old_floor)) which is actually the formula you posted from Wikipedia. ;)

Hope this helps!

Answer 6

You can scale between 0 to 20 with this command in R:

newvalue <- 20/(max(score)-min(score))*(score-min(score))