Converting 1-5 scale to 0-5 scale

femkewg · Sep 8, 2022

Hii all,
For my internship I have to convert a 1-5 scale to a 0-5 scale.
So the 1 is a 0 and the 5 is a 5 (with a range of numbers in between)
Is there a formula that can do this?
I have tried different things but I cannot figure this out on my own.

Thanks in advance!

bebo021999 · Sep 8, 2022

There is not enough information to work with.
what 1-5 and 0-5 scale is?
Could you dig in a specific example?

femkewg · Sep 8, 2022

bebo021999 said:
There is not enough information to work with.
what 1-5 and 0-5 scale is?
Could you dig in a specific example?

I have data from one data set that uses a 0 to 5 scale and one data set that uses a scale from 1 to 5
So now I want to compare the two and get the average, but since they do not use the same scale the average is off.

Example.xlsx

A

B

C

1

1-5 scale

0-scale

2

1

0

3

1,1

0,125

4

1,2

0,25

5

1,3

0,375

6

1,4

0,5

7

1,5

0,625

8

1,6

0,75

9

1,7

0,875

10

1,8

1

11

1,9

1,125

12

2

1,25

13

2,1

1,375

14

2,2

1,5

15

2,3

1,625

16

2,4

1,75

17

2,5

1,875

18

2,6

2

19

2,7

2,125

20

2,8

2,25

21

2,9

2,375

22

3

2,5

23

3,1

2,625

24

3,2

2,75

25

3,3

2,875

26

3,4

3

27

3,5

3,125

28

3,6

3,25

29

3,7

3,375

30

3,8

3,5

31

3,9

3,625

32

4

3,75

33

4,1

3,875

34

4,2

4

35

4,3

4,125

36

4,4

4,25

37

4,5

4,375

38

4,6

4,5

39

4,7

4,625

40

4,8

4,75

41

4,9

4,875

42

5

Vergelijking Likert scale

femkewg · Sep 8, 2022

femkewg said:
I have data from one data set that uses a 0 to 5 scale and one data set that uses a scale from 1 to 5
So now I want to compare the two and get the average, but since they do not use the same scale the average is off.

Example.xlsx
A B C
1 1-5 scale 0-scale
2 1 0
3 1,1 0,125
4 1,2 0,25
5 1,3 0,375
6 1,4 0,5
7 1,5 0,625
8 1,6 0,75
9 1,7 0,875
10 1,8 1
11 1,9 1,125
12 2 1,25
13 2,1 1,375
14 2,2 1,5
15 2,3 1,625
16 2,4 1,75
17 2,5 1,875
18 2,6 2
19 2,7 2,125
20 2,8 2,25
21 2,9 2,375
22 3 2,5
23 3,1 2,625
24 3,2 2,75
25 3,3 2,875
26 3,4 3
27 3,5 3,125
28 3,6 3,25
29 3,7 3,375
30 3,8 3,5
31 3,9 3,625
32 4 3,75
33 4,1 3,875
34 4,2 4
35 4,3 4,125
36 4,4 4,25
37 4,5 4,375
38 4,6 4,5
39 4,7 4,625
40 4,8 4,75
41 4,9 4,875
42 5 5
Vergelijking Likert scale

So the formula I need is how to get the number 1,4 to a 0,5 (so that the scales are all 0-5)

bebo021999 · Sep 8, 2022

With 1 to 5 scale, step 40 (as sample)
With 1-5 scale from A2
B2:

Code:

=PERCENTILE(INDEX((5-0)/40*(ROW(INDIRECT("1:41"))-1),),PERCENTRANK(INDEX(1+(5-1)/40*(ROW(INDIRECT("1:41"))-1),),A2))

where (5-0)/40 represent "0-5 scale step 40" and where (5-1)/40 represent "1-5 scale step 40"
C2: Concise formula:

Code:

=PERCENTILE(INDEX(0.125*(ROW(INDIRECT("1:41"))-1),),PERCENTRANK(INDEX(1+0.1*(ROW(INDIRECT("1:41"))-1),),A2))

Book1

A

B

C

1

1-5 scale

0-5 scale

0-5 scale (2)

2

1

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

Sheet2

Cell Formulas
Range	Formula
B2:B42	B2	=PERCENTILE(INDEX((5-0)/40(ROW(INDIRECT("1:41"))-1),),PERCENTRANK(INDEX(1+(5-1)/40(ROW(INDIRECT("1:41"))-1),),A2))
C2:C42	C2	=PERCENTILE(INDEX(0.125(ROW(INDIRECT("1:41"))-1),),PERCENTRANK(INDEX(1+0.1(ROW(INDIRECT("1:41"))-1),),A2))
A3:A42	A3	=A2+0.1