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.

If I understand what you want try this array formula** :

=MAX(IF(C1:C100<0,ABS(D1:D100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"ExcelHelpPlease" wrote in
message ...
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.

I believe your solution would just return one value for the entire
column....I would like a new value for each "group of numbers-why I am asking
to automatically create ranges" which is separated by zeroes....if that makes
sense. So basically If I ran your formula, I would need a way to break the
operation after a zero is identified, and then restart it for the next group
of < zero values.

"T. Valko" wrote:

If I understand what you want try this array formula** :

=MAX(IF(C1:C100<0,ABS(D1:D100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"ExcelHelpPlease" wrote in
message ...
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.

Ultimately, I will want to pick the largest absolute values from these
ranges

So you want the largest value from *each* of the individual ranges?

How about posting a more detailed sample with the expected result.

--
Biff
Microsoft Excel MVP


"ExcelHelpPlease" wrote in
message ...
I believe your solution would just return one value for the entire
column....I would like a new value for each "group of numbers-why I am
asking
to automatically create ranges" which is separated by zeroes....if that
makes
sense. So basically If I ran your formula, I would need a way to break
the
operation after a zero is identified, and then restart it for the next
group
of < zero values.

"T. Valko" wrote:

If I understand what you want try this array formula** :

=MAX(IF(C1:C100<0,ABS(D1:D100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"ExcelHelpPlease" wrote in
message ...
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.

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.

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.

I can get the max for each range but it requires an array formula copied to
each row.

i have about 85,000 rows on a small dataset

Well, that's a killer! <full stop!

--
Biff
Microsoft Excel MVP


"ExcelHelpPlease" wrote in
message ...
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.

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.

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.

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.

I wondered if you might have a further look at my problem.....Your formula
(Biff) seemed to work well, but now I am getting errors (N/A Values
intermittently) and I don't know why. In addition, I figured I might send
the rest of my requirements and see whether you could do anything more with
it. If you can't that's completely fine and you have started me on the right
track and I truly appreciate your help.

Within Column C, If I have a group of positive values (as you have
identified with your array creation formula), I give the group a label of
"+M" and if negative, then "-M"..but those formulas need some more "If
statements" because if the values flip once and go positive to negative or
negative to positive (before hitting zero) then the label is actually "Di"
however, if the numbers switch multiple times before going to zero (positive,
negative, Positive, Negative, etc or the reverse) then the label is "CD."
For the "Di" or "CD" values, my "Feature Measurement" also is more
complex....basically since the numbers go from positive to negative, my
feature measurement is the sum of the absolute values of the largest positive
and the smallest (or abs...so largest) negative within the group-it is the
overall amplitude of the function.


Is there any hope for this?

Thanks in advance,


"ExcelHelpPlease" wrote:

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.

I'm using Excel 2002. I can't handle 85,000 rows of data!

Your additional requirements sound extremely complex. Perhaps Excel isn't
the best tool for this or, at this point, you need to investigate a
programmed (VBA) soluition.

--
Biff
Microsoft Excel MVP


"ExcelHelpPlease" wrote in
message ...
I wondered if you might have a further look at my problem.....Your formula
(Biff) seemed to work well, but now I am getting errors (N/A Values
intermittently) and I don't know why. In addition, I figured I might send
the rest of my requirements and see whether you could do anything more
with
it. If you can't that's completely fine and you have started me on the
right
track and I truly appreciate your help.

Within Column C, If I have a group of positive values (as you have
identified with your array creation formula), I give the group a label of
"+M" and if negative, then "-M"..but those formulas need some more "If
statements" because if the values flip once and go positive to negative or
negative to positive (before hitting zero) then the label is actually "Di"
however, if the numbers switch multiple times before going to zero
(positive,
negative, Positive, Negative, etc or the reverse) then the label is "CD."
For the "Di" or "CD" values, my "Feature Measurement" also is more
complex....basically since the numbers go from positive to negative, my
feature measurement is the sum of the absolute values of the largest
positive
and the smallest (or abs...so largest) negative within the group-it is the
overall amplitude of the function.


Is there any hope for this?

Thanks in advance,


"ExcelHelpPlease" wrote:

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.