automating an array?

  • Thread starter Thread starter jaimetimbrell
  • Start date Start date
J

jaimetimbrell

Hello all,

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

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

one of the columns contains 3 digit numeric values and I want to run
MAX(array) formula, but I am having a problem constructing a
expression that starts at the top of each column of data in each uniqu
event and stops at the bottom of that unique event, by way o
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 nex
separate event? In other words how do I automate construction of th
array in each of the MAX formulae above (J3:J6 and J9:J15 etc)?

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

Any help would be most gratefully received.

Best Regards,
Jaime
 
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 ..
 
Max,

I am very very thankful for your helpful comments and proposed solution - I will try this a little later when I return home from work - my boss is constantly looking over my shoulder and the spreadsheet isnt what he would consider to be work...

Thank you again,
Jaime.
 
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.
 
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.
 
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?
Click to expand...

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 ..
 
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.
 
Back
Top