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!

 

[Andu]

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

Hello! I am using this formula right now to mark the lowest number with 1 and highest with 10: =1-9*(A1-MIN(A$1))/(MAX(A$2)-MIN(A$1)) but can you help me with finding a formula that does the opposite also? Meaning the highest number will be 1 and the lowest 10.

 

[Rick Rothstein]

If you formula outputs 1, 2, 3,.., 9, 10, then to get the reverse try subtracting what you have from 11...

=11-(1-9*(A1-MIN(A$1))/(MAX(A$2)-MIN(A$1)))

which simplifies to this...

=10+9*(A1-MIN(A$1))/(MAX(A$2)-MIN(A$1))

 

[Andu]

If you formula outputs 1, 2, 3,.., 9, 10, then to get the reverse try subtracting what you have from 11...

=11-(1-9*(A1-MIN(A$1))/(MAX(A$2)-MIN(A$1)))

which simplifies to this...

=10+9*(A1-MIN(A$1))/(MAX(A$2)-MIN(A$1))

It now gives the smallest number value of 10 and the highest value of 19 instead of 1

 

[Rick Rothstein]

Does this formula (which you posted in Message #1) with the minus sign as the second character...

=1-9*(A1-MIN(A$1))/(MAX(A$2)-MIN(A$1))

actually output the numbers 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 for you?

 

[Andu]

Does this formula (which you posted in Message #1) with the minus sign as the second character...

=1-9*(A1-MIN(A$1))/(MAX(A$2)-MIN(A$1))

actually output the numbers 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 for you?

Yes

 

[Rick Rothstein]

What is 11 minus each of those numbers? Seems to me it should be the series you asked for. Did you use the formula I posted in Message #12? Or did you just put 11- in front of your existing formula? If the latter, that is the problem because you forgot to protect the minus sign in front of the 9 by enclosing your original formula in parentheses.