I'm trying to convert 1 minute OHLC (Open/High/Low/Close) data into 5 minute OHLC data in Excel 2013. So far I know the principle. Open
has to take the open value every 5 rows, similarly for Close
. Min
/Max
is also understandable. Unfortunately Excel can't understand that I want to get Min
/Max
from rows 0-5, 5-10 etc. it goes 0-5, 1-6, 2-7 etc.
I was also trying to use AVERAGE somehow but it's pointless since its output doesn't correspond with reality at all. From some more research I think I will have to create a macro from functions AVERAGE, OFFSET, INDEX and MATCH and that's where my struggle begins. I have no idea how to construct that formula.
Here's a picture of how it looks after using Filter on the Count Column:
Add a column (assumed to be A) on the left with 0
as a label and =IF(MOD(B2,6)=0,1+A1,A1)
in A2 copied down. Subtotal for each change in 0
and use Count on all the other columns. Change the first subtotal row formulae to be:
ColumnC: =C2
ColumnD: =SUBTOTAL(4,D2:D6)
ColumnE: =SUBTOTAL(5,E2:E6)
ColumnF: =F6
Filter for ColumnA contains c
and copy formulae down.
Edit