Evenly Distribute Groups of Numbers?

**BigBadBoy** · 01-06-2011, 11:29 AM

I would like to evenly distribute groups of values (such that each group has the closest possible average value +/- standard deviation).

Hypothetical Example: heart rate in 12 patients = 110, 105, 140, 117, 119, 90, 100, 122, 148, 102, 99, 130. I would like to divide the patients into 3 groups of 4 people/group and want to ensure that the average group values are similar across the 3 groups based on average heart rate plus/minus standard deviation.

In reality, I will be dealing with 50-100 values and will need flexibility as to the number of groups (e.g. 85 patients into 6 groups or 52 patients into 3 groups, etc).

This could be done by hand but maybe there is an easier Excel solution? Thanks all!

**shg** · 01-06-2011, 11:46 AM

Simplistically, you could sort the data, and then choose groups by slicing from the top and bottom.

E.g., for 3 groups from 12 people,

1, 12, 2, 11
3, 10, 4, 9
5, 6, 7, 8

For larger populations, you could assign people randomly.

**BigBadBoy** · 01-07-2011, 08:38 AM

Thanks...but that isn't really the solution I was looking for. I realize I can do this by hand...I'm looking for something automated to deal with a large number of values to minimize differences in group averages and standard deviation...a slice form the top and bottom won't ensure that happens.

**shg** · 01-07-2011, 12:15 PM

Okay ...

Suppose you have N*M people having mean and deviation {A,S}, to be divided into N groups of M people each.

Find the M-subset of N that gives {a,s} closest to {A,S}, make that a group, and remove them from the list.

Repeat until done.

**BigBadBoy** · 01-07-2011, 09:18 PM

I see where you're going, but this still doesn't answer the question of finding an automated method within Excel; I believe what I need to do first is find a mathematical solution and then use different tools within Excel to implement quickly. My thought is that I likely need to post in a different section. That said, thanks for considering this question.

**shg** · 01-08-2011, 12:59 AM

I did suggest an algorithm.

You can suggest your own algorithm, and someone can help you implement it.

**Stephy212** · 01-14-2014, 04:49 PM

I do not know if you have figured this out or not, but I am looking to do something very similar. If you have any tips that would be great!
Thanks!
Steph

**shg** · 01-14-2014, 05:15 PM

Got an algorithm?

**Stephy212** · 01-14-2014, 05:27 PM

Ha! No! I might kick butt at some random excel stuff, but unfortunately I am not that amazing

I am pretty good at figuring things out by using out of the box ideas, but I don't know how to write out an algorithm

**shg** · 01-14-2014, 05:28 PM

What's the purpose of it?

**Stephy212** · 01-14-2014, 05:52 PM

Ok so I have a list of account balances to send to collections. We use 3 different vendors, we need to split the accounts into 3 groups (with the exact same about of records) but the total of all the balances need to be around the same. I have tried sorting the data by every which way (acct ID, Name-first and last) but when I divide the records into their groups the grand total is still over 10k of a difference between the three.

I did figure it out manually by using a countif formula and then pivot table and just switching a couple vendors around on individual accounts until the numbers were less that 1k different.

I know that all probably makes no sense!

The best way I can describe it is like this...I am trying to sort a group of values by their average, so record 1-100 is not less or more than 100-200 when totaled.

**shg** · 01-14-2014, 06:33 PM

Post an example with sensitive data removed?

**shg** · 01-14-2014, 08:29 PM

Simple way:

	A	B	C	D	E	F	G
1				Group1	Group2	Group3
2	Name	Value	Group	0	0	0
3	Name79	142	1	142	0	0	C3 and down: =MATCH(MIN(D2:F2), D2:F2, 0)
4	Name47	134	2	142	134	0	D3 and down and across: =D2 + ($C3=COLUMNS($C3:C3)-1) * $B3
5	Name68	133	3	142	134	133
6	Name16	130	3	142	134	263
7	Name38	126	2	142	260	263
8	Name89	123	1	265	260	263
9	Name87	123	2	265	383	263
10	Name73	123	3	265	383	386
11	Name84	113	1	378	383	386
12	Name27	112	1	490	383	386
13	Name58	111	2	490	494	386
14	Name03	110	3	490	494	496
15	Name94	108	1	598	494	496
16	Name90	106	2	598	600	496
17	Name01	102	3	598	600	598
18	Name30	101	1	699	600	598
19	Name61	100	3	699	600	698
20	Name43	100	2	699	700	698
21	Name65	99	3	699	700	797
22	Name53	98	1	797	700	797
23	Name99	97	2	797	797	797
24	Name60	95	1	892	797	797
25	Name72	93	2	892	890	797
26	Name29	93	3	892	890	890
27	Name12	92	2	892	982	890
28	Name75	91	3	892	982	981
29	Name40	91	1	983	982	981
30	Name15	91	3	983	982	1,072
31	Name77	90	2	983	1,072	1,072
32	Name46	90	1	1,073	1,072	1,072
33	Name66	89	2	1,073	1,161	1,072
34	Name64	89	3	1,073	1,161	1,161
35	Name02	89	1	1,162	1,161	1,161
36	Name34	88	2	1,162	1,249	1,161
37	Name09	88	3	1,162	1,249	1,249
38	Name07	88	1	1,250	1,249	1,249
39	Name59	87	2	1,250	1,336	1,249
40	Name26	87	3	1,250	1,336	1,336
41	Name22	86	1	1,336	1,336	1,336
42	Name57	85	1	1,421	1,336	1,336
43	Name85	84	2	1,421	1,420	1,336
44	Name31	84	3	1,421	1,420	1,420
45	Name04	83	2	1,421	1,503	1,420
46	Name76	82	3	1,421	1,503	1,502
47	Name54	82	1	1,503	1,503	1,502
48	Name20	73	3	1,503	1,503	1,575
49	Name74	71	1	1,574	1,503	1,575
50	Name39	71	2	1,574	1,574	1,575
51	Name42	70	1	1,644	1,574	1,575
52	Name36	70	2	1,644	1,644	1,575
53	Name21	53	3	1,644	1,644	1,628

Evenly Distribute Groups of Numbers?

LinkBack

Thread Tools

Rate This Thread

Display

Evenly Distribute Groups of Numbers?

Re: Evenly Distribute Groups of Numbers?

Re: Evenly Distribute Groups of Numbers?

Re: Evenly Distribute Groups of Numbers?

Re: Evenly Distribute Groups of Numbers?

Re: Evenly Distribute Groups of Numbers?

Re: Evenly Distribute Groups of Numbers?

Re: Evenly Distribute Groups of Numbers?

Re: Evenly Distribute Groups of Numbers?

Re: Evenly Distribute Groups of Numbers?

Re: Evenly Distribute Groups of Numbers?

Re: Evenly Distribute Groups of Numbers?

Re: Evenly Distribute Groups of Numbers?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions