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.