Distribute a set of values so that all values fall between a tolerance/specfication limit.

[philiphampson1]

I have 3 sets of values in the table below:

Excel Sheet.png


I have a tolerance band of 7-10. So cells B1, B8, B10 and B15 are out of tolerance.

However I can distribute the excess between the other values in the same set.

So for Set 1, I could move 1 value from B1 (11) to B4 (7) to make all those values fall between the tolerance band as below.

Excel Sheet2.png

Is there a formula which can do this for me? I could manually do this but I have a lot more data than just these 3 sets.

[Richard Buttrey]

Hi, and welcome to the forum

This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example, when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

To upload the workbook choose the Go Advanced button and look underneath the posting area for the 'Manage Attachments' option

[philiphampson1]

Hi,

I've uploaded a more detailed example. (see below)

Basically to achieve the "New data (Manually adjusted)" I have re-distributed/balanced the £'s within each Group between the "Personnel names" to bring the number of £'s within my acceptable criteria.

(edit) Column I shows where I have re-distributed the £'s between personnel in each group of 5.

It's pretty simple to do manually.

All I want is a formula to automate this transformation between the original and new data.

[Richard Buttrey]

That's not a workbook. Pictures are rarely much use and few of us will want to recreate your workbook. As requested upload the workbook.

[philiphampson1]

Should be uploaded now.

[FlameRetired]

It appears simple if the values in column I are already there.

It's pretty simple to do manually.

Please describe in a cell by cell narrative how you arrive at those numbers in column I, ie what are the logical steps you take to get those numbers?

At the moment I am not discerning a consistent pattern.

[philiphampson1]

Yes, Column I shows where I've distributed the £'s between the group.

So for Group No. 1 Oliver had excess £'s (£11) so I moved £1 og his to Jack and this brought Oliver's amount to £10 and into the acceptable criteria. Now Jack has £8 which is still acceptable.

Essentially Oliver's excess £1 could have been moved to either Jacob, Noah or Oscar and they still would have an amount within the acceptable critera. The only reason I manually selected Jack is because he had an amount on the lowest end of the acceptable criteria.

Group No. 2 Charlie (£11) and George (£11) had excess £'s so I moved £1 from Charlie to Harry and £1 from George to William. Now everyone in the group has an amount of £'s which falls within the acceptable criteria.

Again I could have moved £1 from Charlie (£11) to Aflie (£9) which would give them both £10's (acceptable) but I manually selected the other two in this case for no reaon other than they had room for an extra £1.

An example where this wouldnt work is - If all 5 people in the group had £11 then the £'s cannot be distributed between them to give them an amount between £7 - £10 so this would be a FAIL/ERROR.


In summary, everyone in their respective groups has to have between £7 - £10. If they have less/more then distribute the £'s so that their amount falls bwtween £7 - £10.

[FlameRetired]

I think I understand.

May I un-merge the Group No. cells?

Merged cells cause havoc for formulas.

While un-merging is the preferred approach an alternative could be helper columns.

[FlameRetired]

I went ahead and un-merged those cells.

Find this monster in column J that replicates column I.

Please Login or Register  to view this content.

Then in column H.

Please Login or Register  to view this content.

This will not work if the values in column C are greater than 11 or less than 6.

Hopefully someone else will be able to come up with a more elegant solution that will even account for larger/smaller outliers.

Edit Please pay no attention to extraneous formulas and text. Those have no function in the above. It's just me probing for other approaches.

[Richard Buttrey]

What about when it's not possible to distribute excess values to other people in the same group without exceeding a limit?

[philiphampson1]

I went ahead and un-merged those cells.

Find this monster in column J that replicates column I.

Please Login or Register  to view this content.

Then in column H.

Please Login or Register  to view this content.

This will not work if the values in column C are greater than 11 or less than 6.

Hopefully someone else will be able to come up with a more elegant solution that will even account for larger/smaller outliers.

Edit Please pay no attention to extraneous formulas and text. Those have no function in the above. It's just me probing for other approaches.

Thanks, this works great at the moment.

As you say, if anyone could figure it out for larger/smaller outliers it'd be perfect.

[philiphampson1]

What about when it's not possible to distribute excess values to other people in the same group without exceeding a limit?

In this instance a FAIL/ERROR would be displayed. Which is fine.

[philiphampson1]

I've inputted some more data where this formula doesn't seem to work.

Column J shows some of the £'s have been unevenly distributed in Groups 2 & 3.

In Group 1 not enough £'s have been distributed to make Oscar's value "acceptable".