This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi,
I've got a list of monetary values (£34,568.12, £51,113.99, etc) and need to split them into 2 groups (which should have totals as close as possible). I could go down the list and manually assign them to a group but to get them as close as possible would be quite labour intensive. Is there an automatic way to do this (for someone who isn't an Excel expert)?
I've already had the idea to rank each figure (highest to lowest) and assigning alternate values into different groups.
I hope this makes sense.
AB
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Hi,
We need some more information to help you further.
You say a list, is that one value per cell or a comma-separated list in a cell?
What do you mean by splitting into 2 groups with close totals? Can you post some sample data and the expected result? It will also help if you explain the logic behind your result.
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
Hi,
I've got a list of monetary values (£34,568.12, £51,113.99, etc) and need to split them into 2 groups (which should have totals as close as possible). I could go down the list and manually assign them to a group but to get them as close as possible would be quite labour intensive. Is there an automatic way to do this (for someone who isn't an Excel expert)?
I've already had the idea to rank each figure (highest to lowest) and assigning alternate values into different groups.
I hope this makes sense.
AB
From a mathematical point of view, the only way to guarantee the best split is to try every possible split and choose the best.
Gordon
Hi,
That is a brain breaker! I could split it up to a difference of 9:
| List of Costs | Small | Large | £465,324.97 | £232,662.49 | ||
|---|---|---|---|---|---|---|
| £34,115.26 | £111.49 | £94,666.23 | £232,671.56 | -£9.08 | ||
| £3,269.01 | £265.49 | £64,509.26 | 1 | |||
| £59,887.44 | £549.66 | £59,887.44 | ||||
| £15,426.59 | £1,006.49 | £49,777.46 | 1 | |||
| £45,067.11 | £3,269.01 | £48,569.23 | ||||
| £5,687.49 | £5,687.49 | £45,067.11 | 1 | |||
| £94,666.23 | £6,649.16 | £34,115.26 | 1 | |||
| £15,764.59 | £15,426.59 | £20,003.01 | ||||
| £20,003.01 | £15,764.59 | £15,764.59 | ||||
| £64,509.26 | £20,003.01 | £15,426.59 | ||||
| £48,569.23 | £34,115.26 | £6,649.16 | ||||
| £265.49 | £45,067.11 | £5,687.49 | ||||
| £111.49 | £48,569.23 | £3,269.01 | ||||
| £6,649.16 | £49,777.46 | £1,006.49 | ||||
| £49,777.46 | £59,887.44 | £549.66 | 1 | |||
| £549.66 | £64,509.26 | £265.49 | 1 | |||
| £1,006.49 | £94,666.23 | £111.49 | 1 |
Used the Small column, but don't see yet how I can put a logic behind. Found so far that you need the 3 highest values to be able to reach the middle point (in this case). The "1" indicates to sum for the nearest value.
Hi JP,
Thanks for the quick response.
The list is a simple list of costs (in £). One value per cell.
My basic aim is to split the list of costs into 2 groups so they can be charged against 2 different purchase orders. The hope is to have the 2 groups as close to equal value as possible.
I've produced a shorted list in the linked Excel doc. I've ranked them in order and then assigned alternate group numbers (1s & 2s). I totalled the 1s & 2s and then checked the difference between them. I then manually swapped some of the 1s & 2s to get the totals as close as possible. I was wondering if there was an automatic way / calculation to do this function without having to use my manual trial & error method.
Thanks,
AB