"(IF) Formulas"

[Marwan69]

[TABLE="width: 64"]
<tbody>[TR]
[TD="align: left"]
[TABLE="width: 567"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: left"]

Table 1​

[/TD]
[TD="align: left"]

Table 2​

[/TD]
[TD="align: left"]

Amount​

[/TD]
[TD="align: left"]

% of Total Portfolio​

[/TD]
[TD="align: left"]

% of Total Asset​

[/TD]
[TD="align: left"]

Under/ Over %​

[/TD]
[TD="align: left"]

Notes​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

>=20%​

[/TD]
[TD]

100%​

[/TD]
[TD]

15,292​

[/TD]
[TD]

4.70%​

[/TD]
[TD]

1.50%​

[/TD]
[TD]

-15.30%​

[/TD]
[TD]

I can't understand how this formula works. The value isn't true but still returns(D4-A4). It should return(0)​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

>=20%​

[/TD]
[TD]

100%​

[/TD]
[TD]

23,585​

[/TD]
[TD]

7.20%​

[/TD]
[TD]

2.30%​

[/TD]
[TD]

-12.80%​

[/TD]
[TD]

Same as 1st note​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

<=10%​

[/TD]
[TD]

5%​

[/TD]
[TD]

40,382​

[/TD]
[TD]

12.40%​

[/TD]
[TD]

3.90%​

[/TD]
[TD]

2.40%​

[/TD]
[TD]

I believe is correct​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

<=10%​

[/TD]
[TD]

1%​

[/TD]
[TD]

0​

[/TD]
[TD]

0.00%​

[/TD]
[TD]

0.00%​

[/TD]
[TD]

0.00%​

[/TD]
[TD]

Correct​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

<=5%​

[/TD]
[TD]

100%​

[/TD]
[TD]

8,709​

[/TD]
[TD]

2.70%​

[/TD]
[TD]

0.80%​

[/TD]
[TD]

-2.30%​

[/TD]
[TD]

Same as 1st note​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

<=5%​

[/TD]
[TD]

5%​

[/TD]
[TD]

17,000​

[/TD]
[TD]

5.20%​

[/TD]
[TD]

1.70%​

[/TD]
[TD]

0.20%​

[/TD]
[TD]

Same as 1st note because 17000 >5%(B8), so it should return (0)​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

<=5%​

[/TD]
[TD][/TD]
[TD]

21,751​

[/TD]
[TD]

6.70%​

[/TD]
[TD]

2.10%​

[/TD]
[TD]

1.70%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Cell Formula
D2 C2/325,405
E2 C2/1,027,949
F2 IF(AND(C2<>0,(E2<=D2>A2)),D2-A2,0)
[/TD]
[/TR]
</tbody>[/TABLE]
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

 

[Macropod]

This thread is a duplicate of: https://www.mrexcel.com/forum/excel-questions/1073315-if-formulas.html

Kindly read Mr Excel's policy in Rule 12 against asking questions about the same topic in multiple threads: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Thread closed. You may continue the discussion in your other thread.