Data filtering problem - challenging!

Hopefully someone can give me the answer or point me in the right direction. It's a bit tricky to explain, but here goes - I will try to keep it as simple as possible.

It will be a lot easier to explain if you view the workbook I have attached to this message.

The workbook represents an investment into four funds. The amount to invest is in cell B8 (currently £1,000 - this can be changed at will).

Column A: contains the four fund names. These do not change.

Column B: represents the percentage allocation of the investment to allocate to the particular fund . IE: B2 contains 10%, so 10% of our investment of £1,000 is to be allocated to this fund. B3 contains 20%, so 20% of £1,000 to be allocated, and so on. Note that the total of column B is 100%, just to be sure that all of the £1,000 has been accounted for. For the purpose of this example, these percentages will not change, but we can change these in the future if we decide to change the amount we allocate to each fund. Remember, though, that whatever % allocation we give to each of the four funds, the total across all four will always equal 100%.

Column C: this is the minimum amount that can be invested into each fund. This does not change.

The next five "PARSE" columns are a filter. This will be explained later.

Our main aim:

To invest the £1,000 across the four funds, and split it according to the percentages in column B, subject to the minimum amount.

The twist:

If a particular fund's minimum is not met, the amount that would have been invested into it should be split across the other funds, taking into account their own % allocation.

The filters in columns D to H make this calculation. I will explain later the problem that I am having which needs a solution, because I'm stuck!

FILTER 1 - calculates the amount that will be allocated to the particular fund. IE: for FUND A, the formula is =$B$8*B2 which translates as £1,000 x 10%, which is £100.00.

FILTER 2 - checks to see if the result of FILTER1 is less than the minimum amount in column C. For FUND A, the formula is =IF(D2<C2,0,D2). If true, make the result of FILTER 2 zero. If false, stick with FILTER1's result. Note that FILTER2 has a total, which represents the total of the funds that passed this FILTER2 test. This will be used in FILTER3's calculation:

FILTER 3 - Getting intense now! Checks to see if the result of FILTER2 is not zero. IF it is, keep it as zero. If not, it divides FILTER1's result into FILTER2's TOTAL to get a percentage. This percentage represents the new percentage amount to be allocated to the fund, and is effectively spreading the funds that failed the FILTER2 test across the remaining funds that passed the FILTER2 test. In our example, FUNDS B and C failed the FILTER2 test, because they did not exceed the minimum amount required in column C. For FUND A, the formula is =IF(E2>0,D2/$E$6,0)

FILTER 4 - subtracts FILTER 2's total from FILTER 1's total and applies FILTER 3's percentage to get the new EXTRA amount to invest for this fund. If FILTER3's result was zero, FILTER 4 will stay at zero.

FILTER 5 - Adds the results of FILTER 2 and FILTER 4 together to give the new total amount to invest in the fund.

FILTER 5 therefore contains the amount that needs to be invested into each fund, allowing for any funds that do not meet the minimum amount.

This filter system can probably be simplified, but that's for another time.

Now, on to the problem!

To illustrate this better, change all of the % allocations in column B to 25%. Make sure that the total in B6 is 100%, but leave the minimum amounts in column C alone.

Test 1 - change the total investment amount in B8 to £2,000. Everything works fine, because each fund is to receive £500. The highest minimum amount is FUND D, which is £400.

Test 2 - change the total investment amount in B8 to £1,500. FUND D's minimum is now not reached. £1,500 x 25% = £375, which is less than the minimum required of £400 (column C). The filters then take over and work out how much of FUND D's £375 should be spread over the other funds. As the other three funds each receive 25%, it is spread evenly. Everything fine.

Test 3 - change the total investment amount in B8 to £1,000. This is where we start to have problems. FUND D still doesn't reach it's minimum amount, so that's OK. The problem is with FUND C. On face value, everything looks OK. As £300 is needed for FUND C, but only £250 is available, then it doesn't meet it's minimum. Correct, but is it? What about the £250 we have available from FUND D? If FUND D's £250 is spread over the other three funds, we would have an extra £83.33 available to each fund, making a total of £333.33 available to each fund, thereby exceeding the minimum amount of £300 required for FUND C.

So the problem is this: FILTER1 is only checking the original intended amount for the fund, not what could be available to it.

I can see the problem, I just don't know how to fix it. I expect some elaborate expanding of the FILTER system will be needed. I would say the best method is to work out the order of which fund would fail to meet it's minimum amount first, then second, then third, and so on.

If you wanted a challenge, then this is surely it? Good luck and I will remain in your debt if you can solve this! Thanks everyone.