Apportioning values but keep in minimum value in formula

booms

Board Regular
Joined
Dec 2, 2010
Messages
60
Hi all,

This might be as much a maths question as an excel question.

I’m trying to apportion a value between twenty different descriptors. This is linearly apportioned based on another numeric feature and will add up to the original value, example below.

However, to make things complicated, I want to ensure that none of the apportioned values fall below a minimum, whilst still making sure that all twenty will add up to the original value.

Is there anyway I can do this in formula rather than in VBA?

For example apportioning linearly below, 'D', ends up with 106k. Can I set a minimum value, of say, 150k, that would then automatically adjust all the other values to linearly apportion the remainder whilst still adding up to 10m?

[TABLE="width: 302"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Total to apportion[/TD]
[TD="align: right"]10,000,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Numeric feature[/TD]
[TD]Apportioned value[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]0.901310638[/TD]
[TD="align: right"]805,843.83[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]0.803327803[/TD]
[TD="align: right"]718,239.33[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]0.302376282[/TD]
[TD="align: right"]270,348.59[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]0.118878557[/TD]
[TD="align: right"]106,286.94[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]0.940530667[/TD]
[TD="align: right"]840,909.67[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]0.241567195[/TD]
[TD="align: right"]215,980.40[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]0.81744142[/TD]
[TD="align: right"]730,858.04[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]0.521753703[/TD]
[TD="align: right"]466,489.56[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD="align: right"]0.337175248[/TD]
[TD="align: right"]301,461.65[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD="align: right"]0.612220382[/TD]
[TD="align: right"]547,374.01[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD="align: right"]0.781189611[/TD]
[TD="align: right"]698,446.02[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD="align: right"]0.772540213[/TD]
[TD="align: right"]690,712.77[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD="align: right"]0.19931728[/TD]
[TD="align: right"]178,205.60[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD="align: right"]0.844274884[/TD]
[TD="align: right"]754,849.30[/TD]
[/TR]
[TR]
[TD]O[/TD]
[TD="align: right"]0.271566755[/TD]
[TD="align: right"]242,802.41[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]0.556527301[/TD]
[TD="align: right"]497,579.94[/TD]
[/TR]
[TR]
[TD]Q[/TD]
[TD="align: right"]0.539259607[/TD]
[TD="align: right"]482,141.24[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD="align: right"]0.765787615[/TD]
[TD="align: right"]684,675.40[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD="align: right"]0.364686522[/TD]
[TD="align: right"]326,058.93[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD="align: right"]0.492949588[/TD]
[TD="align: right"]440,736.38[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD] [/TD]
[TD="align: right"]10,000,000.00[/TD]
[/TR]
</tbody>[/TABLE]

Thanks
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
This can be tricky. By forcing some values to a given value, all the others are slightly shifted as well. Then you have to adjust those, and it becomes an iterative process. You can set something up with the Solver, or with iterative calculations, but both of those are awkward.

Another approach is to figure out which numeric features fall short of the threshold and assign them the minimum value. Then the rest of the total can be apportioned to the rest of the values. That would look like this:

ABC
24

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Total to apportion[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10,000,000.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Min value[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]150,000.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Min numeric feature[/TD]
[TD="align: right"]0.167770219[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Numeric feature[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Apportioned value[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.901310638[/TD]
[TD="align: right"]802283.3963[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.803327803[/TD]
[TD="align: right"]715065.9617[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.302376282[/TD]
[TD="align: right"]269154.1187[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.118878557[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.940530667[/TD]
[TD="align: right"]837194.3102[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.241567195[/TD]
[TD="align: right"]215026.1425[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.81744142[/TD]
[TD="align: right"]727628.9118[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.521753703[/TD]
[TD="align: right"]464428.4836[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.337175248[/TD]
[TD="align: right"]300129.713[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.612220382[/TD]
[TD="align: right"]544955.5643[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.781189611[/TD]
[TD="align: right"]695360.0988[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.772540213[/TD]
[TD="align: right"]687661.0125[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.19931728[/TD]
[TD="align: right"]177418.237[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.844274884[/TD]
[TD="align: right"]751514.1759[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.271566755[/TD]
[TD="align: right"]241729.6427[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.556527301[/TD]
[TD="align: right"]495381.4971[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.539259607[/TD]
[TD="align: right"]480011.0093[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.765787615[/TD]
[TD="align: right"]681650.3242[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.364686522[/TD]
[TD="align: right"]324618.316[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.492949588[/TD]
[TD="align: right"]438789.0845[/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10,000,000.00[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=B2/B1*SUM(A6:A25)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=IF(A6<$B$3,$B$2,A6/SUMIF($A$6:$A$25,">"&$B$3)*($B$1-COUNTIF($A$6:$A$25,"<="&$B$3)*$B$2))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B26[/TH]
[TD="align: left"]=SUM(B6:B25)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



This also has a few issues with how the percentages work, but it may be sufficient for your needs. Hope this helps.
 
Upvote 0
This can be tricky. By forcing some values to a given value, all the others are slightly shifted as well. Then you have to adjust those, and it becomes an iterative process. You can set something up with the Solver, or with iterative calculations, but both of those are awkward.

Another approach is to figure out which numeric features fall short of the threshold and assign them the minimum value. Then the rest of the total can be apportioned to the rest of the values. That would look like this:

ABC
Total to apportion
Min value
Min numeric feature
Numeric featureApportioned value
24

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]10,000,000.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]150,000.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0.167770219[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]0.901310638[/TD]
[TD="align: right"]802283.3963[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]0.803327803[/TD]
[TD="align: right"]715065.9617[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]0.302376282[/TD]
[TD="align: right"]269154.1187[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0.118878557[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0.940530667[/TD]
[TD="align: right"]837194.3102[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]0.241567195[/TD]
[TD="align: right"]215026.1425[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]0.81744142[/TD]
[TD="align: right"]727628.9118[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]0.521753703[/TD]
[TD="align: right"]464428.4836[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]0.337175248[/TD]
[TD="align: right"]300129.713[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]0.612220382[/TD]
[TD="align: right"]544955.5643[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]0.781189611[/TD]
[TD="align: right"]695360.0988[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]0.772540213[/TD]
[TD="align: right"]687661.0125[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]0.19931728[/TD]
[TD="align: right"]177418.237[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]0.844274884[/TD]
[TD="align: right"]751514.1759[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]0.271566755[/TD]
[TD="align: right"]241729.6427[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]0.556527301[/TD]
[TD="align: right"]495381.4971[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]0.539259607[/TD]
[TD="align: right"]480011.0093[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]0.765787615[/TD]
[TD="align: right"]681650.3242[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.364686522[/TD]
[TD="align: right"]324618.316[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]0.492949588[/TD]
[TD="align: right"]438789.0845[/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10,000,000.00[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B3[/TH]
[TD="align: left"]=B2/B1*SUM(A6:A25)[/TD]
[/TR]
[TR]
[TH]B6[/TH]
[TD="align: left"]=IF(A6<$B$3,$B$2,A6/SUMIF($A$6:$A$25,">"&$B$3)*($B$1-COUNTIF($A$6:$A$25,"<="&$B$3)*$B$2))[/TD]
[/TR]
[TR]
[TH]B26[/TH]
[TD="align: left"]=SUM(B6:B25)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



This also has a few issues with how the percentages work, but it may be sufficient for your needs. Hope this helps.

Ah, that's very clever - thanks.

I guess there's still the problem that when you've identified those 'numeric features' that are too low and allocated the minimums - you've reduced the residual pool, so when those are linearly apportioned some might end up beneath the threshold. It is iterative - as you say.
 
Upvote 0
That's exactly the kind of problem I was talking about. However, I experimented a fair bit with the numbers, trying to force some allotment below the minimum. However, whenever I changed the values, the minimum numeric feature changed also, and I was never able to get anything below the minimum allotment. I can't say that it's impossible mathematically, I'd have to do some more analysis, but such cases would certainly be rare.
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,283
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top