Calculating RAtio

[kumara_faith]

Hi,

I have thf ollowing table:

Normalize to 100^1.xlsx
	D	E	F	G	H	I	J
11	STOCK NAME	PRICE PER UNIT	DISCOUNT	AVAILABLE FUND	INVESTMENT RATIO	AMOUNT TO BE INVESTED	UNIT TO BE INVESTED
12	ALPHABET INC. (XNAS:GOOGL)	$ 101.13	41%	5000		0	#DIV/0!
13	AMAZON.COM, INC. (XNAS:AMZN)	$ 119.32	42%	5000		0	#DIV/0!
14	MICROSOFT CORPORATION (XNAS:MSFT)	$ 242.12	31%	5000		0	#DIV/0!
Sheet1

Cell Formulas
Range	Formula
I12:I14	I12	=H12*G12
J12:J14	J12	=E12/I12
E12:E14	E12	=D12.Price

Column E shows the stock discount based on their evaluation. Total available funds are 5,000. In column H, I would like to build a formula to show what will be the ratio of amount from the 5,000 that should be invested based on the discount of the stock and the total available fund of 5,000. The investment ratio in column H has to equal 100%. Is there a way to do this? Appreciate the help.

 

[StephenCrump]

... what will be the ratio of amount from the 5,000 that should be invested based on the discount of the stock ...

The answer will depend on what you mean by based on the discount? You haven't explained this.

One solution would be to put the lot into Amazon, because in your evaluation it is the most heavily discounted.

I'm guessing you intend some sort of weighted average? If this is based on the relative sizes of the % discounts for each stock, then you'd allocate your $5,000 this way (before allowance for rounding):

	D	E	F	G
11	STOCK NAME	PRICE PER UNIT	DISCOUNT	%
12	ALPHABET INC. (XNAS:GOOGL)	101.13	41%	36.0%
13	AMAZON.COM, INC. (XNAS:AMZN)	119.32	42%	36.8%
14	MICROSOFT CORPORATION (XNAS:MSFT)	242.12	31%	27.2%
15				100.0%
Sheet1

Cell Formulas
Range	Formula
G12:G14	G12	=F12/SUM(F$12:F$14)
G15	G15	=SUM(G12:G14)

Perhaps a more realistic solution would take into account your existing holdings and how much you were prepared to be under/overweight relative to the index?

 

[kumara_faith]

Thanks Stephen. That worked and have a great day ahead.