Here's a clarification of what I think the OP is requesting.
As DSCfromCFA noted, the average of the totals is about 6,740, so the perfect solution would rearrange the numbers so that each row totaled 6,740.
The screenshot below shows the in the posted arrangement the first row totals 7397 which varies from 6740 by 657. The sum of all the variances is 10,209 and the maximum variance is the 4th row which totals 1912.
Excel 2016 (Windows) 32 bit |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
2 | | | | | | | Variance | |
---|
3 | 1 | 2 | 3 | 4 | 5 | Total | From Target | |
---|
4 | 685 | 1,713 | 1,477 | 1,637 | 1,885 | 7,397 | 657 | |
---|
5 | 1,282 | 1,998 | 596 | 1,282 | 1,705 | 6,863 | 123 | |
---|
6 | 817 | 2,008 | 1,720 | 1,573 | 1,997 | 8,115 | 1,375 | |
---|
7 | 1,144 | 1,526 | 2,028 | 1,947 | 2,007 | 8,652 | 1,912 | |
---|
8 | 552 | 1,299 | 1,884 | 1,964 | 0 | 5,699 | 1,041 | |
---|
9 | 1,901 | 2,117 | 1,662 | 1,640 | 0 | 7,320 | 580 | |
---|
10 | 1,082 | 1,703 | 1,523 | 1,475 | 0 | 5,783 | 957 | |
---|
11 | 1,785 | 1,979 | 1,071 | 1,020 | 0 | 5,855 | 885 | |
---|
12 | 1,099 | 2,023 | 1,261 | 1,977 | 0 | 6,360 | 380 | |
---|
13 | 1,957 | 1,174 | 952 | 2,021 | 0 | 6,104 | 636 | |
---|
14 | 1,960 | 1,459 | 874 | 2,002 | 0 | 6,295 | 445 | |
---|
15 | 1,976 | 1,618 | 1,533 | 2,068 | 0 | 7,195 | 455 | |
---|
16 | 1,546 | 2,067 | 1,489 | 875 | 0 | 5,977 | 763 | |
---|
17 | | | | | | | 10,209 | Sum |
---|
18 | | | | | Target: | 6,740 | 1,912 | Max |
---|
|
---|
If we exchange the positions of 2007 and 0 in column 5, the new sum of Variances from Target is 8,317 and the new Maximum variance is 1,375.
Excel 2016 (Windows) 32 bit |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
23 | | | | | | | Variance | |
---|
24 | 1 | 2 | 3 | 4 | 5 | Total | From Target | |
---|
25 | 685 | 1,713 | 1,477 | 1,637 | 1,885 | 7,397 | 657 | |
---|
26 | 1,282 | 1,998 | 596 | 1,282 | 1,705 | 6,863 | 123 | |
---|
27 | 817 | 2,008 | 1,720 | 1,573 | 1,997 | 8,115 | 1,375 | |
---|
28 | 1,144 | 1,526 | 2,028 | 1,947 | 0 | 6,645 | 95 | |
---|
29 | 552 | 1,299 | 1,884 | 1,964 | 2,007 | 7,706 | 966 | |
---|
30 | 1,901 | 2,117 | 1,662 | 1,640 | 0 | 7,320 | 580 | |
---|
31 | 1,082 | 1,703 | 1,523 | 1,475 | 0 | 5,783 | 957 | |
---|
32 | 1,785 | 1,979 | 1,071 | 1,020 | 0 | 5,855 | 885 | |
---|
33 | 1,099 | 2,023 | 1,261 | 1,977 | 0 | 6,360 | 380 | |
---|
34 | 1,957 | 1,174 | 952 | 2,021 | 0 | 6,104 | 636 | |
---|
35 | 1,960 | 1,459 | 874 | 2,002 | 0 | 6,295 | 445 | |
---|
36 | 1,976 | 1,618 | 1,533 | 2,068 | 0 | 7,195 | 455 | |
---|
37 | 1,546 | 2,067 | 1,489 | 875 | 0 | 5,977 | 763 | |
---|
38 | | | | | | | 8,317 | Sum |
---|
39 | | | | | Target: | 6,740 | 1,375 | Max |
---|
|
---|
The best solution can be found by brute force by trying all combinations however there are optimization approaches such a "Branch and Bound" that can greatly reduce the amount of combinations that need to be tested.
https://en.wikipedia.org/wiki/Branch_and_bound
@pepsibrandon, please clarify, when you say that you want the totals to be as close as possible to each other, is the goal to minimize the Total of Variance from Target; or to minimize the value of the row with the Max variance?