Distribute a number from a cell across others based on the percentage of another cell

I have a total of 100 oranges available to 4 teams. I want them to pick 50 aggregately.
Each team only has a percentage of the 100 available to them (Team 1 30%, Team 2 20%, Team 3 40%, Team 4 10%).
I want to assign each team a "fairshare" of that 50 based on the percentage of the 100 they have.

The total MUST be equal to 50. And must be a whole number.

Is this possible with a formula?

In real life, the oranges are people and there are a total of 28 teams.

I started with this

Please Login or Register  to view this content.

to assign each team their respective share but, doesn't work for all the different "fruit". For Oranges it works but, as the total fruit available changes and the percentage each team has available to them changes, the total can be over or under the aggregate amount of fruit to pick as you can see in this sample with "Apples", the total fruit assigned, doesn't equal the aggregate in column C.

I've updated the attachment to have the actual current formulas I'm using. Oranges the current formula works out perfectly, Apples you will see the issue in column H.

Tricky since you're working only with integer values so you can't guarantee that the split will work out. Your second row is asking to split 11 in ratio 7:2:7:7. Which answer would you prefer? 3,2,3,3 or 3,1,3,4? The second option could be achieved by changing the formula in column G:

Please Login or Register  to view this content.

The first option would be trickier to implement - I'd need to put on my thinking cap for that one!

WBD

OK. I had to use a helper column in J and also use the computed rankings. It's a little complex but I think it works; at least for your data that you have!

See attached.

WBD

Totally OK with a helper column, I'll admit I got a bit of a headache trying to figure out exactly what it was doing but, I copied it.

Everything was looking promising as the first cell I pasted the updated formula into was dead on, then as I applied it down the list and across each of the teams, the formula started throwing out negative numbers (which I totally forgot to mention cannot be the case). For my application, there will be times where a Team will have no "fruit" available, and if that is the case they'd be assigned 0.

The formula you posted worked beautifully when I applied it to the small sample though, and I got super excited for a second :D

These calculations would be done one time a year, so I don't know if it's something I should post in the Macro/VBA section? Although, I'm very intrigued by the fact your formula worked perfect in the small sample.

We might be in a better position to help if we could see a sample that shows where the formula produced negative numbers.
That said I took a different approach:
1. D10:G10 are populated using a pseudo banker's rounding formula: =IF(ISODD(D5),ROUNDUP(D4/SUM($D4:$G4),2),ROUNDDOWN(D4/SUM($D4:$G4),2))
2. D2:G3 are populated using: =MAX(0,$C2*D$10)
Let us know if you have any questions.

Originally Posted by WideBoyDixon

OK. I had to use a helper column in J and also use the computed rankings. It's a little complex but I think it works; at least for your data that you have!

See attached.

WBD

Originally Posted by JeteMc

We might be in a better position to help if we could see a sample that shows where the formula produced negative numbers.
That said I took a different approach:
1. D10:G10 are populated using a pseudo banker's rounding formula: =IF(ISODD(D5),ROUNDUP(D4/SUM($D4:$G4),2),ROUNDDOWN(D4/SUM($D4:$G4),2))
2. D2:G3 are populated using: =MAX(0,$C2*D$10)
Let us know if you have any questions.

Thank you both again for the assist, it has been doing my head in for the better part of a week...

I've massively redacted/sanitized my actual working document and attached it. I've added tabs for my original, then the WBD tab, and JETEMC's. All my original formulas are still present.
Basically, the only time I want the "rank" to come into play is if column AE is not exactly equal to column C.

You can now see where WBD's is giving me negative numbers.

On my original you will see it is like a 99% solution. It counts the total of "fruit 1" that "team 1" has available, then divides that by the total amount of "fruit 1" available to all teams, and multiplies it by the "quota".

Teams may have a massive amount of "fruit 1" available, and none of "fruit 2" so, I cannot tell that team to pick anything of something they don't have...

Feel free to tell me to get bent I've searched for how to proportionally distribute for the last 2 weeks and I cannot get anywhere on what I've found... They all seem to work for the samples provided (just as each of yours have) but when I apply the same logic to my situation, the numbers get thrown off.

There are some real genius contributors on this site, and I hope one of them will give you an elegant solution.
Perhaps my proposal, modeled on the FRUIT ASGN (TACHYONSTIGGY) sheet, will help until then.
1. I added a column on the Fruit Avail sheet that displays the team number using: =MID(D2,6,3)+0
2. I put the following into AF2:AG10 to show the next teams that could pick a particular fruit:

Formula:

Please Login or Register  to view this content.

3. I put the following into AH2:AH10 to show the team assigned a fruit that is being over picked: =IF($C2< $AE2,"TEAM "&AGGREGATE(14,6,MID($D$1:$AD$1,6,3)/($D2:$AD2>0),1),"")
4. I populated a new pick range in AI2:BI10 using: =SUM(D2,IF(OR($AF2=AI$1,$AG2=AI$1),1),IF($AH2=AI$1,-1))
Note that the columns could be added to AF2:AG10 or AH2:AH10 if you find the values in column AE are off by more than one or two.
Let us know if you have any questions.

Good catch. Column E has been removed and the formula for columns AF:AG has been modified to read:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

EDIT: I figured out how to duplicate it (leaving the below as a reference for myself later), the issue was in the "under assigned" columns, you used the reference as the columns... For the "over assigned" you just had it as a "1" like below

Please Login or Register  to view this content.

once I added the columns to that reference/k It stopped duplicating it...

Please Login or Register  to view this content.

I have marked this as solved however, I may have another issue... Have to run to an appointment. Again, if I could add more rep to you boss, I would.

Originally Posted by JeteMc

3. I put the following into AH2:AH10 to show the team assigned a fruit that is being over picked: =IF($C2< $AE2,"TEAM "&AGGREGATE(14,6,MID($D$1:$AD$1,6,3)/($D2:$AD2>0),1),"")

I am about 99% finished. The biggest issue I have now is, I cannot figure out how to duplicate

Please Login or Register  to view this content.

that is in column AH. I was able to duplicate AF:AG with no issues but, when I applied the same logic to AH I get the same team listed in each of the new cells... Example, if AE is 3 higher than C, then columns AH (and then AI and AJ) all return the same Team. I tried editing the formula to

Please Login or Register  to view this content.

but, I get the same result. (Note: Where the headers show D2:AD2 it is because on my workbook I had to actually add under a new Row under Row 1 that identifies each Team as "Team #", the actual Teams have individual names that don't follow any logic) :D

I believe that the following will do what you want:

Formula:

Please Login or Register  to view this content.

If not please upload a sample that shows where the formula isn't working.

In the case of fruits displayed on the RED POP % sheet all teams that have those fruits available have been assigned an amount, which is a different situation from earlier examples where some teams that had a particular fruit available had not yet been assigned an amount.
From an Excel standpoint a second IFERROR can be added that will add the difference between the values in columns C and BH to one of the teams which already has a value.
So this becomes a non-Excel decision as to which of the teams should have that value added. As in the case of fruit 40 should the one extra unit be assigned to the team which is picking the most (1) or the team which is picking the least (11) or to one of the other teams. We will need to know the rational for the selection so that we can write a corresponding value_if_error argument.
Let us know if you have any questions.

Team #1 has the highest aggregate of all fruits, yet when the Red Pop % is filtered by fruit and team, team 1 is not assigned fruit 72.
The highest ranked team that is assigned fruit 72 appears to be team22.
So, I am going to proceed on the assumption that you want the additional value added to the highest overall ranked team that has a particular fruit assigned.
The formula for BJ26 is:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Very Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

The second formula starting with cell BJ3 and copied across and down would be:

Formula:

Please Login or Register  to view this content.

Note that when I test using the file attached to post #18 and clearing the filter on column C, it causes problems in several rows including 6, 7, 15 etc.
The first formula starting with cell BJ3 and copied across and down would be:

Formula:

Please Login or Register  to view this content.

The first formula doesn't produce any problems when tested.
Let us know if you have any questions.

If I understand then you want to add the first additional value to the highest overall ranked team that has a particular fruit assigned and then if more values need adding they should be added to teams that have fruit available but not yet assigned.
If that is accurate then the following will should work:
For column BJ:

Formula:

Please Login or Register  to view this content.

For columns BK:BM

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

I still don't feel that I have answered your last question.
Here is a formula that will check all previously assigned teams and then start adding values to teams that have fruit available but have not yet been assigned:

Formula:

Please Login or Register  to view this content.

Hopefully one of these works for your purposes.
Let us know if you have any questions.

The aggregate formula was using the ranks in row 151 to arrange the teams, however it seems I didn't understand that "highest" meant the largest number in row 151. I took it to mean that the team ranked 1st should get the first extra value.
According to row 151 Team 1, Team 8, Team 4, Team 5 are the 1st, 2nd, 3rd and 4th ranked teams, while Team 9, Team 3, Team 6, Team 8 are the 9th, 7th, 5th and 2nd ranked teams.
Interestingly the SMALL IF formula skips team 7 which is the 8th ranked team and team 2 which is the 6th ranked team.
Modifying the AGGREGATE formula as follows shows TEAM 9, TEAM 7, TEAM 3 and TEAM 2:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.