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

Gowardo

New Member
Joined
Jul 24, 2019
Messages
9
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

Active Member
Joined
Jan 31, 2018
Messages
290
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?
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
Joined
Jul 24, 2019
Messages
9
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
Joined
Jan 31, 2018
Messages
290
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
Joined
Jul 24, 2019
Messages
9
P.s. Forgot to say, ideally rounded down.

So something like this as final output

100009
1231238
5415677.4
33126.5
01
100000010
21315
222
1664.3
1113.1

<tbody>
</tbody>
 

Gowardo

New Member
Joined
Jul 24, 2019
Messages
9
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
Joined
Jan 31, 2018
Messages
290
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
Joined
Jul 24, 2019
Messages
9
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
Joined
May 28, 2005
Messages
42,247
Office Version
365
Platform
Windows
So something like this as final output

100009
1231238
5415677.4
33126.5
01
100000010
21315
222
1664.3
1113.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
Joined
Jul 24, 2019
Messages
9
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top