Formula to calculate how much to add or subtract to keep totals even

[MrHoyt]

Say I am tasked with either adding or taking away oranges from three different bins, depending on the day. Each bin can only hold up to 100 oranges, and the idea is to try to keep all three bins with equal amounts of oranges after I am done, or as close as possible. (can use fractions of oranges to keep things easier)

The problem is right now each bin is out of sorts:

A1- Where I would enter the total amount of oranges I need to add that day. Lets say I need to add 100 oranges today.
A2- Where I would enter the total amount of oranges that I need to remove that day. Lets say I do not need to remove any oranges today.

C1- Bin 1 currently has 30 oranges. D1 shows in percentage how full Bin 1 is, which is 30%
C2- Bin 2 currently has 50 oranges. D2 shows in percentage how full Bin 2 is, which is 50%
C3- Bin 3 currently has 80 oranges. D3 shows in percentage how full Bin 3 is, which is 80%

Is there a formula I could use that would tell me how much to distribute to get the bins balanced out?
(My math shows me that I need to add 56.666 to Bin 1, 36.666 to Bin 2 and 6.666 to Bin 3)

 

[myall_blues]

Try this.
In short. you need to get the total current number of oranges (100 to add minus zero to remove plus the total of all the bins) = 260
Divide that by the number of bins (3) = 86.67 to be in each bin after distribution.
Subtract the number in each bin from 86.67 to find the number to add/remove to each bin (it may be negative, indicating a removal).

Book1
	A	B	C	D	E	F
1						Add/remove
2	To add	100		Bin 1	30	56.66667
3	To remove	0		Bin 2	50	36.66667
4				Bin 3	80	6.666667
5
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=($B$2-$B$3+SUM($E$2:$E$4))/COUNT($E$2:$E$4)-$E2

 

[MrHoyt]

Myall_blues, thank you for your help with this. I forgot to mention that I am not able to remove the oranges from the fullest bin(s) to help balance things out. What you have provided would help but the times it wants to remove from the fullest bin(s) to make the bins 100% balanced makes this not a perfect solution.

I was trying to use the MIN, MEDIAN, and MAX functions to determine how to allocate the oranges in the scenario I described for 100 oranges being added.
Bin 3(Max) - Bin 1(Min) = Add 50 oranges to Bin 1.
Bin 3(Max) - Bin 2(Median)= add 30 oranges to Bin 2.
All three bins are now at 80. I have 20 oranges left over to evenly distribute to all three bins, which makes the end total 86.6667 per bin.

There will be times I won't be able to get all three bins perfectly balanced, but I'm thinking this way or something like it has me moving in the right direction?

 

[myall_blues]

I am not able to remove the oranges from the fullest bin(s) to help balance things out.

What rule defines bins in the plural situation here? One could argue in the simple three bin situation described that both the bin with 80 and the bin with 50 are the fullest bins (plural). Or is this a multi-line production scenario where each line has a singular ’fullest bin’?

 

[MrHoyt]

Instead of bins think of the three storage locations as warehouses. Typically marketing is either buying oranges to put into these "warehouses" to sell at a later date, or selling oranges which requires us to move them out of storage.

It's best practice to be diligent to keep inventory equal across all three warehouses so as not to need the calculation I am looking for. But you have the human element involved in this storage process. Or for business reasons it might make more sense to only put oranges into two of the warehouses with the idea to "square up" later. Also while it is possible to pull oranges from one warehouse to put into the other to balance out their inventory, it is almost never used because there is a cost associated with doing this.

About every week or so there is a new protocol management sends out for us to follow to try to balance out the warehouses. I was hoping to have something to use on a daily basis.

 

[myall_blues]

Ok thanks

Is VBA solution an option for you?

 

[MrHoyt]

Yes, VBA is an option. Thank you!

 

[MrHoyt]

I think I found a solution using an IF statement in a helper column that I can hide, and a very lengthy, multiple IF statement in the Add/Remove column It's not pretty but from I can see it works.

=IF(E2=MAX($E$2:$E$4),0,MAX($E$2:$E$4-E2))

Basically the Helper column shows the difference between the Max bin and the other bins. The Helper column is then used to help with calculations in the Add/Remove column.


=IF(IF($B$2<SUM($F$2:$F$4),(($B$2-SUM($F$2:$F$4))/2)+F2,(($B$2-SUM($F$2:$F$4))/3)+F2)<0,0,IF($B$2<SUM($F$2:$F$4),(($B$2-SUM($F$2:$F$4))/2)+F2,(($B$2-SUM($F$2:$F$4))/3)+F2))

If To Add is less than the SUM of the Helper column, then subtract To Add from the SUM of the Helper column and divide this by 2 and add then add the Helper amount
If To Add is not less than the SUM of the Helper column, then subtract To Add from the SUM of the Helper column and divide this by 3 and add then add the Helper amount
If this results in a negative number then this results in a 0 in the Add/Remove Column
If this does not result in a negative number than the IF statement basically repeats itself.

What do you think? Also, I only worked with the To Add, would have to make some small tweaks for the To Remove part. Or utilize VBA to not have such a long IF statement?