Apportioning values but keep in minimum value in formula

[booms]

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

 

[Eric W]

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:

	A	B	C
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.

 

[booms]

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:

	A	B	C
Total to apportion
Min value
Min numeric feature
Numeric feature	Apportioned 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.

 

[Eric W]

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.