Hello
I am working on some technical analysis for share price data and would be very grateful for a solution to a problem I have.
My raw data is a download of two-weeks of daily data which contains a row for each minute of the day, so lots of data! Each row displays the following information:
Column A: Date and time (in the format dd:mm:yyyy hr:mm). There is a row for every minute from 0800hrs to 1630hrs
Column B: Open Price
Column C: High Price
Column D: Low Price
Column E: Close Price
In my analysis I want to identify the highest price recorded during a range of times. E.g. the highest price between 1000 hrs and 1500 hrs on a particular day. I also want to know the lowest price.
I am familiar with using Max and Min formulas. I have also tried the following formula:
=MAXA(IF(AND(A2:A10000>=H2,A2:A10000<=I2),C2:C10000))
Where:
A2:A10000 is where the raw data is stored for the date and time
H2 is a cell containing the start time of my choice, e.g. 01/08/2011 10:00
I2 is a cell containing the end time of my choice, e.g. 01/08/2011 15:00
C2:C10000 is the range of cells containing "High Price" data
The problem with the formula is that it returns a result of "0" because the IF and AND functions are not met as my raw data spans across multiple days.
I am now stuck and would be very happy if someone has a solution please!
Thank you
David
I am working on some technical analysis for share price data and would be very grateful for a solution to a problem I have.
My raw data is a download of two-weeks of daily data which contains a row for each minute of the day, so lots of data! Each row displays the following information:
Column A: Date and time (in the format dd:mm:yyyy hr:mm). There is a row for every minute from 0800hrs to 1630hrs
Column B: Open Price
Column C: High Price
Column D: Low Price
Column E: Close Price
In my analysis I want to identify the highest price recorded during a range of times. E.g. the highest price between 1000 hrs and 1500 hrs on a particular day. I also want to know the lowest price.
I am familiar with using Max and Min formulas. I have also tried the following formula:
=MAXA(IF(AND(A2:A10000>=H2,A2:A10000<=I2),C2:C10000))
Where:
A2:A10000 is where the raw data is stored for the date and time
H2 is a cell containing the start time of my choice, e.g. 01/08/2011 10:00
I2 is a cell containing the end time of my choice, e.g. 01/08/2011 15:00
C2:C10000 is the range of cells containing "High Price" data
The problem with the formula is that it returns a result of "0" because the IF and AND functions are not met as my raw data spans across multiple days.
I am now stuck and would be very happy if someone has a solution please!
Thank you
David
