Lookup value until cell in reference column changes

[tvrage]

Dear experts,

I have random securities in column B with a respective value in column C. I want to calculate the profit I made if I sell the security. However, I only sell if the security in column B changes in the following row. Otherwise I hold the security and the profit is zero (or empty, not relevant). Here is an example. I start with 100 and the periods increase with rows, i.e. I compute profit by calculating from bottom to top.

[TABLE="class: grid"]
<tbody>[TR]
[TD]Month (=A1)[/TD]
[TD]Security[/TD]
[TD]Value[/TD]
[TD]Profit[/TD]
[TD]Calculation[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/09/2011[/TD]
[TD]Bond[/TD]
[TD]91.49[/TD]
[TD]0[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]31-Oct-2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Bond[/TD]
[TD]91.65[/TD]
[TD]0[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]30-Nov-2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Bond[/TD]
[TD]91.80[/TD]
[TD]0[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]31-Dec-2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Bond[/TD]
[TD]91.95[/TD]
[TD]0[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]31-Jan-2012[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Bond[/TD]
[TD]92.10[/TD]
[TD]-7.90[/TD]
[TD]C7-C2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]29-Feb-2012[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]S&P[/TD]
[TD]95.67[/TD]
[TD]3.56[/TD]
[TD]C8-C7[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]31-Mar-2012[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Dow[/TD]
[TD]96.92[/TD]
[TD]0[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]30-Apr-2012[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Dow[/TD]
[TD]94.32[/TD]
[TD]-1.35[/TD]
[TD]C10-C8[/TD]
[/TR]
</tbody>[/TABLE]


I want to find the formula for the profit. The table continues for various periods, so the securities repeat themselves randomly. I tried to use INDEX with counting the number of time a security appears until it changes but it did not work. COUNTIF until value in column B changes didn't work either since it counts all values in the entire table, not just the ones until the security changes.

I could not find a forum entry in this respect.
Many thanks for your help.

 

[wideboydixon]

Book1
	A	B	C	D
1	Month	Security	Value	Profit
2			100.00
3	30/09/2011	Bond	91.49	0.00
4	31/10/2011	Bond	91.65	0.00
5	30/11/2011	Bond	91.80	0.00
6	31/12/2011	Bond	91.95	0.00
7	31/01/2012	Bond	92.10	-7.90
8	29/02/2012	S&P	95.67	3.57
9	31/03/2012	Dow	96.92	0.00
10	30/04/2012	Dow	94.32	-1.35
Sheet1

Cell Formulas
Range	Formula
D3	{=IF($B3=$B4,0,$C3-INDEX($C$2:$C3,MAX(IF($B$2:$B3<>$B3,ROW($B$2:$B3)-1,0))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[tvrage]

This is excellent, Sir. Thank you very much!

 

[Nishant94]

A Non-Array solution of the same can be like this:

Unknown
	A	B	C	D
1	Month (=A1)	Security	Value	Profit
2			100
3	30/09/2011	Bond	91.49	-
4	31-Oct-11	Bond	91.65	-
5	30-Nov-11	Bond	91.8	-
6	31-Dec-11	Bond	91.95	-
7	31-Jan-12	Bond	92.1	-7.90
8	29-Feb-12	S&P	95.67	3.57
9	31-Mar-12	Dow	96.92	-
10	30-Apr-12	Dow	94.32	-1.35
Sheet6

Cell Formulas
Range	Formula
D3	=IF(B3<>B4,C3-INDEX($C$2:$C$10,MATCH(B3,$B$2:$B$10,0)-1),"-")

 

[wideboydixon]

Only if the value in Column B doesn't repeat. Switching it back to "Bond" in B9, for example, would make this formula fail.

WBD