Array or other Formulas to Determine Price and Volume Count

[Steve=True]

Hi, I have a set of data.

Date........Price........Volume
7am.........99............10
701am......98.............2
702am......98.5..........12
etc.
Record set of 46k rows so looking for the most efficient formula.

I want to graph the volume shifts but struggling with an efficient formula to do these things:

If we set a price watch target of $10

A) Sum the volume every time the data shifts more than or equal to the price watch target of $10

Example
Row2= Price at 7am = 99
Row56=Price at 756am = 109
Sum of Volume from 7am-756am = 479

Row56=Price at 756am = 109
Row402=Price at 902am = 119
Sum of Volume from 7am-756am = 961

Row402=Price at 902am = 109
Row421=Price at 915am = 109
Sum of Volume from 7am-756am = 1461

I want to plot a graph of this data

xaxis 756am.....479 volume (datapoint)
xaxis 902am.....961 volume (datapoint)
xaxis 915am.....1461 volume (datapoint)

Thanks in advance!

 

[DonkeyOte]

If I've understood, perhaps something along the lines of:

Excel Workbook
	A	B	C	D
1	Time	Price	Volume	Chart
2	07:00	99	22	#N/A
3	07:01	98	27	#N/A
4	07:02	93	41	#N/A
5	07:03	98	27	#N/A
6	07:04	95	38	#N/A
7	07:05	97	47	#N/A
8	07:06	98	6	#N/A
9	07:07	101	42	#N/A
10	07:08	103	7	#N/A
11	07:09	104	45	#N/A
12	07:10	109	12	314
13	07:11	109	47	#N/A
14	07:12	107	1	#N/A
15	07:13	110	36	#N/A
16	07:14	113	36	#N/A
17	07:15	118	35	#N/A
18	07:16	123	50	205
19	07:17	122	22	#N/A
20	07:18	120	9	#N/A
Sheet1

 

[Steve=True]

Thanks much Donkeyote, you are wonderful and that formula will work, however, i may run into a calculation problem as this formula needs to be copied down all 45,000 rows.

I would probably look for the first X number of occurances between certain time ranges. (lets say 50 points total)

Any thoughts on how i can optimize the spreadsheet to only calculate so many data points between the time range?

Steve

 

[DonkeyOte]

The formula is relatively efficient... it should be noted that the SUMIF section will only be applied where the volume variance exceeds permitted boundaries (10)

If you wish to apply the test only a given number of times you can add a pre-emptive COUNT:

D2:
=IF(COUNT($D$1:$D1)=50,NA(),IF(ABS($B2-INDEX($B:$B,IF(COUNT(D$1:D1)=0,2,MATCH(9.99E+307,$D$1:$D1))))<10,NA(),SUM($C$2:$C2)-SUMIF($D$1:$D1,"<>#N/A")))

If you wish to remove the formulae altogether you may be best served using VBA and subsequently working with VBA Arrays.