I want to find the high value from daily data for each week and copy that value.
I have a list of daily price data, being the date, opening price, high price, low price and closing price. For each week I am finding the data necessary to create a list of the weekly opening price, high price, low price and closing price ending on each Friday.
To find the opening price I am using this formulae -
=INDIRECT(ADDRESS(MATCH(J2,$A$1:$A$1400,0)+1,COLUMN(B1)))
To find the closing price the formulae is -
=INDIRECT(ADDRESS(MATCH($J$3,$A$1:$A$1400,0),COLUMN(E2)))
I need to find the high value and the low value within the five days data, from two separate columns (C and D) of daily data.
Can I use a formula similar to those above, but instead of copying a relevant cell, for example, finding the high from cells B3 to B7. Then I need to do this for each subsequent week.
An example spreadsheet is attached. You will see that currently I am currently copying the Friday data in columns L and M as I don't know how to adapt the formula to do the job.