high and low from data range

[censura]

Hi guys, hope you can help, I have a spread sheet with the following rows headers

date / time, open price, close price, high, low


this data is ft100 uk stock exchange data for 5min bars ie the open ,close, high, low 8.10 _8.15 bar, 8.15-8.20 bar

eg

11/11/2013 8.10.00, 6685, 6679, 6691, 6682

for each day there are 102 bars / rows of data

what i want to calculate is the high and low for each day (101) bars from 8.05 to 4.30pm to form 3 new rows of data - date, high, low for each day

For those of you familiar with stock exchange indices you may be asking that you can get this data from other sources , the issue is that the opennprice at 8.00 and high and low is not a reflection of the data in that 5min timeframe rather is includes trading out of hrs from 16.31-7.59 and gives a false high ajd low for the day.


Thank you

[rishikrsaw]

u can use pivot table for summarize ur data or send me ur dummy sheet I'll be pivoting for u.........


thanks
R

[censura]

Hi not sure why but spread sheet does not show times, it is not too important for the purposes of you helping with the template as I can add the correct data with times into your template.

The main thing is if you use 2nd row of each day (8.05) bar as opposed to the first bar (8.00) till the last bar of the day (4.30) for the range calculation that would work . It would be more robust if it picks 8.05 bar rather than just 2nd bar as I might want to try it on different time frames ie 1min not just 5min bars

Thank you

[censura]

anyone else can help