Excel convert data to a range from 0-10

[mikhrab]

I have a column represents a set of numbers. Lowest number is 0, highest is 195. What I am trying to do is create a scale of 1-10, where the largest number in the series represents a 10, and the lowest number equals 1.

I am hoping anyone of you can help me create a formula that will automatically scale this large string of numbers into a 1-10 scale.

Thanks in advance for helping me out here!

 

[joeu2004]

There are many ways to map one range into another. The following demonstrates how to do a linear mapping.

Book1
	A	B
1	0	1.0000
2	29	2.3385
3	124	6.7231
4	85	4.9231
5	195	10.0000
Sheet1

The formula in column B is of the form (in B1):

=FORECAST(A1, {1,10}, {0,195})

Caveat: The syntax in your region might differ.

 

[Rick Rothstein]

You can also use this formula...

=1+9*A1/195

where the 9 is derived by subtracting the low end (1) from the upper end (10). This formula can be simplified slightly like so...

=1+3*A1/65

 

[Peter_SSs]

Welcome to the MrExcel board!

Perhaps one of these?

20 03 04.xlsm
	A	B	C	D
1	148	7.33	7	7.58974
2	185	8	8	9.48718
3	0	0	0	0
4	117	5.33	5	6
5	89	3.33	3	4.5641
6	48.75	2	2	2.5
7	194	9.33	9	9.94872
8	193	8.66	9	9.89744
9	97.5	4.66	5	5
10	195	10	10	10
11	132	6	6	6.76923
12	96	4	4	4.92308
13	17	0.66	1	0.87179
14	38	1.33	1	1.94872
15	145	6.66	7	7.4359
16	84	2.66	3	4.30769
Scale

Cell Formulas
Range	Formula
B1:B16	B1	=10*PERCENTRANK.INC(A$1:A$16,A1)
C1:C16	C1	=ROUND(10*PERCENTRANK.INC(A$1:A$16,A1),0)
D1:D16	D1	=10*A1/195

 

[mikhrab]

You can also use this formula...

=1+9*A1/195

where the 9 is derived by subtracting the low end (1) from the upper end (10). This formula can be simplified slightly like so...

=1+3*A1/65

Thank you so much for your reply. I'm curious to know why you add 1 to 9?

 

[mikhrab]

Welcome to the MrExcel board!

Thank you Peter for you reply.

This is the formula that used. Do you think this would work? I used max function because values change every year.

 

[Peter_SSs]

This is the formula that used. Do you think this would work?

It will work provided your list always has zero as its lowest number or you want your list treated as if zero is the lowest number as shown in column B below.

However, if you want your results to always stretch from 0 to 10 and zero may not be the lowest number, then look at column C

20 03 04.xlsm
	A	B	C
2	85	4	0
3	95	5	1
4	105	5	2
5	115	6	3
6	125	6	4
7	135	7	5
8	145	7	5
9	155	8	6
10	165	8	7
11	175	9	8
12	185	9	9
13	195	10	10
14	185	9	9
15	175	9	8
16	165	8	7
17	155	8	6
18	145	7	5
Scale (2)

Cell Formulas
Range	Formula
B2:B18	B2	=10*A2/MAX(A$2:A$18)
C2:C18	C2	=10*(A2-MIN(A$2:A$18))/(MAX(A$2:A$18)-MIN(A$2:A$18))

 

[mikhrab]

It will work provided your list always has zero as its lowest number or you want your list treated as if zero is the lowest number as shown in column B below.

However, if you want your results to always stretch from 0 to 10 and zero may not be the lowest number, then look at column C

20 03 04.xlsm
	A	B	C
2	85	4	0
3	95	5	1
4	105	5	2
5	115	6	3
6	125	6	4
7	135	7	5
8	145	7	5
9	155	8	6
10	165	8	7
11	175	9	8
12	185	9	9
13	195	10	10
14	185	9	9
15	175	9	8
16	165	8	7
17	155	8	6
18	145	7	5
Scale (2)

Cell Formulas
Range	Formula
B2:B18	B2	=10*A2/MAX(A$2:A$18)
C2:C18	C2	=10*(A2-MIN(A$2:A$18))/(MAX(A$2:A$18)-MIN(A$2:A$18))

This is very helpful! Thanks a lot Peter !!

 

[Rick Rothstein]

I'm curious to know why you add 1 to 9?

Because you said your range was 1 to 10 in your original post. If the range is actually 0 to 10 (as your later posts seem to suggest), then my formula would become this instead...

=10*A1/195

 

[Peter_SSs]

This is very helpful! Thanks a lot Peter !!

You're welcome.