- Joined
- Aug 10, 2005
- Messages
- 17
- Reaction score
- 0
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 3 452 =MAX(J3:J6) result 937
row 4 653
row 5 937
row 6 932
row 7 "blank"
row 8 487 =MAX(J8:J14) result 983
row 9 665
row 10 344
row 11 958
row 12 234
row 13 567
row 14 983
row 15 "blank"
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 8 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 J8:J14 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.
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 3 452 =MAX(J3:J6) result 937
row 4 653
row 5 937
row 6 932
row 7 "blank"
row 8 487 =MAX(J8:J14) result 983
row 9 665
row 10 344
row 11 958
row 12 234
row 13 567
row 14 983
row 15 "blank"
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 8 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 J8:J14 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.