excelexcel-365

In Excel formula I am trying to split a total savings value between goals but once a goal is reached allocate to the remaining to the other goals

In this table below I am trying to create a formula in Column D under 'Allocated' that allocates a split of D1 based on the goal and allocation %, once an allocated amount reaches the target amount it stops receiving any further allocation and the remaining would be spread to the other goals (distributed using ratios of the remaining allocation %)

A B C D E
1 Savings £39,000
2 Goal Name Target Amount Allocation % Allocated Desired result
3 Goal 1 £2,000,000 10% £7,571 £11,500
4 Goal 2 £15,000 25% £18,929 £15,000
5 Goal 3 £5,000 40% £5,000 £5,000
6 Goal 4 £7,500 25% £7,500 £7,500

I had managed to make (brute force) a formula that almost worked that I will try to break down my logic below but there will hopefully be a better approach rather than trying to fix mine.

My formula taken from D3:
=IF(B3<=$D$1*C3,B3,($D$1-SUMPRODUCT(($B$3:$B$6<=$C$3:$C$6*$D$1)*$B$3:$B$6))*(C3*(1/SUMPRODUCT(($B$3:$B$6>$C$3:$C$6*$D$1)*$C$3:$C$6))))

IF B3<=D1*C3,B3 this should set the allocated amount to be no higher than the target, this issue is that in the above example for row 4, 39k * 0.25 = 9,750 meaning this IF returns a false.

$D$1-SUMPRODUCT(($B$3:$B$6<=$C$3:$C$6*$D$1)*$B$3:$B$6)) calculates the sum of any row that would have returned a true above and deducts them from D1 to leave the remaining amount to allocate. In this case it sees row 5 & 6 as true so deducts 5k&7.5k from 39k to leave 26.5k

(C3*(1/SUMPRODUCT(($B$3:$B$6>$C$3:$C$6*$D$1)*$C$3:$C$6)))) does similar as above to find the allocation % that would have been false for the if and sums them together. It divides 1 by this sum to give a factor the apply to the original allocation % for the new % in this case 1/( 0.1 + 0.25) = 2.86

2.86 * 0.1 = 0.286

+

2.86 * 0.25 = 0.714

total = 1 (100%)

The remaining amount to allocate is multiplied by the new % to get the final allocation.

This issue is this then doesn't account for if this new allocation takes the allocated amount above the target as seen in D4.

Edit for clarity

In my table above you can see the results of my formula in D3:D6 however I would want D4 to show 15k as that is the target and the remaining to be allocated to D3 instead. Also added a column to show the desired result.

Solution

  • Here is the solution for you:

    enter image description here

    [D3]=LET(tgts,B3:B6,prcs,C3:C6,savs,D1,
  main,LAMBDA(alloc,percents,lmbd,
    LET(diff,tgts-alloc,remnd,IF(diff<0,0,diff),deal,(savs-SUM(alloc))*percents,
      cond,remnd<deal,remp,IF(cond,0,percents),na,IF(cond,remnd,deal)+alloc,
      IF(savs>SUM(na),lmbd(na,remp/SUM(remp),lmbd),na)
    )
  ),
  main(tgts*0,prcs,main)
)

    The solution utilizes recursive calls to approach the current allocation to the resulting allocation which meets all conditions and consumes the whole savings.

    If the allocation can't be reached the formula returns "#DIV/0!".

    Step by step:

    1. The final expression main(tgts*0,prcs,main) calls the main function with the initial allocation {0;0;0;0} and the initial percentage.
    2. The main function (its definition starts at the second line) calculates in lines #3 to #5: a) the next allocation na in the defined limits; b) the next percentage remp/SUM(remp).
    3. In line #5, if the next allocation doesn't consume the whole savings savs>SUM(na), the function calls itself recursively with the next allocation and percentage, otherwise it returns.