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

#### Gowardo

##### New Member

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

##### Active Member

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?
Code:
``````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

##### New Member
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

##### Active Member
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.
Code:
``````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``````

Last edited:

#### Gowardo

##### New Member
P.s. Forgot to say, ideally rounded down.

So something like this as final output

 10000 9 123123 8 541567 7.4 3312 6.5 0 1 1000000 10 2131 5 22 2 166 4.3 111 3.1

<tbody>
</tbody>

#### Gowardo

##### New Member
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

##### Active Member
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

##### New Member
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

##### MrExcel MVP, Moderator
So something like this as final output

 10000 9 123123 8 541567 7.4 3312 6.5 0 1 1000000 10 2131 5 22 2 166 4.3 111 3.1

<tbody>
</tbody>
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.

Last edited:

#### Gowardo

##### New Member
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

1,082,153
Messages
5,363,459
Members
400,737
Latest member
urhen22

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...