automating an array?
 

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

One play which might work ..

For the col J sample data, let's just take J2:J16, which covers the first 2
"batches" (disregard cell J17, which is the starting cell of the 3rd batch)

Using 2 empty cols to the right, say cols K & L

Put in K2: =COUNTIF($J$2:J2,"Result")
Copy K2 down until the last blank in col J, i.e. till K16

Col K will mark it out nicely as to the different "batches",
i.e. the lines in-between one "RESULT" to the next "RESULT"

Now we could put in say L2, and array-enter,
i.e. press CTRL+SHIFT+ENTER:

=MAX(IF($K$2:$K$16=ROWS($A$1:A1),$J$2:$J$16))

and copy L2 down until zeros appear,
signalling exhaustion of calcs for col J

For the sample data in J2:J16,
L2 will return the max for the 1st batch: 937
L3 will return the max for the 2nd batch: 983
and so on ..

Adapt the ranges $K$2:$K$16, $J$2:$J$16 to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"jaimetimbrell"
wrote in message
news:jaimetimbrell.1tl8qg_1123743912.782@excelforu m-nospam.com...

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

Max,

I am sorry but I am obviously being a bit dim. It didnt work but then
that is probably how I have adapted your advice.

I dont know what the $A$1:A1 refers to or what ctrl shift enter does or
when/how to do it?

I think I need a beginners class!

JAime.


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

Maybe take a look at a sample file with the implemented construct? :
http://www.savefile.com/files/9609633
File: Automating_an_array_jaimetimbrell_misc.xls

.. what ctrl shift enter does or when/how to do it?

Pressing CTRL+SHIFT+ENTER
is a special way of entering array formulas
(after we have pasted the formula into the formula bar)

For normal formulas, we just press ENTER to confirm the entry,
but for array formulas we need to press CTRL+SHIFT+ENTER
(Hold down the CTRL + SHIFT keys, press ENTER)

If correctly done, Excel will then insert curly braces { }
around the array formula

For e.g. in L2 in the sample file, if you look closely,
the formula appears in the formula bar as:
{=MAX(IF($K$2:$K$16=ROWS($A$1:A1),$J$2:$J$16))}

Don't worry about the "A$1:A1" etc for now

Hope the above and the sample file helps ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"jaimetimbrell"
wrote in message
news:jaimetimbrell.1tnbsl_1123841191.875@excelforu m-nospam.com...

Max,

I am sorry but I am obviously being a bit dim. It didnt work but then
that is probably how I have adapted your advice.

I dont know what the $A$1:A1 refers to or what ctrl shift enter does
or when/how to do it?

I think I need a beginners class!

JAime.


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

Max,

I have just returned from a weekend away from my pc, and have returned
to this fantastic additional advice and very haelpful save file.

I have been through your advice again and the penny has dropped.

Thank you so, so, much for sticking with me and explaining things
again.

This has helped me greatly and I am indebted to your kind attention to
my problem and helping me to move on in my knowledge and solving this
problem.

I only hope that I can help you in some other matter - but given the
gulf in our respective knowledge of excel I dont think it will be on
this forum.

Thank you again.
Jaime.


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

You're welcome, Jaime ! Glad it helped.
The feedback is appreciated ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--