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

 

[MoshiM]

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

VBA Solution using a stochastic formula. This will create an array with the values you want. You didn't state where you wanted the result to be so I'll leave that to you. Values will be rounded down since I used mod 10. Did you want values to round up or down?

Sub Index_Round_Down()

Dim MY_R() As Variant, MA As Double, T As Long, MI As Double

MY_R = ActiveSheet.Range("M4:M36").Value2

MA = WorksheetFunction.Max(MY_R)
MI = WorksheetFunction.Min(MY_R)

For T = 1 To UBound(MY_R, 1)

    MY_R(T, 1) = (((MY_R(T, 1) - MI) / (MA - MI)) * 100) Mod 10
    
Next T

End Sub

 

[Gowardo]

Hi MoshiM and thanks for your response.

I would like the result to appear in the next column (N).

Unfortunately, I am not too familiar with VBA within Excel and therefore I am skeptical I can get that working properly, unless there's an easy way to ingest the script into the workbook and recall it within cells...?

If not, I wouldn't mind finding a solution using relatively simple logic/math and the max, min functions perhaps, which was a route I was getting some progress on.

Sorry to throw a curve ball, but I know my limitations...!

Thank you again for your time.

Gowardo

 

[MoshiM]

Whoops shouldn't have used mod.

A stochastic formula is (Current-Min of range)/(Max of range-Min of range)
This will return a decimal so you could just divide it by .1 to get a number 1-10 with a decimal attached. If you go for the formula approach, column N properly.

Sub Index_Round_Down()

Dim MY_R() As Variant, MA As Double, T As Long, MI As Double

MY_R = ActiveSheet.Range("M4:M36").Value2

MA = WorksheetFunction.Max(MY_R)
MI = WorksheetFunction.Min(MY_R)

For T = 1 To UBound(MY_R, 1)

    MY_R(T, 1) = clng((((MY_R(T, 1) - MI) / (MA - MI)) / .1)
    if MY_R(T, 1)=0 then MY_R(T, 1)  =1
Next T
ActiveSheet.Range("N4:N36").Value2=MY_R
End Sub

 

[Gowardo]

P.s. Forgot to say, ideally rounded down.

So something like this as final output

[TABLE="width: 500"]
<tbody>[TR]
[TD]10000[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]123123[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]541567[/TD]
[TD]7.4[/TD]
[/TR]
[TR]
[TD]3312[/TD]
[TD]6.5[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1000000[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2131[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]166[/TD]
[TD]4.3[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]3.1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gowardo]

When I try to run the script in VBA I get a syntax error here, but can't figure out why - seems solid to me!

MY_R(T, 1) = clng((((MY_R(T, 1) - MI) / (MA - MI)) / .1)

 

[MoshiM]

When I try to run the script in VBA I get a syntax error here, but can't figure out why - seems solid to me!

MY_R(T, 1) = clng((((MY_R(T, 1) - MI) / (MA - MI)) / .1)

check the code again. I made some edits to the number of parentheses shortly after posting.

Or use this and column formatting to change the number of decimals.
=((M4-MIN($M$4:$M$36))/(MAX($M$4:$M$36)-MIN($M$4:$M$36)))/0.1

 

[Gowardo]

check the code again. I made some edits to the number of parentheses shortly after posting.

Or use this and column formatting to change the number of decimals.
=((M4-MIN($M$4:$M$36))/(MAX($M$4:$M$36)-MIN($M$4:$M$36)))/0.1

Thank you MoshiM, I'll try this and report back!

 

[Peter_SSs]

So something like this as final output

[TABLE="width: 500"]
<tbody>[TR]
[TD]10000[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]123123[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]541567[/TD]
[TD]7.4[/TD]
[/TR]
[TR]
[TD]3312[/TD]
[TD]6.5[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1000000[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2131[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]166[/TD]
[TD]4.3[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]3.1[/TD]
[/TR]
</tbody>[/TABLE]

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

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.

Which is it to be?


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

 

[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?

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