What if investment

[tigerzen]

I've been asked how to model the following, if I take out 1 or 2 stocks, can Excel display the output without the stocks taken out? So if something is entered into rows 13 and/or 14 then an output like what is in column G to J is produced, this shows the impact of removing the 2 stocks namely B and C. Looking for a formula/function solution that will work on Excel 2019.

Shares what if.xlsx
	B	C	D	E	F	G	H	I	J
2	Name	Purchase Price	Current value	Return		Name	Purchase Price	Current value	Return
3	Stock A	$ 800	$ 1,000	80%		Stock A	$ 800	$ 1,000	80%
4	Stock B	$ 400	$ 600	67%		Stock D	$ 250	$ 500	50%
5	Stock C	$ 600	$ 800	75%		Total	$ 1,050	$ 1,500	70%
6	Stock D	$ 250	$ 500	50%
7	Total	$ 2,050	$ 2,900	71%
8
9
10
11
12	Impact of selling
13	Stock B	400	600	67%
14	Stock C	600	800	75%
Sheet1

Cell Formulas
Range	Formula
E3:E7,J3:J5	J3	=H3/I3
H5:I5	H5	=SUM(H3:H4)
C7:D7	C7	=SUM(C3:C6)
C13:C14	C13	=VLOOKUP(B13,$B$3:$E$6,2,FALSE)
D13:D14	D13	=VLOOKUP(B13,$B$3:$E$6,3,FALSE)
E13:E14	E13	=VLOOKUP(B13,$B$3:$E$6,4,FALSE)

Cells with Data Validation
Cell	Allow	Criteria
B13:B14	List	=$B$3:$B$6

 

[bebo021999]

Try to use COUNTIF to define names in table 1, those are NOT in table 2, then using INDEX to get name

Book1.xlsx
	B	C	D	E	F	G	H	I	J
2	Name	Purchase Price	Current value	Return		Name	Purchase Price	Current value	Return
3	Stock A	$ 800	$ 1,000	#VALUE!		Stock A	$ 800	$ 1,000	#VALUE!
4	Stock B	$ 400	$ 600	#VALUE!		Stock D	$ 250	$ 500	#VALUE!
5	Stock C	$ 600	$ 800	#VALUE!		Total	0	0	#DIV/0!
6	Stock D	$ 250	$ 500	#VALUE!
7	Total	0	0	#DIV/0!
8
9
10
11
12	Impact of selling
13	Stock B	$ 400	$ 600
14	Stock C	$ 600	$ 800
15
16
17
18
Sheet2

Cell Formulas
Range	Formula
E3:E7,J3:J5	J3	=H3/I3
H5:I5	H5	=SUM(H3:H4)
C7:D7	C7	=SUM(C3:C6)
B13:B18	B13	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$3:$B$6)/(COUNTIF($G$3:$G$4,$B$3:$B$6)=0),ROWS($1:1))),"")
C13:E18	C13	=IFERROR(VLOOKUP($B13,$B$3:$E$6,COLUMNS($A:B),FALSE),"")

 

[tigerzen]

Thanks Bebo, I think I can adapt your formula to work for my situation as yours might be the other way around to mine. In mine, the output in columns G to J need to be formula-derived. The idea being that one selects a stock from a drop-down box and the output is generated in G to J.

When I first came across this issue I automatically thought SUMIFS or FILTER function. Couldn't get either to quite finish the problem.