Hello all,

I have been driven to near dementure by a problem that I am sure has a
simple solution (but then all problems are easy when the solution is to
hand!)

I have a spreadsheet, about 5000 rows by 20 columns of data that has
been imported from a *.txt file. The data is separated into events that
take place separately and I want to treat each event as a unique array
and look at each event separately - it is all vertically stacked when
looking at the spreadsheet.

one of the columns contains 3 digit numeric values and I want to run a
MAX(array) formula, but I am having a problem constructing an
expression that starts at the top of each column of data in each unique
event and stops at the bottom of that unique event, by way of
illustration:

column J
row 2 RESULT
row 3 452 MAX(J3:J6) result 937
row 4 653
row 5 937
row 6 932
row 7 "blank"
row 8 RESULT
row 9 487 MAX(J9:J15) result 983
row 10 665
row 11 344
row 12 958
row 13 234
row 14 567
row 15 983
row 16 "blank"
row 17 RESULT
etc

How do I construct a formula that starts at row 3 and stops at row 6,
calculates the MAX figure and then starts again at row 9 etc - the next
separate event? In other words how do I automate construction of the
array in each of the MAX formulae above (J3:J6 and J9:J15 etc)?

(note: I have 100 spreadsheets of about 5000 rows so manually
transposing or manipulating the data isnt an option open to me)

Any help would be most gratefully received.

Best Regards,
Jaime.


--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...o&userid=26162
View this thread: http://www.excelforum.com/showthread...hreadid=394847