Hi again,
I have been away for a few weeks. I appreciate the assistance and have managed to 'combine' all of the suggestions from Jason/John and AliGW to come up with a formula that appears to work. The one issue with Johns 'formula' was the value '5' . This is fine if there are 5 rows or cells of data in the block. However if this number changes to 3 or even 1 then that formula will not work. Nevertheless I have adapted this formula with the following (pasted into B1).
=IFERROR(AVERAGE(OFFSET(N2,MATCH("1",N2:N200,0),,(MATCH("2",N2:N200,0)-MATCH("1",N2:N200,0)-2))),"")
The blocks of data to consider are in column 'N'. I have deliberately left out '$' because in my actual spreadsheet I need to copy column B to other parts of the spreadsheet. Also the above formula in B1 cannot be copied down the column because the markers "1", "2" etc, need to change in each row. Hence the formula in B2 will be:
=IFERROR(AVERAGE(OFFSET(N2,MATCH("2",N2:N200,0),,(MATCH("3",N2:N200,0)-MATCH("2",N2:N200,0)-2))),"")
Naturally this gets quite tedious if there a 'millions' of rows but fortunately I have a maximum of 20 rows so the task is not too bad. The formula may not be elegant and I am sure there are folk out there who could modify it in case one does have 'millions' of rows.
I attach the actual part of the spreadsheet
Thanks again
Chris
Bookmarks