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

M

Mathman

Board Regular
Joined
Jan 28, 2017
Messages
152
Office Version
  1. 2016
Platform
  1. Windows
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.






[TABLE="width: 130"]
<tbody>[TR]
[TD="width: 65"]Current Average[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl65, width: 65, align: right"]$23.25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Price[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]23-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$24.22[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]22-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$24.20[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]21-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$23.85[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]20-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$23.90[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]19-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$23.60[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]16-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$23.06[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]15-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$23.43[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]14-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$23.41[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]13-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$23.64[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]12-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$23.53[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]9-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$23.46[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]8-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$23.46[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]7-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$22.99[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]6-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$22.90[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]5-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$22.84[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]2-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$22.64[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]1-Dec-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$22.86[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]30-Nov-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$22.30[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]29-Nov-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$22.54[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]28-Nov-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$22.41[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]25-Nov-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$23.21[/TD]
[/TR]
[TR]
[TD="class: gmail-m_7317369843439429420gmail-xl63, align: right"]23-Nov-16[/TD]
[TD="class: gmail-m_7317369843439429420gmail-xl64, align: right"]$23.08[/TD]
[/TR]
</tbody>[/TABLE]



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

 
Sort by date Sort by votes
Mathman said:
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.
Click to expand...
- 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?
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.


[TABLE="width: 261"]
<colgroup><col width="87" span="3" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: gmail-xl63, width: 87"]Average ->[/TD]
[TD="class: gmail-xl64, width: 87, align: right"] $7.200 [/TD]
[TD="class: gmail-xl63, width: 87"][/TD]
[/TR]
[TR]
[TD="class: gmail-xl63"][/TD]
[TD="class: gmail-xl63"][/TD]
[TD="class: gmail-xl63"]Positive[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]23-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $7.750 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]20-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $7.650 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]19-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $7.550 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]18-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $7.600 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]17-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $8.000 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]16-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $7.550 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]13-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $7.500 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]12-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $7.550 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]11-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $7.700 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]10-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $7.600 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]9-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $7.400 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]6-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $7.800 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]5-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $7.000 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]4-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $6.950 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]3-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $6.500 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]30-Dec-16[/TD]
[TD="class: gmail-xl66, align: right"] $6.950 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]29-Dec-16[/TD]
[TD="class: gmail-xl66, bgcolor: #FFC7CE, align: right"] $6.100 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]28-Dec-16[/TD]
[TD="class: gmail-xl66, align: right"] $6.000 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]23-Dec-16[/TD]
[TD="class: gmail-xl66, align: right"] $5.650 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]




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




[TABLE="width: 261"]
<colgroup><col width="87" span="3" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: gmail-xl63, width: 87"]Average ->[/TD]
[TD="class: gmail-xl64, width: 87, align: right"] $26.484 [/TD]
[TD="class: gmail-xl67, width: 87"][/TD]
[/TR]
[TR]
[TD="class: gmail-xl63"][/TD]
[TD="class: gmail-xl63"][/TD]
[TD="class: gmail-xl63"]Negative[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]23-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.190 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]20-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.210 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]19-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.130 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]18-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.110 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]17-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.280 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]16-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.340 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]13-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.310 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]12-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.230 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]11-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.400 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]10-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.810 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]9-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.970 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]6-Jan-17[/TD]
[TD="class: gmail-xl66, bgcolor: #FFC7CE, align: right"] $27.080 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]5-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.980 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]4-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.840 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]3-Jan-17[/TD]
[TD="class: gmail-xl66, align: right"] $26.530 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]30-Dec-16[/TD]
[TD="class: gmail-xl66, align: right"] $26.630 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]29-Dec-16[/TD]
[TD="class: gmail-xl66, align: right"] $26.570 [/TD]
[TD="class: gmail-xl63, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]28-Dec-16[/TD]
[TD="class: gmail-xl66, align: right"] $26.200 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, align: right"]23-Dec-16[/TD]
[TD="class: gmail-xl66, align: right"] $26.380 [/TD]
[TD="class: gmail-xl63, align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]






I hope a did a better job with this explanation. Thank again!
 
Upvote 0
In Post #1 you wrote:

Mathman said:
... I'm trying to figure out how to get the First date the Price was over or below the Average Price...
Click to expand...
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)))
 
Upvote 0
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.



Mathman said:
[TABLE="width: 174"]
<tbody>[TR]
[TD="class: gmail-xl64, width: 87"]Average[/TD]
[TD="class: gmail-xl65, width: 87, align: right"] $3.73[/TD]
[/TR]
[TR]
[TD="class: gmail-xl63"][/TD]
[TD="class: gmail-xl63"][/TD]
[/TR]
[TR]
[TD="class: gmail-xl64"]Date[/TD]
[TD="class: gmail-xl64"]Pice[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]23-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $4.20[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]20-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $4.13[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]19-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $4.03[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]18-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $3.90[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]17-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $3.80[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]13-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $3.90[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]12-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $4.09[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]11-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $3.76[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]10-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $3.74[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]9-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $3.85[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]6-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $3.71[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]5-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $3.98[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]4-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $3.71[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]3-Jan-17[/TD]
[TD="class: gmail-xl65, align: right"] $3.38[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]30-Dec-16[/TD]
[TD="class: gmail-xl65, align: right"] $3.14[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]29-Dec-16[/TD]
[TD="class: gmail-xl65, align: right"] $3.24[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]28-Dec-16[/TD]
[TD="class: gmail-xl65, align: right"] $3.44[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]27-Dec-16[/TD]
[TD="class: gmail-xl65, align: right"] $3.46[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]23-Dec-16[/TD]
[TD="class: gmail-xl65, align: right"] $3.50[/TD]
[/TR]
[TR]
[TD="class: gmail-xl66, align: right"]22-Dec-16[/TD]
[TD="class: gmail-xl65, align: right"] $3.57[/TD]
[/TR]
[TR]
[TD="class: gmail-xl63"][/TD]
[TD="class: gmail-xl63"][/TD]
[/TR]
[TR]
[TD="class: gmail-xl67"]22-Dec-16[/TD]
[TD="class: gmail-xl68"]23-Jan-17[/TD]
[/TR]
[TR]
[TD="class: gmail-xl64"]Date[/TD]
[TD="class: gmail-xl64"]Date[/TD]
[/TR]
[TR]
[TD="class: gmail-xl69"]>=42726[/TD]
[TD="class: gmail-xl69"]<=42758[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Mathman said:
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.
Click to expand...
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.
 
Upvote 0
that really would be great if a version using DSUM'S were possible!
 
Last edited:
Upvote 0
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

[TABLE="width: 468"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SUM(F4+1)
[/TD]
[TD]B4
[/TD]
[TD]average
[/TD]
[TD]Date
[/TD]
[TD]Date
[/TD]
[TD]average
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]"<="&(C2)
[/TD]
[TD]">="&(E2)
[/TD]
[TD]"<="&(F2)
[/TD]
[TD]">="&(C2)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]EDATE(F2,-3)
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]">"&(DMAX(B3:C300,B3,H2:J3))
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]">"&(DMAX(B3:C300,B3,G2:I3))
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Result
[/TD]
[TD="colspan: 3"]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)
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 489"]
<tbody>[TR]
[TD]Set Up[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Average ->
[/TD]
[TD] $1.954
[/TD]
[TD][/TD]
[TD]24-Oct-16
[/TD]
[TD]23-Jan-17
[/TD]
[TD]average
[/TD]
[TD]Date
[/TD]
[TD]Date
[/TD]
[TD]average
[/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]Average
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]<=1.95412698412698
[/TD]
[TD]>=42667
[/TD]
[TD]<=42758
[/TD]
[TD]>=1.95412698412698
[/TD]
[/TR]
[TR]
[TD]23-Jan-17
[/TD]
[TD] $1.660
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]23-Oct-16
[/TD]
[TD]
[/TD]
[TD]Date
[/TD]
[TD]Date
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20-Jan-17
[/TD]
[TD] $1.720
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19-Jan-17
[/TD]
[TD] $1.710
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]18-Jan-17
[/TD]
[TD] $1.810
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]>42738
[/TD]
[TD]>42758
[/TD]
[/TR]
[TR]
[TD]17-Jan-17
[/TD]
[TD] $1.870
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16-Jan-17
[/TD]
[TD] $1.840
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Result
[/TD]
[TD]-14
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13-Jan-17
[/TD]
[TD] $1.840
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-Jan-17
[/TD]
[TD] $1.810
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11-Jan-17
[/TD]
[TD] $1.900
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10-Jan-17
[/TD]
[TD] $1.840
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9-Jan-17
[/TD]
[TD] $1.860
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6-Jan-17
[/TD]
[TD] $1.870
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5-Jan-17
[/TD]
[TD] $1.900
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4-Jan-17
[/TD]
[TD] $1.950
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3-Jan-17
[/TD]
[TD] $1.970
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30-Dec-16
[/TD]
[TD] $1.930
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29-Dec-16
[/TD]
[TD] $1.960
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28-Dec-16
[/TD]
[TD] $1.980
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23-Dec-16
[/TD]
[TD] $1.920
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22-Dec-16
[/TD]
[TD] $2.080
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21-Dec-16
[/TD]
[TD] $2.190
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20-Dec-16
[/TD]
[TD] $2.170
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
INDEX and MATCH formula for rainfall cessation
Replies
6
Views
300
Eric W
Eric W
F
  • Solved
XLOOKUP and ignoring blanks
Replies
8
Views
2K
SanjayGMusafir
SanjayGMusafir
JenniferMurphy
  • Question Question
How to synchronize two tables?
Replies
0
Views
343
JenniferMurphy
JenniferMurphy
R
  • Solved
Very Complex- How to Find Stock Market Probability of 3, 4 & 5 negative days (Find Results of Day 4,5&6)
Replies
8
Views
613
rebel123
R
skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
571
skyh3ck
skyh3ck

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top