Condensing financial data

[H28Sailor]

Hi



I have one minute financial data of the form:-

20010102, 230300, OP, HP, LP, CP

where OP = Open Price , HP = High Price, LP = Low Price, CP = Close Price
and Date = YYYYMMDD, and Time = HHMMSS

However, some minute data does not exist i.e. there are a variable number of minute data to make up each hour

I would like to condense the minute data into hourly data such that

(i) OP becomes OP for 1st available minute data for that hour
(ii) HP becomes max(of all available HP's) for that hour span
(iii) LP becomes min(of all available LPs) for that hour span
(iv) CP becomes CP for last available minute data for that hour

Any suggestions for the appropriate formulae gratefully received

Thank you

Bob M

p.s. if the fields were separated into their own columns and there were say 6 rows per minute I could use the following:-
OP: =OFFSET(Sheet1!C$2,(ROW()-1)*6-6,0)
HP: =MAX(OFFSET(Sheet1!D$2,(ROW()-1)*6-6,0,6,1))
LP: =MIN(OFFSET(Sheet1!E$2,(ROW()-1)*6-6,0,6,1))
CP: =OFFSET(Sheet1!F$2,(ROW()-1)*6-1,0)

but I do not know how to accommodate the variable number of rows per minute