Determining Which of Two Conditions Are Met First

[purple]

I have a data set of stock prices where each line represents a day and has a price.

Date	Price
6/1/1987	122.35
6/2/1987	119.98

For each row, I want to determine which condition is eventually met first: the price increased by 10%, or it decreased by 5%.

Can someone please help me with a formula (or formulas)? I have no idea of how to do this.

 

[Eric W]

Welcome to the MrExcel forums!

Consider:

Book3
	A	B	C	D
1	Date	Price		First exceeds 10%
2	6/1/1987	122.35		7
3	6/2/1987	119.98		First falls below 5%
4	6/3/1987	109.11		4
5	6/4/1987	120.2
6	6/5/1987	130.11
7	6/6/1987	135.88
8	6/7/1987	131.88
9
Sheet9

Cell Formulas
Range	Formula
D2	D2	=AGGREGATE(15,6,ROW(A2:A100)/(B2:B100>=B2*1.1),1)
D4	D4	=AGGREGATE(15,6,ROW(A2:A100)/(B2:B100<=B2*0.95)/(B2:B100<>""),1)

The D2 and D4 formulas return the row number. If the dates are always increasing, like your example, we can show the date instead.

 

[purple]

Thank you, Eric!

The dates will indeed be sequential (ascending).

So if I'm kind of understanding, I think that each row would have these two new columns (D2 and D4), and it can return the date. I'm guessing that a third column would be needed to indicate which event happened first.

 

[purple]

Hi Eric, I figured out how to compare the two. I wasn't thinking - it actually didn't need the date, row number was sufficient.

=IF(C2 < D2, "gain", "loss")

However, can you please help me figure out how to handle when the formulas finding the condition return #NUM!? For example, for some prices, the price don't ever seem to meet the -5% again.

 

[Eric W]

Do you mean something like this?

Book3
	A	B	C	D	E
1	Date	Price	Next Gain row	Next Loss Row	Summary
2	6/1/1987	122.35	7	4	Loss of 5% from 122.35 achieved on row 4
3	6/2/1987	119.98	7	4	Loss of 5% from 119.98 achieved on row 4
4	6/3/1987	109.11	5	#NUM!	Gain of 10% over 109.11 achieved on row 5
5	6/4/1987	120.2	7	#NUM!	Gain of 10% over 120.2 achieved on row 7
6	6/5/1987	130.11	#NUM!	9	Loss of 5% from 130.11 achieved on row 9
7	6/6/1987	135.88	#NUM!	9	Loss of 5% from 135.88 achieved on row 9
8	6/7/1987	131.88	#NUM!	9	Loss of 5% from 131.88 achieved on row 9
9	6/8/1987	120.22	#NUM!	#NUM!	Price never exceeds the 15% spread in the remaining rows
10	6/9/1987	122.18	#NUM!	#NUM!	Price never exceeds the 15% spread in the remaining rows
11	6/10/1987	121.55	#NUM!	#NUM!	Price never exceeds the 15% spread in the remaining rows
12
Sheet9

Cell Formulas
Range	Formula
C2:C11	C2	=AGGREGATE(15,6,ROW(A2:A100)/(B2:B100>=B2*1.1),1)
D2:D11	D2	=AGGREGATE(15,6,ROW(A2:A100)/(B2:B100<=B2*0.95)/(B2:B100<>""),1)
E2:E11	E2	=IF(ISERROR(C2)*ISERROR(D2),"Price never exceeds the 15% spread in the remaining rows",IF(IFERROR(C2,2^99)<IFERROR(D2,2^99),"Gain of 10% over "&B2&" achieved on row "&C2,"Loss of 5% from "&B2&" achieved on row "&D2))

 

[purple]

YES! Thank you!!!!

 

[Eric W]

Happy to help!