Need Formula for Data that Shifts When It Updates

Simplified Rules and Table to Better Illustrate My Desired Outcomes

Data shifts and is not static so formulas have to be based on the "X"s. Please see updated spreadsheet for a better understanding.

All of the formulas are entered in columns K3:Q10

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	#	DATE	TIME	OPEN	HIGH	LOW	CLOSE	VOL	MARK	#	DATE	TIME	OPEN	HIGH	LOW	CLOSE	VOL
2								x
3	1000	4/3/2014	1:00	1.3764	1.3765	1.3760	1.3764	684		1	4/3/2014		1.3764	1.3765	1.3760	1.3764	684
4	999	4/3/2014	1:15	1.3764	1.3764	1.3760	1.3761	780		1	4/3/2014		1.3764	1.3765	1.3760	1.3761	1464
5	998	4/3/2014	1:30	1.3764	1.3765	1.3760	1.3764	684		1	4/3/2014		1.3764	1.3765	1.3760	1.3764	2148
6	997	4/3/2014	1:45	1.3764	1.3764	1.3760	1.3761	780		1	4/3/2014		1.3764	1.3765	1.3760	1.3761	2928
7	996	4/3/2014	2:00	1.3761	1.3766	1.3759	1.3765	709		1	4/3/2014		1.3764	1.3766	1.3759	1.3765	3637
8	995	4/3/2014	2:15	1.3766	1.3769	1.3765	1.3765	405		1	4/3/2014		1.3764	1.3769	1.3759	1.3765	4042
9	994	4/3/2014	2:30	1.3765	1.3768	1.3764	1.3765	411		1	4/3/2014		1.3764	1.3769	1.3759	1.3765	3769
10	993	4/3/2014	2:45	1.3765	1.3766	1.3763	1.3765	218	X	1	4/3/2014	2:45	1.3764	1.3769	1.3759	1.3765	4671

All of the fomulas are entered in columns K3:Q10

K3:K10 DATE: Either the current date for that row if above lower "X" or the date at "X"

L3:L10 TIME: The time for the "X" in that row or the "X" above it.

M3:M10 OPEN: Is the first open between the nearest top two "X"s

N3 HIGH: First row at or above "X"s
N4 HIGH: MAX Between 1st and 2nd row at or above two "X"s
N5 HIGH: MAX Between 1st through 3rd row at or above two "X"s
N6 HIGH: MAX Between 1st through 4th row at or above "X"s
N7 HIGH: MAX Between 1st through 5th row at or above "X"s
N8 HIGH: MAX Between 1st through 6th row at or above "X"s
N9 HIGH: MAX Between 1st through 7th row at or above "X"s
N10 HIGH: MAX Between 1st through 8th row at or above "X"s

O10 LOW: First row at or above "X"s
O11 LOW: MIN Between 1st and 2nd row at or above "X"s
O12 LOW: MIN Between 1st through 3rd row at or above "X"s
O13 LOW: MIN Between 1st through 4th row at or above "X"s
O14 LOW: MIN Between 1st through 5th row at or above "X"s
O15 LOW: MIN Between 1st through 6th row at or above "X"s
O16 LOW: MIN Between 1st through 7th row at or above "X"s
O17 LOW: MIN Between 1st through 8th row at or above "X"s

P3:P10 CLOSE: Either the current Close for that row if above lower "X" or the Close at "X"

Q3:Q10 VOLUME: Add the vaules of current row plus the values above until the upper "X"


Any help that can be provided for any of the formulas I need would be greatly appreciated Thank you.

Any help that can be provided for any of the formulas I need would be greatly appreciated Thank you.

Originally Posted by AliGW

I think you'd get a better response if you provided a sample workbook instead of expecting would-be helpers to spend time replicating your data.

Hello Ali,
Thank you for the response. I provided a sample workbook in Post#1 and Post#2. I created the table to just to further illustrate what I'm trying to accomplish. See post #2 or this post for the most updated workbook.

G’day Ratcat and thank you for replying. I’d greatly appreciate it if you made a go at this challenge even if you only helped find the solution to just one of the formulas I need.

I would need the whole 24hrs not just a snapshot. If you have any other questions feel free and ask.

Thanks again.

G'day Ratcat,
Rough is an understatement. I'm going to review it and test everything but before I do I wanted to say thank you for using some of your time and energy to helping me resolve this problem. Thank you.

G'day Ratcat,
So I made some changes to the workbook you uploaded and it works fine with the current data but when I pasted in the complete data set all of the formulas are out of whack.

Also, can you tell where I can edit [db!] in the Name Manager? I see the Refers To but I can’t figure out what it refers to. I think that’s what needs to be changed.

I’m attaching two spreadsheets one titled shifting_data_5 which has the changes to the workbook you uploaded and shifting_data_6 which is another workbook that contains your formulas but with updated data. The second workbook is where the results are all out of whack. See the shifting_data_5 for the changes and for updates to the rules and a better understanding of what I'm trying to accomplish.

Thank you again for assisting me with this Ratcat. It’s greatly appreciated.

Any help that can be provided for any of the formulas I need would be greatly appreciated Thank you.