"(IF) Formulas"

Marwan69 · Oct 7, 2018

Hello Ladies and Gentlemen,

Please refer to the below table:

A	B	C	D	E	F	G
Table 1	Table 2	Amount	% of Total Portfolio	% of Total Asset	Under/ Over %	Notes	2
>=20%	100%	15,292	4.7%	1.5%	-15.3%	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.2%	2.3%	-12.8%	Same as 1st note	4
<=10%	5%	40,382	12.4%	3.9%	2.4%	I believe is correct	5
<=10%	1%	0	0.0%	0.0%	0.0%	Correct	6
<=5%	100%	8,709	2.7%	0.8%	-2.3%	Same as 1st note	7
<=5%	5%	17,000	5.2%	1.7%	0.2%	Same as 1st note because 17000 >5%(B8), so it should return (0)	8
<=5%	21,751	6.7%	2.1%	1.7%
<=15%	5%	20,039	6.2%	1.9%	-8.8%	Same as 1st note	10
1%	18,821	5.8%	1.8%	-9.2%		Same as 1st note because 18,821 is <15%(A11), so it should return (0)
<=15%	0%	8,928	2.7%	0.9%	-12.3%	Same as 1st note	12
<=10%	10%	150,899	46.4%	14.7%	36.4%	Correct	13
		325,405		1,027,949			14

[FONT=&quot]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 (D3), nor should it exceed the % of total assets (E3).[/FONT]
[FONT=&quot]However, in certain circumstances like: 15,292(C3), I can invest as much as 100% of my total portfolio. 100% means no limit.[/FONT]
[FONT=&quot]For another asset class, however, such as: 40,382(C5), my investment shouldn't exceed <=10(A5) nor 3.9%(B5). Meaning, as long as am not exceeding the top percentage (10%) am safe.[/FONT]
[FONT=&quot]How can I build a formula to capture all of these parameters.[/FONT]
[FONT=&quot]The formulas in D3 & E3 are: C3/$C$14 and C3/$E$14.[/FONT]
[FONT=&quot]My work as seen in (F3 & F11) is as follows and drag till F13:[/FONT]
[FONT=&quot]-15.3% IF(AND(C3<>0,(E3<=D3>A3)),D3-A3,0)[/FONT]
[FONT=&quot]-9.2% IF(AND(C11<>0,(E11<=D11>A11)),D11-A10,0)[/FONT]
[FONT=&quot]I hope the above is clear. What you have to do is copy and paste the above table in excel by the same rows and columns stated above.
Many thanks,[/FONT]
[FONT=&quot]Marwan[/FONT]



P.S. There's merge cells I couldn't fix it. Sorry for the inconvenience.

"(IF) Formulas"

Marwan69

Board Regular

Excel Facts

Similar threads

Forum statistics

Share this page

"(IF) Formulas"

Marwan69

Board Regular

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock