Let me give that a shot (i'm trying to follow your formula and need to plug
it into my dataset)...thanks so much for the work on my behalf....and as for
the conditions, that's no problem at all....The dataset is the result of a
background subtraction and interpolation method which means that the
beginning and end rows would always have to be "0"

"T. Valko" wrote:

Formulas will work. It's just how long it takes to calculate. I've managed
to get a non-array formula to work but it has conditions:

The data must not start on row 1
The last value of the data in column C must be either a 0 or there must be
an empty row at the end of the dataset.

Screencap:

http://img254.imageshack.us/img254/9584/maxrangeqa6.jpg

This just indentifies the max abs value for each group and returns that
value to the beginning of each group. If you just want to "flag" the
specific cell that could probably be done.


--
Biff
Microsoft Excel MVP


"David Hilberg" wrote in message
news:d0aqi.3390$0v4.540@trndny01...
In your example, five non-zero values are separated by more than five
zeroes. If this is a rule, perhaps formulas will work If not, I believe
VBA is necessary.

- David

ExcelHelpPlease wrote:
Yeah, so i have about 85,000 rows on a small dataset......so I need to
learn VBA?

"David Hilberg" wrote:

Using formulas alone (no VBA), you might need four auxiliary columns -
one to figure the first cell in the range, one to figure the last, one
for the absolute max, and one to flag the row(s) of the max. For more
than a few hundred rows, however, I would not recommend that approach.

- David

ExcelHelpPlease wrote:
I have a column (C) with values in it ranging from -5000 to zero to
5000. Reading down the column, it is something like this:
0,0,0,0,0,0,0,0,-4000,-4010,-4020,-4010,-4000,0,0,0,0,0,0,0,50,60,70,60,50,0,0,0,0,0,0,0,0.
What I would like to do is to specify that a search which doesn't do
anything at the zeroes, but when it hits a number, it spits out C9:C13
in a cell to the right of the first non-zero number (in this example at
D9) and then continues down the list until it hits "50" at C21. At
D21, I want it to spit out a range of D21:D25...and then continue down.
Ultimately, I will want to pick the largest absolute values from these
ranges, and then reference other numbers on the row which corresponds
to the largest value in that range. Is this at all possible? Thanks
in advance for any help.