Transforming a large set of numbers into a 0 to 10 scale

[Gowardo]

Hi all and thank you in advance for reading my thread.

I have a set of numbers in a column M4:M36. The smallest number is 0 and the larget is in the hundreds of million. I would like to create another column to return a value from 0 to 10 where the biggest number in column M would return 10 and the smallest 0.

I have tried this, but not much luck. Returns 10 everywhere.... any advice you can give me would be extremely appreciated!

=INT(CEILING(M4,MAX($M$4:$M$36)/10)*10/MAX($M$4:$M$36))

Thank you,

Gowardo

 

[Peter_SSs]

Either would be suitable for the case at hand, but 0 to 10 preferable. If you have any other options not involving VBA, I'd love to hear them.

Thanks,

Gowardo

I just edited my last post to include an extra question.

 

[Gowardo]

The above example is not consistent with your original request, which was to produce a range from 0 to 10




Which is it to be?


Also, what is the logic of your scale in post #5 - it clearly isn't linear.

Hi Peter,

I'm afraid I don't follow your question, apologies. However, the list of numbers in column M are total contract values for different customers, ranging from 0 to multiple million, formatted as currency with no decimals.

What I'm trying to achieve in column N is a simple scale from 1 to 10, so I can "score" these customers based on the values in column M. Ideally, this list would be between 0 and 10 where 10 is assigned to the biggest number in the list (dynamically) and 0 to the lowest.

Is this the context you were looking for?

Thank you,

Gowardo

 

[Peter_SSs]

What about something like this then?

Excel 2016
	M	N
4	783,931	4.3
5	174,907	3.1
6	1,568,954	5
7	767,780	4
8	1,000,000	4.6
9	135,765	2.8
10	65,722,156	8.1
11	280,000	3.4
12	280,000	3.4
13	83,727,288	9.3
14	64,468,066	7.8
15	50,569,651	6.8
16	40,693	1.5
17	76,932	2.1
18	88,888,888	9.6
19	85	0.3
20	945	0.9
21	71,040,876	8.7
22	562	0.6
23	0	0
24	62,502	1.8
25	97,597	2.5
26	54,023,354	7.5
27	22,614	1.2
28	2,555,520	5.3
29	43,533,753	6.5
30	105,897,451	10
31	72,649,579	9
32	9,162,761	5.6
33	52,970,215	7.1
34	70,306,676	8.4
35	17,321,683	5.9
36	33,563,401	6.2
Scale numbers

Cell Formulas
Range	Formula
N4	=PERCENTRANK(M$4:M$36,M4,2)*10

 

[Gowardo]

What about something like this then?

Excel 2016

	M	N

<tbody>
[TD="align: center"]4[/TD]
[TD="align: right"]783,931[/TD]
[TD="align: right"]4.3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]174,907[/TD]
[TD="align: right"]3.1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1,568,954[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]767,780[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1,000,000[/TD]
[TD="align: right"]4.6[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]135,765[/TD]
[TD="align: right"]2.8[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]65,722,156[/TD]
[TD="align: right"]8.1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]280,000[/TD]
[TD="align: right"]3.4[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]280,000[/TD]
[TD="align: right"]3.4[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]83,727,288[/TD]
[TD="align: right"]9.3[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]64,468,066[/TD]
[TD="align: right"]7.8[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]50,569,651[/TD]
[TD="align: right"]6.8[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]40,693[/TD]
[TD="align: right"]1.5[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]76,932[/TD]
[TD="align: right"]2.1[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]88,888,888[/TD]
[TD="align: right"]9.6[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]0.3[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]945[/TD]
[TD="align: right"]0.9[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]71,040,876[/TD]
[TD="align: right"]8.7[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]562[/TD]
[TD="align: right"]0.6[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]62,502[/TD]
[TD="align: right"]1.8[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]97,597[/TD]
[TD="align: right"]2.5[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]54,023,354[/TD]
[TD="align: right"]7.5[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]22,614[/TD]
[TD="align: right"]1.2[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]2,555,520[/TD]
[TD="align: right"]5.3[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]43,533,753[/TD]
[TD="align: right"]6.5[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]105,897,451[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]72,649,579[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]9,162,761[/TD]
[TD="align: right"]5.6[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]52,970,215[/TD]
[TD="align: right"]7.1[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]70,306,676[/TD]
[TD="align: right"]8.4[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]17,321,683[/TD]
[TD="align: right"]5.9[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]33,563,401[/TD]
[TD="align: right"]6.2[/TD]

</tbody>

Scale numbers​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]N4[/TH]
[TD="align: left"]=PERCENTRANK(M$4:M$36,M4,2)*10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Peter, absolutely spot on!

Thank you, that's exactly what I was looking for. Much, much appreciated. I wasn't familiar with the percentrank formula, useful.

The only other question I would have for you is this:

- Column M actually includes a fair long formula, please see below:

=(SUM(Table3[@[Total License Fee]:[Total Maintenance ]])*$S$17)*([@[Committed ARR]]*$S$16)*VLOOKUP([@CR],$O$16:$P$20,2,0)*VLOOKUP([@Vertical],$O$23:P27,2,0)*VLOOKUP([@Renewal],$R$23:$S$26,2,0)

I would ideally like to now display those 0 to 10 numbers within column M and effectively embed your formula. Would that be feasible do you think?

Can't tell you how grateful I am for your help, many thanks indeed.

Gowardo

 

[Peter_SSs]

Is column M part of a table?
If so, why not leave the existing column with the large formula and hide that column and use the formula I posted in a visible column pointing to the hidden one?
If not, I don't see it as feasible anyway. Again, you could hide the column with the large formula & just show the other one.

 

[Gowardo]

Is column M part of a table?
If so, why not leave the existing column with the large formula and hide that column and use the formula I posted in a visible column pointing to the hidden one?
If not, I don't see it as feasible anyway. Again, you could hide the column with the large formula & just show the other one.

Thank you Peter, that was my thinking. I was hoping to nest the logic in order to have easy access to the overall formula, but not to worry this will be absolutely fine for the use case.

Again, many thanks inded, extremely helpful!

Have a great day,

G