How to get the First date the Price was over or below the Average Price?

[Mathman]

Hi

I'm trying to figure out how to get the First date the Price was over or below the Average Price.

The first thing needed is to see if the current price (dec 23,2016) is above or below the average. If the current price is above the average the result should be positive but if the result was current price is below the average the result should be negative.

I need to find (in the example) when the First time (Date) the price was above the current average (as the current price is above the average) the result should be Positive (Dec 23,2016) - First day price was above the average (Dec 19, 2016) =Should Return 4.

Current Average	$23.25
	Price
23-Dec-16	$24.22
22-Dec-16	$24.20
21-Dec-16	$23.85
20-Dec-16	$23.90
19-Dec-16	$23.60
16-Dec-16	$23.06
15-Dec-16	$23.43
14-Dec-16	$23.41
13-Dec-16	$23.64
12-Dec-16	$23.53
9-Dec-16	$23.46
8-Dec-16	$23.46
7-Dec-16	$22.99
6-Dec-16	$22.90
5-Dec-16	$22.84
2-Dec-16	$22.64
1-Dec-16	$22.86
30-Nov-16	$22.30
29-Nov-16	$22.54
28-Nov-16	$22.41
25-Nov-16	$23.21
23-Nov-16	$23.08

<tbody>
</tbody>

In this example the result should be a negative number as the Last price (Dec 23, 2016) is below the average. The result should be (Dec 23,2016) - First date below average (Dec 14,2016) results = -9




Average 0.53
23-Dec-16 0.49
22-Dec-16. 0.52
21-Dec-16. 0.52
20-Dec-16. 0.50
19-Dec-16. 0.51
16-Dec-16. 0.52
15-Dec-16. 0.52
14-Dec-16. 0.51
13-Dec-16. 0.53
12-Dec-16. 0.52
9-Dec-16 0.53
8-Dec-16. 0.53
7-Dec-16. 0.54
6-Dec-16. 0.53
5-Dec-16. 0.53
2-Dec-16. 0.51
1-Dec-16. 0.51
30-Nov-16. 0.53
29-Nov-16. 0.53
28-Nov-16. 0.53
25-Nov-16. 0.55
24-Nov-16. 0.54
23-Nov-16. 0.56

If you can help that would be great.

Thank you

 

[Tetra201]

You are correct, I was off by one on my initial post, my apologies. Here are some of the results I got and what the the results should be. Both formula did return the same results.

- Should be -1 -- that's fixable.
- Should be 1 -- that's fixable.
- Should be -9 -- in this case, the threshold date should be 15-Jan-16. There is no such date in the set.
- Should be -7 -- in this case, the threshold date should be 17-Jan-16. Why?
- Should be 8 -- in this case, the threshold date should be 16-Jan-16. Why?
- Should be 13 -- in this case, the threshold date should be 11-Jan-16. Why?
- Should be 12 -- in this case, the threshold date should be 12-Jan-16. Why?

 

[Mathman]

I see want you're saying. Looks like how I thought I could get the result I wanted wasn't the right approach. If I start counting weekends and holidays this can get me away from the true value I'm seeking.



So I will try to do a better job explains the result I would like. This example the first price is over the average so we will have a positive number. so we have the first 12 prices above the average and so I want the result to be 12. Keep in mind that in my data set I do not have the true or false values, I just put them their to show the pattern.

Average ->	$7.200
		Positive
23-Jan-17	$7.750	TRUE
20-Jan-17	$7.650	TRUE
19-Jan-17	$7.550	TRUE
18-Jan-17	$7.600	TRUE
17-Jan-17	$8.000	TRUE
16-Jan-17	$7.550	TRUE
13-Jan-17	$7.500	TRUE
12-Jan-17	$7.550	TRUE
11-Jan-17	$7.700	TRUE
10-Jan-17	$7.600	TRUE
9-Jan-17	$7.400	TRUE
6-Jan-17	$7.800	TRUE
5-Jan-17	$7.000	FALSE
4-Jan-17	$6.950	FALSE
3-Jan-17	$6.500	FALSE
30-Dec-16	$6.950	FALSE
29-Dec-16	$6.100	FALSE
28-Dec-16	$6.000	FALSE
23-Dec-16	$5.650	FALSE

<colgroup><col width="87" span="3" style="width: 65pt;"></colgroup><tbody>
</tbody>

And this one is a negative result. it's below the average from the first row 9 times giving a result of -9

Average ->	$26.484
		Negative
23-Jan-17	$26.190	TRUE
20-Jan-17	$26.210	TRUE
19-Jan-17	$26.130	TRUE
18-Jan-17	$26.110	TRUE
17-Jan-17	$26.280	TRUE
16-Jan-17	$26.340	TRUE
13-Jan-17	$26.310	TRUE
12-Jan-17	$26.230	TRUE
11-Jan-17	$26.400	TRUE
10-Jan-17	$26.810	FALSE
9-Jan-17	$26.970	FALSE
6-Jan-17	$27.080	FALSE
5-Jan-17	$26.980	FALSE
4-Jan-17	$26.840	FALSE
3-Jan-17	$26.530	FALSE
30-Dec-16	$26.630	FALSE
29-Dec-16	$26.570	FALSE
28-Dec-16	$26.200	TRUE
23-Dec-16	$26.380	TRUE

<colgroup><col width="87" span="3" style="width: 65pt;"></colgroup><tbody>
</tbody>

I hope a did a better job with this explanation. Thank again!

 

[Tetra201]

In Post #1 you wrote:

... I'm trying to figure out how to get the First date the Price was over or below the Average Price...

That's why both my formulas were operating with dates from column A.

If you need just the number of entries (that is, rows) separating the current date/price and the threshold date/price, then the formula becomes much simpler. It's still an array formula:

=IF(B3=B1,0,IF(B3>B1,MATCH(FALSE,B3:B1000>B1,0)-1,1-MATCH(FALSE,B3:B1000< B1,0)))

 

[Mathman]

Thanks Tetra201 That's awesome, gives me the desired result. Much appreciated. I took me a while but I appreciate your patience. Sometimes what you think you want isn't the case.



Any chance your formula can be worked into the example below with DSUM's? I'm still a little worried using an array formula.

Average	$3.73
Date	Pice
23-Jan-17	$4.20
20-Jan-17	$4.13
19-Jan-17	$4.03
18-Jan-17	$3.90
17-Jan-17	$3.80
13-Jan-17	$3.90
12-Jan-17	$4.09
11-Jan-17	$3.76
10-Jan-17	$3.74
9-Jan-17	$3.85
6-Jan-17	$3.71
5-Jan-17	$3.98
4-Jan-17	$3.71
3-Jan-17	$3.38
30-Dec-16	$3.14
29-Dec-16	$3.24
28-Dec-16	$3.44
27-Dec-16	$3.46
23-Dec-16	$3.50
22-Dec-16	$3.57
22-Dec-16	23-Jan-17
Date	Date
>=42726	<=42758

<tbody>
</tbody>

 

[Tetra201]

Thanks Tetra201 That's awesome, gives me the desired result. Much appreciated. I took me a while but I appreciate your patience. Sometimes what you think you want isn't the case.
Any chance your formula can be worked into the example below with DSUM's? I'm still a little worried using an array formula.

Actually, my formula can be even further simplified, but it's still an array formula:

=SIGN(B3-B1)*(MATCH(FALSE,SIGN(B3-B1)*B3:B1000>SIGN(B3-B1)*B1,0)-1)

I will try to see if DSUM can be used to achieve the same.

 

[Mathman]

that really would be great if a version using DSUM'S were possible!

 

[Mathman]

Looks after playing with it I managed to get the same results as the array but using the DSUM functions. The paste job is ugly but the formula is below. If you have a suggestion to make this more simple please let me know.

Here's a questions to anyone, when using DSUM functions is a heading always needed?

Thanks again.




Formula

SUM(F4+1)	B4	average	Date	Date	average
		"<="&(C2)	">="&(E2)	"<="&(F2)	">="&(C2)
	EDATE(F2,-3)
				Date
				">"&(DMAX(B3:C300,B3,H2:J3))
				Date
				">"&(DMAX(B3:C300,B3,G2:I3))
			Result	IF(DCOUNT(B3:C300,B3,I6:I7)+DCOUNT(B3:C300,B3,I8:I9)=0,"",DCOUNT(B3:C300,B3,I6:I7)+DCOUNT(B3:C300,B3,I8:I9))*SIGN(C4-C2)

<tbody>
</tbody>

Set Up
Average ->	$1.954		24-Oct-16	23-Jan-17	average	Date	Date	average
Date	Average				<=1.95412698412698	>=42667	<=42758	>=1.95412698412698
23-Jan-17	$1.660			23-Oct-16		Date	Date
20-Jan-17	$1.720
19-Jan-17	$1.710						Date	Date
18-Jan-17	$1.810						>42738	>42758
17-Jan-17	$1.870
16-Jan-17	$1.840					Result	-14
13-Jan-17	$1.840
12-Jan-17	$1.810
11-Jan-17	$1.900
10-Jan-17	$1.840
9-Jan-17	$1.860
6-Jan-17	$1.870
5-Jan-17	$1.900
4-Jan-17	$1.950
3-Jan-17	$1.970
30-Dec-16	$1.930
29-Dec-16	$1.960
28-Dec-16	$1.980
23-Dec-16	$1.920
22-Dec-16	$2.080
21-Dec-16	$2.190
20-Dec-16	$2.170

<tbody>
</tbody>

 

[Tetra201]

Here are my suggestions on making your setup and formula simpler:

- Empty G2:G3;
- Empty I8:I9;
- J2 =CHAR((C4 < C2)*2+60)&"="&C2<c2)*2+60)&"="&c2

- Formula =IF(C4=C2,"",SIGN(C4-C2)*DCOUNT(B3:C300,B3,I6:I7))</c2)*2+60)&"="&c2