find max of a repeating dynamic range in Excel

This may have a simple solution which I haven't found yet. but here's the situation.

I have a data in Excel sheet:

This is a log file generated from simulations. The simulation is run tens of times (variable) and each run generates one block starting at "-------------------" and ending before the next "-----------------" divider. The number of rows between these dividers is variable but certain things are fixed. the number and order of columns and the first row & cell being the divider, the next row in the same column having date stamp, the next row having column headings. the divider开发者_C百科 and date stamp are contained in only 1 cell.

What I need to do is mind the MAX of CNT & SIM_TIME for each simulation run. I will then take the average of these. I only need to do this for the "Floor 1" table from the screenshot.

What's the best way to proceed? which functions should I use? (I have Office 2010 if that has new functions not present in 2007)

---

General approach, by example:

Data sheet: Sheet1

Results on seperate sheet: Sheet2

Number of rows in data: Cell F2
=COUNTA(Sheet1!B:B)

Intermediate result, Row of data set Cell A3
=MATCH(Sheet1!$B$1,OFFSET(Sheet1!$B$1,A2,0,$F$2),0)+A2

Intermediate result, row of next data Cell B3
=IF(IFERROR(N(A4),0)=0,IF(ISNA(A3),"",$F$2),A4)

Max of CNT data set, Cell C3
=IF(B3<>"",MAX(OFFSET(Sheet1!$B$1,$A3+2,0,$B3-$A3-3)),"")

Max of SIM_TIME, Cell D3
=IF(C3<>"",MAX(OFFSET(Sheet1!$B$1,$A3+2,3,$B3-$A3-3)),"")

Date from data set
=IF(D3<>"",OFFSET(Sheet1!$B$1,$A3,),"")

To expand to give results for all available data, copy range C3:E3 down for as many rows as are in data. any extra rows will show N/A in column A and blanks in others

Screen shot of results:

Screen Shot of formulas:

---

I am not sure i got what you want to do.

Perhaps something like this would work, although it is not automatic. I am making the assumption that the last value of each simulation is the MAX value.

1. Put the following formula at cell "I4" =if(B5 = "---------" ; B4 ; "")
2. Pull the cell formula down till the last row of "Floor 1"
3. Calculate the average =average(I:I). Don't put this type on column I!!!

Notes

1. use as many "-" as there are at cell B22
2. you may want to insert a new column between I and J, in order to average SIM_TIME. The procedure is the same. Only the cells change.
3. You could easily automate this procedure a little bit with macros.