Number clusters

[shabazali1]

Hi Guys,

I have a set of values for which i need to find clusters, i use office 2016 and iv tried a few things using charts but to no avail. what im after is what number levels has strength (ie the bigger the cluster of numbers) the more strength any ideas re how to go about it?, I have attached some numbers so you can get an idea of what i mean although there are 20k rows in total. Cant seem to attach a file to the post. Many Thanks in advance.

1.1508
1.1581
1.1656
1.1682
1.1678
1.162
1.1637
1.1663
1.1672
1.1683
1.166
1.1996
1.1631
1.1699
1.1745
1.1718
1.1663
1.173
1.1738
1.1716
1.1727
1.1738
1.1448
1.1521
1.1596
1.1622
1.1618
1.156
1.1577
1.1603
1.1612
1.1623
1.16
1.15612

[AliGW]

Nope!!!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Special-K]

FYI

Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

You need to explain more clearly.
"The bigger the cluster of numbers the more strength"

Take the whole bloody lot! You cant get a bigger cluster than all of them.
Thats clearly not what you mean, so you need to be more specific.

What constitutes a cluster?
Whats the maximum amount of numbers a cluster can contain?

@AliGW
EDIT: Too late! :-)

[AliGW]

Later, perhaps, but more eloquent!

[shabazali1]

Hi Guys,

First off, than you so much for your reply, im very grateful. hopefully the file is attached and may explain what result im after.

when i say cluster i mean the number levels that are within 0.005 of a level.
for example the following would be a cluster

cluster 1
1.1357 -0.0056 (next level over 0.005 of this level)

cluster 2
1.1413 0
1.1413 -0.002533333
1.143833333 -0.000152667
1.143986 0
1.143986 -0.000814 (this Custer has a count of 5 and the next number elev would take it past 5 so that would be the next cluster.


1.1448


Apologies if i seem dim or confusing. please do let me know if its im unclear

Thanks

[MrShorty]

What kind of clustering algorithm (https://en.wikipedia.org/wiki/Cluster_analysis ) do you want?

If it will help, there's a spreadsheet here (https://www.excelforum.com/excel-pro...ng-tester.html ) that claims to do k-means clustering. If nothing else, it may be a starting place for whatever clustering algorithm you want to use.

[MarvinP]

Hi,

See the attached where I did a Pivot Table and Chart of your data. I grouped your data from the lowest value to the highest using groups of 0.005. Is this what you wanted? Is that what you mean when you say cluster?
Pivot Table Group by and Count.xlsx

[shabazali1]

Hi,

See the attached where I did a Pivot Table and Chart of your data. I grouped your data from the lowest value to the highest using groups of 0.005. Is this what you wanted? Is that what you mean when you say cluster?
Attachment 584632

Hi Mate,

Thank you, thats the kind of thing i was after, but to the 4th decimal point, if that makes sense. Could you kindly the me know the steps you took for the process please.

Many Thanks

[MarvinP]

Hi shabazali,

I'm using 2016 and not sure if you can see the exact same dialog as I see, but you need to see the "Group" dialog. You would change the "By:" field from my 0.005 to any number you want.
See:
https://exceljet.net/blog/how-to-gro...e-by-age-range

If you open my workbook above and right click in Column J (where the rows of the Pivot Table are found) and then on "Group", you should see the correct dialog to make your changes.

[shabazali1]

Hi,

See the attached where I did a Pivot Table and Chart of your data. I grouped your data from the lowest value to the highest using groups of 0.005. Is this what you wanted? Is that what you mean when you say cluster?
Attachment 584632

Got it. Thanks mate.