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?

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

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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
1	Total to apportion	10,000,000.00
2	Min value	150,000.00
3	Min numeric feature	0.167770219
4
5	Numeric feature	Apportioned value
6	0.901310638	802283.3963
7	0.803327803	715065.9617
8	0.302376282	269154.1187
9	0.118878557	150000
10	0.940530667	837194.3102
11	0.241567195	215026.1425
12	0.81744142	727628.9118
13	0.521753703	464428.4836
14	0.337175248	300129.713
15	0.612220382	544955.5643
16	0.781189611	695360.0988
17	0.772540213	687661.0125
18	0.19931728	177418.237
19	0.844274884	751514.1759
20	0.271566755	241729.6427
21	0.556527301	495381.4971
22	0.539259607	480011.0093
23	0.765787615	681650.3242
24	0.364686522	324618.316
25	0.492949588	438789.0845
26		10,000,000.00

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B3 =B2/B1*SUM(A6:A25) B6 =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)) B26 =SUM(B6:B25) <tbody> </tbody>

<tbody>
</tbody>

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
1	Total to apportion	10,000,000.00
2	Min value	150,000.00
3	Min numeric feature	0.167770219
4
5	Numeric feature	Apportioned value
6	0.901310638	802283.3963
7	0.803327803	715065.9617
8	0.302376282	269154.1187
9	0.118878557	150000
10	0.940530667	837194.3102
11	0.241567195	215026.1425
12	0.81744142	727628.9118
13	0.521753703	464428.4836
14	0.337175248	300129.713
15	0.612220382	544955.5643
16	0.781189611	695360.0988
17	0.772540213	687661.0125
18	0.19931728	177418.237
19	0.844274884	751514.1759
20	0.271566755	241729.6427
21	0.556527301	495381.4971
22	0.539259607	480011.0093
23	0.765787615	681650.3242
24	0.364686522	324618.316
25	0.492949588	438789.0845
26		10,000,000.00

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B3 =B2/B1*SUM(A6:A25) B6 =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)) B26 =SUM(B6:B25) <tbody> </tbody>

<tbody>
</tbody>

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.