How to assign numbers in sequence for a cluster

**ysr** · 06-05-2013, 03:36 PM

Hi, below is my problem:

I want to assign numbers in sequence to group with atleast 25 members in it.

Group1 Cluster number Explanation:
15 1 (15 is less than 25. So, I want to add to next group number i.e 5 . After adding
5 1 to next group it is 15+5 = 20 , which is still less than 25. So, I will also add the
5 1 3rd group number . Now it is 15+5+5=25 and I want to give it cluster number 1)
27 2 -This has 27 members in group1, so I want to give it cluster2
30 3 -Same reason as above
12 4 (-This has 12 members, which is less than 25, so will add to next member which is 15.)
15 4 So, 12+15 = 27 which is more than 25. Hence 12 and 15 should be under one cluster i.e cluster 4 . As of now, I just have group 1 values. How do I assign the cluster numbers in excel using a function?

Please help

**shg** · 06-05-2013, 04:22 PM

Please Login or Register  to view this content.

**ysr** · 06-05-2013, 04:44 PM

Thanks very much !!!

**shg** · 06-05-2013, 04:46 PM

You're welcome.

**ysr** · 06-06-2013, 02:46 PM

Hello shg, your formula works great but I am not getting the logic at all.

SUMIF( range, criteria, [sum_range] ) >=25

I understand the standard SUMIF syntax but I don't understand at all how your formula is working
Can you please breakdown in layman terms and help me understand the logic you used.

Thank you very much for your time !

**shg** · 06-07-2013, 02:27 PM

The cluster number is the same as the prior number unless the prior cluster already has >=25 members.

The expression (SUMIF(B$1:B1,B1,A$1:A1) >= 25) returns True or False. When it becomes part of an arithmetic expression, True is coerced to 1 and False to 0, so it either adds one or doesn't.