| A | B | C | D | E | F | G | | 1 | Table 1 | Table 2 | Amount | % of Total Portfolio | % of Total Asset | Under/ Over % | Notes | 2 | >=20% | 100% | 15,292 | 4.70% | 1.50% | -15.30% | I can't understand how this formula works. The value isn't true but still returns(D4-A4). It should return(0) | 3 | >=20% | 100% | 23,585 | 7.20% | 2.30% | -12.80% | Same as 1st note | 4 | <=10% | 5% | 40,382 | 12.40% | 3.90% | 2.40% | I believe is correct | 5 | <=10% | 1% | 0 | 0.00% | 0.00% | 0.00% | Correct | 6 | <=5% | 100% | 8,709 | 2.70% | 0.80% | -2.30% | Same as 1st note | 7 | <=5% | 5% | 17,000 | 5.20% | 1.70% | 0.20% | Same as 1st note because 17000 >5%(B8), so it should return (0) | 8 | <=5% | | 21,751 | 6.70% | 2.10% | 1.70% | | | | | | | | | | | | | | | | | |
<tbody>
</tbody> Cell Formula
D2 C2/325,405
E2 C2/1,027,949
F2 IF(AND(C2<>0,(E2<=D2>A2)),D2-A2,0)
|
<tbody>
</tbody>
As you can see, I'm bound by these two boundaries(Table 1 & 2). The amount that I should invest shouldn't exceed the % of portfolio, nor should it exceed the % of total assets.
However, in certain circumstances
like: 15,292, I can invest as much as 100% of my total portfolio. 100% means no limit.
For another asset class, however, such as: 40,382, my investment should be between <=10 & 3.9%.
How can I build a formula to capture all of these parameters?
Many thanks,
Marwan
