Col A through D contain the data. Col E contains the formula I'm asking
about:
Col A Col B Col C Col D Col E
7 6 7 2 1
4 3 2 1 4
8 10 9 8 -
5 3 2 10 3

I'd like to Col E to count consecutive improvements for each row of data
starting in Col A relative to Col B, then Col B relative to Col C.

Is there a way to do this?

Thanks a lot!

This should do it for you, at least it works with your test cases.
Enter the formula out in column E, then extend the formula down the sheet
this assumes your first row is at 2
=IF(A2B2,1,0)+IF(C2<B2,IF(A2B2,1,0),0)+IF(B2C2, IF(A2B2,1,0),0)+IF(C2D2,IF(B2C2,IF(A2B2,1,0),0 ),0)

JLatham

"SteveC" wrote:

Col A through D contain the data. Col E contains the formula I'm asking
about:
Col A Col B Col C Col D Col E
7 6 7 2 1
4 3 2 1 4
8 10 9 8 -
5 3 2 10 3

I'd like to Col E to count consecutive improvements for each row of data
starting in Col A relative to Col B, then Col B relative to Col C.

Is there a way to do this?

Thanks a lot!

Hi, it works great as is. Thank you!

As a follow up, how could I expand it to 8 columns A through H?

And if you have time, how do you go about the process of creating all these
nested functions... I tried it a few times and screwed it up.

Thanks so much!


"JLatham" wrote:

This should do it for you, at least it works with your test cases.
Enter the formula out in column E, then extend the formula down the sheet
this assumes your first row is at 2
=IF(A2B2,1,0)+IF(C2<B2,IF(A2B2,1,0),0)+IF(B2C2, IF(A2B2,1,0),0)+IF(C2D2,IF(B2C2,IF(A2B2,1,0),0 ),0)

JLatham

"SteveC" wrote:

Col A through D contain the data. Col E contains the formula I'm asking
about:
Col A Col B Col C Col D Col E
7 6 7 2 1
4 3 2 1 4
8 10 9 8 -
5 3 2 10 3

I'd like to Col E to count consecutive improvements for each row of data
starting in Col A relative to Col B, then Col B relative to Col C.

Is there a way to do this?

Thanks a lot!

Also, saw this formula in the IF(C2<B2,IF(A2B2,1,0),0) and noticed you
didn't repeat it (you use the "<" sign instead of the "" sign and you use
the "sign" for all the others. What does this accomplish with the overall
formula.

By the way, I extended the formula you created to this:
=IF(M18N18,1,0)+IF(O18<N18,IF(M18N18,1,0),0)+IF( N18O18,IF(M18N18,1,0),0)+IF(O18P18,IF(N18O18,I F(M18N18,1,0),0),0)+IF(P18Q18,IF(O18P18,IF(N18 O18,IF(M18N18,1,0),0),0),0)+IF(Q18R18,IF(P18Q18 ,IF(O18P18,IF(N18O18,IF(M18N18,1,0),0),0),0),0) +IF(R18S18,IF(Q18R18,IF(P18Q18,IF(O18P18,IF(N1 8O18,IF(M18N18,1,0),0),0),0),0),0)+IF(S18T18,IF (R18S18,IF(Q18R18,IF(P18Q18,IF(O18P18,IF(N18O 18,IF(M18N18,1,0),0),0),0),0),0),0)

And it works. But I'm wondering if it's accurate for all cases because I'm
not sure what the < sign in the second nested formula does and why it's not
repeated through the rest of the formula...

Thanks a lot for your time!

Also wondering if there is an efficient way to use
=if(iserror(bigformula,0,bigformula) -- do I really have to input that big
formula below twice?

thanks again!

"JLatham" wrote:

This should do it for you, at least it works with your test cases.
Enter the formula out in column E, then extend the formula down the sheet
this assumes your first row is at 2
=IF(A2B2,1,0)+IF(C2<B2,IF(A2B2,1,0),0)+IF(B2C2, IF(A2B2,1,0),0)+IF(C2D2,IF(B2C2,IF(A2B2,1,0),0 ),0)

JLatham

"SteveC" wrote:

Col A through D contain the data. Col E contains the formula I'm asking
about:
Col A Col B Col C Col D Col E
7 6 7 2 1
4 3 2 1 4
8 10 9 8 -
5 3 2 10 3

I'd like to Col E to count consecutive improvements for each row of data
starting in Col A relative to Col B, then Col B relative to Col C.

Is there a way to do this?

Thanks a lot!

For this data in row 20, ColM - ColT
17.7% 16.5% 15.2% 14.8% 14.3% 12.5% #DIV/0! #DIV/0! #DIV/0!

this formula:
=IF(M20N20,1,0)+IF(O20<N20,IF(M20N20,1,0),0)+IF( N20O20,IF(M20N20,1,0),0)+IF(O20P20,IF(N20O20,I F(M20N20,1,0),0),0)+IF(P20Q20,IF(O20P20,IF(N20 O20,IF(M20N20,1,0),0),0),0)+IF(Q20R20,IF(P20Q20 ,IF(O20P20,IF(N20O20,IF(M20N20,1,0),0),0),0),0) +IF(R20S20,IF(Q20R20,IF(P20Q20,IF(O20P20,IF(N2 0O20,IF(M20N20,1,0),0),0),0),0),0)+IF(S20T20,IF (R20S20,IF(Q20R20,IF(P20Q20,IF(O20P20,IF(N20O 20,IF(M20N20,1,0),0),0),0),0),0),0)

returns an error

Is there away to still count the consecutive improvements in the first few
columns?

The prior post where I suggested returning a 0 if an error is discovered
wouldn't work...

SteveC, Let me look at your questions this evening so I can hopefully come up
with a one-answer-answers-all answer. We're going to be limited in the
current method by the nested function limit in Excel - 7 nested functions.

The basic premise of the current formula was that if the value to the right
of a cell was greater than the current cell (i.e. A1 B1) then set the
result to 1, otherwise set it to zero. The complication comes in when you
realize that any time the constant decrease in values from left to right is
interrupted that you don't/can't count any further at all even if you have an
apparent valid sequence later on: the 10 7 10 9 situation: the second 10
nullifies any further 'valid' sequences.

If you don't mind working with VBA macros, I think it would be easier to
code up a generic solution to this problem than to try to extend the logic
through nested IF statements.

Besides, nested IF's give me a headache <g

"SteveC" wrote:

For this data in row 20, ColM - ColT
17.7% 16.5% 15.2% 14.8% 14.3% 12.5% #DIV/0! #DIV/0! #DIV/0!

this formula:
=IF(M20N20,1,0)+IF(O20<N20,IF(M20N20,1,0),0)+IF( N20O20,IF(M20N20,1,0),0)+IF(O20P20,IF(N20O20,I F(M20N20,1,0),0),0)+IF(P20Q20,IF(O20P20,IF(N20 O20,IF(M20N20,1,0),0),0),0)+IF(Q20R20,IF(P20Q20 ,IF(O20P20,IF(N20O20,IF(M20N20,1,0),0),0),0),0) +IF(R20S20,IF(Q20R20,IF(P20Q20,IF(O20P20,IF(N2 0O20,IF(M20N20,1,0),0),0),0),0),0)+IF(S20T20,IF (R20S20,IF(Q20R20,IF(P20Q20,IF(O20P20,IF(N20O 20,IF(M20N20,1,0),0),0),0),0),0),0)

returns an error

Is there away to still count the consecutive improvements in the first few
columns?

The prior post where I suggested returning a 0 if an error is discovered
wouldn't work...

Thanks Latham, there's no urgency. I''m not that good with macros, so I dont'
want you to waste your time. Then again, if this is a challenge that
interests you, I would enjoy seeing it if only to learn something. Thanks
again!

"JLatham" wrote:

SteveC, Let me look at your questions this evening so I can hopefully come up
with a one-answer-answers-all answer. We're going to be limited in the
current method by the nested function limit in Excel - 7 nested functions.

The basic premise of the current formula was that if the value to the right
of a cell was greater than the current cell (i.e. A1 B1) then set the
result to 1, otherwise set it to zero. The complication comes in when you
realize that any time the constant decrease in values from left to right is
interrupted that you don't/can't count any further at all even if you have an
apparent valid sequence later on: the 10 7 10 9 situation: the second 10
nullifies any further 'valid' sequences.

If you don't mind working with VBA macros, I think it would be easier to
code up a generic solution to this problem than to try to extend the logic
through nested IF statements.

Besides, nested IF's give me a headache <g

"SteveC" wrote:

For this data in row 20, ColM - ColT
17.7% 16.5% 15.2% 14.8% 14.3% 12.5% #DIV/0! #DIV/0! #DIV/0!

this formula:
=IF(M20N20,1,0)+IF(O20<N20,IF(M20N20,1,0),0)+IF( N20O20,IF(M20N20,1,0),0)+IF(O20P20,IF(N20O20,I F(M20N20,1,0),0),0)+IF(P20Q20,IF(O20P20,IF(N20 O20,IF(M20N20,1,0),0),0),0)+IF(Q20R20,IF(P20Q20 ,IF(O20P20,IF(N20O20,IF(M20N20,1,0),0),0),0),0) +IF(R20S20,IF(Q20R20,IF(P20Q20,IF(O20P20,IF(N2 0O20,IF(M20N20,1,0),0),0),0),0),0)+IF(S20T20,IF (R20S20,IF(Q20R20,IF(P20Q20,IF(O20P20,IF(N20O 20,IF(M20N20,1,0),0),0),0),0),0),0)

returns an error

Is there away to still count the consecutive improvements in the first few
columns?

The prior post where I suggested returning a 0 if an error is discovered
wouldn't work...

Here is the solution to include columns A through H. That's as far as we can
take this using nested IF() statements due to the built in limit of 7 nested
functions.

The first two columns are kind of special cases, as shown by your example
data and result.

For column A to be counted, the value in it simply needs to be greater than
the value in column B.

Column B needs to be both smaller than the value in column A and larger than
the value in column C to count.

For the rest of the series it is just a matter of back tracking to make sure
that the series continues to decline in value from right to left. Any time
that a value goes higher than that of the cell to the right of it, it causes
failure of the test at that point.

Here is the 8-column formula, split at the + symbols for easier reading:
=IF(A2B2,1,0)+
IF(C2<B2,IF(A2B2,1,0),0)+
IF(A2B2,IF(B2C2,1,0),0)+
IF(A2B2,IF(B2C2,IF(C2D2,1,0),0),0)+
IF(A2B2,IF(B2C2,IF(C2D2,IF(D2E2,1,0),0),0),0)+
IF(A2B2,IF(B2C2,IF(C2D2,IF(D2E2,IF(E2F2,1,0), 0),0),0),0)+
IF(A2B2,IF(B2C2,IF(C2D2,IF(D2E2,IF(E2F2,IF(F2 G2,1,0),0),0),0),0),0)+
IF(A2B2,IF(B2C2,IF(C2D2,IF(D2E2,IF(E2F2,IF(F2 G2,IF(G2H2,1,0),0),0),0),0),0),0)

Here it is again so that you can easily cut and paste it:
=IF(A2B2,1,0)+IF(C2<B2,IF(A2B2,1,0),0)+IF(A2B2, IF(B2C2,1,0),0)+IF(A2B2,IF(B2C2,IF(C2D2,1,0),0 ),0)+IF(A2B2,IF(B2C2,IF(C2D2,IF(D2E2,1,0),0),0 ),0)+IF(A2B2,IF(B2C2,IF(C2D2,IF(D2E2,IF(E2F2, 1,0),0),0),0),0)+IF(A2B2,IF(B2C2,IF(C2D2,IF(D2 E2,IF(E2F2,IF(F2G2,1,0),0),0),0),0),0)+IF(A2B2, IF(B2C2,IF(C2D2,IF(D2E2,IF(E2F2,IF(F2G2,IF(G2 H2,1,0),0),0),0),0),0),0)

You can pick up a sample workbook with this solution in it along with a code
solution.

I'm not greatly pleased with the code, it's clumsy, but it does work and it
is easily modified for varying number of columns of data. Not sure how well
it work with fewer than 4 columns of data. That workbook can be downloaded
from my site he
http://www.jlathamsite.com/uploads/SteveC_Sequences.xls - right click and
'save target as' to grab it.

One thing that continues to confuse me is that a sequence like 10 9 10 you
show as a count of 1, but 10 9 8 would be 3, there seems no way to get a
sequence of 2? Somehow I think that the 'answer' to this riddle may be why
the data in the second column has to be handled as a special case.

And you can call me Jerry - most people do.

"SteveC" wrote:

Thanks Latham, there's no urgency. I''m not that good with macros, so I dont'
want you to waste your time. Then again, if this is a challenge that
interests you, I would enjoy seeing it if only to learn something. Thanks
again!

"JLatham" wrote:

SteveC, Let me look at your questions this evening so I can hopefully come up
with a one-answer-answers-all answer. We're going to be limited in the
current method by the nested function limit in Excel - 7 nested functions.

The basic premise of the current formula was that if the value to the right
of a cell was greater than the current cell (i.e. A1 B1) then set the
result to 1, otherwise set it to zero. The complication comes in when you
realize that any time the constant decrease in values from left to right is
interrupted that you don't/can't count any further at all even if you have an
apparent valid sequence later on: the 10 7 10 9 situation: the second 10
nullifies any further 'valid' sequences.

If you don't mind working with VBA macros, I think it would be easier to
code up a generic solution to this problem than to try to extend the logic
through nested IF statements.

Besides, nested IF's give me a headache <g

"SteveC" wrote:

For this data in row 20, ColM - ColT
17.7% 16.5% 15.2% 14.8% 14.3% 12.5% #DIV/0! #DIV/0! #DIV/0!

this formula:
=IF(M20N20,1,0)+IF(O20<N20,IF(M20N20,1,0),0)+IF( N20O20,IF(M20N20,1,0),0)+IF(O20P20,IF(N20O20,I F(M20N20,1,0),0),0)+IF(P20Q20,IF(O20P20,IF(N20 O20,IF(M20N20,1,0),0),0),0)+IF(Q20R20,IF(P20Q20 ,IF(O20P20,IF(N20O20,IF(M20N20,1,0),0),0),0),0) +IF(R20S20,IF(Q20R20,IF(P20Q20,IF(O20P20,IF(N2 0O20,IF(M20N20,1,0),0),0),0),0),0)+IF(S20T20,IF (R20S20,IF(Q20R20,IF(P20Q20,IF(O20P20,IF(N20O 20,IF(M20N20,1,0),0),0),0),0),0),0)

returns an error

Is there away to still count the consecutive improvements in the first few
columns?

The prior post where I suggested returning a 0 if an error is discovered
wouldn't work...

Sorry for the "late" solution !

For the range A10:H10 this array formula:

{=MIN(IF(A10:G10 <= B10:H10, COLUMN(A10:G10) - COLUMN(A10),
COLUMNS(A10:G10)))}

should give the right result, and it is not limited in size...

--
Festina Lente


"SteveC" wrote:

Col A through D contain the data. Col E contains the formula I'm asking
about:
Col A Col B Col C Col D Col E
7 6 7 2 1
4 3 2 1 4
8 10 9 8 -
5 3 2 10 3

I'd like to Col E to count consecutive improvements for each row of data
starting in Col A relative to Col B, then Col B relative to Col C.

Is there a way to do this?

Thanks a lot!