+ Reply to Thread
Results 1 to 16 of 16

Using Countif to Count Consecutive Relative Increases in Numbers

  1. #1
    SteveC
    Guest

    Using Countif to Count Consecutive Relative Increases in Numbers

    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!


  2. #2
    JLatham
    Guest

    RE: Using Countif to Count Consecutive Relative Increases in Numbers

    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(A2>B2,1,0)+IF(C2<B2,IF(A2>B2,1,0),0)+IF(B2>C2,IF(A2>B2,1,0),0)+IF(C2>D2,IF(B2>C2,IF(A2>B2,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!
    >


  3. #3
    SteveC
    Guest

    RE: Using Countif to Count Consecutive Relative Increases in Numbe

    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(A2>B2,1,0)+IF(C2<B2,IF(A2>B2,1,0),0)+IF(B2>C2,IF(A2>B2,1,0),0)+IF(C2>D2,IF(B2>C2,IF(A2>B2,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!
    > >


  4. #4
    SteveC
    Guest

    RE: Using Countif to Count Consecutive Relative Increases in Numbe

    Also, saw this formula in there: IF(C2<B2,IF(A2>B2,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(M18>N18,1,0)+IF(O18<N18,IF(M18>N18,1,0),0)+IF(N18>O18,IF(M18>N18,1,0),0)+IF(O18>P18,IF(N18>O18,IF(M18>N18,1,0),0),0)+IF(P18>Q18,IF(O18>P18,IF(N18>O18,IF(M18>N18,1,0),0),0),0)+IF(Q18>R18,IF(P18>Q18,IF(O18>P18,IF(N18>O18,IF(M18>N18,1,0),0),0),0),0)+IF(R18>S18,IF(Q18>R18,IF(P18>Q18,IF(O18>P18,IF(N18>O18,IF(M18>N18,1,0),0),0),0),0),0)+IF(S18>T18,IF(R18>S18,IF(Q18>R18,IF(P18>Q18,IF(O18>P18,IF(N18>O18,IF(M18>N18,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!


  5. #5
    SteveC
    Guest

    RE: Using Countif to Count Consecutive Relative Increases in Numbe

    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(A2>B2,1,0)+IF(C2<B2,IF(A2>B2,1,0),0)+IF(B2>C2,IF(A2>B2,1,0),0)+IF(C2>D2,IF(B2>C2,IF(A2>B2,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!
    > >


  6. #6
    SteveC
    Guest

    Ignoring error in cells

    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(M20>N20,1,0)+IF(O20<N20,IF(M20>N20,1,0),0)+IF(N20>O20,IF(M20>N20,1,0),0)+IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0)+IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0)+IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0)+IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0),0)+IF(S20>T20,IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,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...



  7. #7
    JLatham
    Guest

    RE: Ignoring error in cells

    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(M20>N20,1,0)+IF(O20<N20,IF(M20>N20,1,0),0)+IF(N20>O20,IF(M20>N20,1,0),0)+IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0)+IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0)+IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0)+IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0),0)+IF(S20>T20,IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,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...
    >
    >


  8. #8
    SteveC
    Guest

    RE: Ignoring error in cells

    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(M20>N20,1,0)+IF(O20<N20,IF(M20>N20,1,0),0)+IF(N20>O20,IF(M20>N20,1,0),0)+IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0)+IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0)+IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0)+IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0),0)+IF(S20>T20,IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,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...
    > >
    > >


  9. #9
    JLatham
    Guest

    RE: Ignoring error in cells

    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(A2>B2,1,0)+
    IF(C2<B2,IF(A2>B2,1,0),0)+
    IF(A2>B2,IF(B2>C2,1,0),0)+
    IF(A2>B2,IF(B2>C2,IF(C2>D2,1,0),0),0)+
    IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,1,0),0),0),0)+
    IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,1,0),0),0),0),0)+
    IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,1,0),0),0),0),0),0)+
    IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,IF(G2>H2,1,0),0),0),0),0),0),0)

    Here it is again so that you can easily cut and paste it:
    =IF(A2>B2,1,0)+IF(C2<B2,IF(A2>B2,1,0),0)+IF(A2>B2,IF(B2>C2,1,0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,1,0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,1,0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,1,0),0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,1,0),0),0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,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 here:
    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(M20>N20,1,0)+IF(O20<N20,IF(M20>N20,1,0),0)+IF(N20>O20,IF(M20>N20,1,0),0)+IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0)+IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0)+IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0)+IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0),0)+IF(S20>T20,IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,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...
    > > >
    > > >


  10. #10
    SteveC
    Guest

    RE: Ignoring error in cells

    Jerry, thank you for your time and for explaining your "best practices"
    approach of creating the nested ifs. I adjustd the code as suggested and it
    works great! Regards, SteveC

  11. #11
    SteveC
    Guest

    RE: Ignoring error in cells

    Hi,

    I just noticed that the formula to count the number of consecutive
    improvements does not work in all cases... for example, starting from A2 to
    I2,

    10.2% 9.8% 9.4% 9.2% 9.0% 8.7% 8.6% 7.8% 7.9%

    this should return a value of 7, but instead returns a value of 8.

    And

    17.3% 15.9% 14.9% 15.4% 15.6% 16.1% 16.6% 16.6% 16.0%

    this should return a value of 2 consecutive improvements, but instead
    returns a value of 3.

    I tried playing around with it but can't seem to get a fix... I'll keep
    trying... meanwhile, any suggestions? thanks again...





    "JLatham" wrote:

    > 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(A2>B2,1,0)+
    > IF(C2<B2,IF(A2>B2,1,0),0)+
    > IF(A2>B2,IF(B2>C2,1,0),0)+
    > IF(A2>B2,IF(B2>C2,IF(C2>D2,1,0),0),0)+
    > IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,1,0),0),0),0)+
    > IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,1,0),0),0),0),0)+
    > IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,1,0),0),0),0),0),0)+
    > IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,IF(G2>H2,1,0),0),0),0),0),0),0)
    >
    > Here it is again so that you can easily cut and paste it:
    > =IF(A2>B2,1,0)+IF(C2<B2,IF(A2>B2,1,0),0)+IF(A2>B2,IF(B2>C2,1,0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,1,0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,1,0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,1,0),0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,1,0),0),0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,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 here:
    > 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(M20>N20,1,0)+IF(O20<N20,IF(M20>N20,1,0),0)+IF(N20>O20,IF(M20>N20,1,0),0)+IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0)+IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0)+IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0)+IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0),0)+IF(S20>T20,IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,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...
    > > > >
    > > > >


  12. #12
    JLatham
    Guest

    RE: Ignoring error in cells

    Steve, I think this goes to the question I asked in my last posting above:

    "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."

    Either we count the total number of values involved in the series, or we
    count the number of times within a series that the number decreases. What I
    hear you saying in this last post is that we should count the number of times
    the numbers decrease within the series. I'll go try and tweak it up and will
    let you know when I think it's correct per this latest information.

    "SteveC" wrote:

    > Hi,
    >
    > I just noticed that the formula to count the number of consecutive
    > improvements does not work in all cases... for example, starting from A2 to
    > I2,
    >
    > 10.2% 9.8% 9.4% 9.2% 9.0% 8.7% 8.6% 7.8% 7.9%
    >
    > this should return a value of 7, but instead returns a value of 8.
    >
    > And
    >
    > 17.3% 15.9% 14.9% 15.4% 15.6% 16.1% 16.6% 16.6% 16.0%
    >
    > this should return a value of 2 consecutive improvements, but instead
    > returns a value of 3.
    >
    > I tried playing around with it but can't seem to get a fix... I'll keep
    > trying... meanwhile, any suggestions? thanks again...
    >
    >
    >
    >
    >
    > "JLatham" wrote:
    >
    > > 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(A2>B2,1,0)+
    > > IF(C2<B2,IF(A2>B2,1,0),0)+
    > > IF(A2>B2,IF(B2>C2,1,0),0)+
    > > IF(A2>B2,IF(B2>C2,IF(C2>D2,1,0),0),0)+
    > > IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,1,0),0),0),0)+
    > > IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,1,0),0),0),0),0)+
    > > IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,1,0),0),0),0),0),0)+
    > > IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,IF(G2>H2,1,0),0),0),0),0),0),0)
    > >
    > > Here it is again so that you can easily cut and paste it:
    > > =IF(A2>B2,1,0)+IF(C2<B2,IF(A2>B2,1,0),0)+IF(A2>B2,IF(B2>C2,1,0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,1,0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,1,0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,1,0),0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,1,0),0),0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,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 here:
    > > 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(M20>N20,1,0)+IF(O20<N20,IF(M20>N20,1,0),0)+IF(N20>O20,IF(M20>N20,1,0),0)+IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0)+IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0)+IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0)+IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0),0)+IF(S20>T20,IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,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...
    > > > > >
    > > > > >


  13. #13
    JLatham
    Guest

    RE: Ignoring error in cells

    Ok, I changed both the formula(s) and the code to count the number of times
    that the series 'improves' (decreases before an increase) rather than the
    number of points that constitute the series.

    Use the same link from the earlier posting to get the new copy.

    "SteveC" wrote:

    > Hi,
    >
    > I just noticed that the formula to count the number of consecutive
    > improvements does not work in all cases... for example, starting from A2 to
    > I2,
    >
    > 10.2% 9.8% 9.4% 9.2% 9.0% 8.7% 8.6% 7.8% 7.9%
    >
    > this should return a value of 7, but instead returns a value of 8.
    >
    > And
    >
    > 17.3% 15.9% 14.9% 15.4% 15.6% 16.1% 16.6% 16.6% 16.0%
    >
    > this should return a value of 2 consecutive improvements, but instead
    > returns a value of 3.
    >
    > I tried playing around with it but can't seem to get a fix... I'll keep
    > trying... meanwhile, any suggestions? thanks again...
    >
    >
    >
    >
    >
    > "JLatham" wrote:
    >
    > > 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(A2>B2,1,0)+
    > > IF(C2<B2,IF(A2>B2,1,0),0)+
    > > IF(A2>B2,IF(B2>C2,1,0),0)+
    > > IF(A2>B2,IF(B2>C2,IF(C2>D2,1,0),0),0)+
    > > IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,1,0),0),0),0)+
    > > IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,1,0),0),0),0),0)+
    > > IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,1,0),0),0),0),0),0)+
    > > IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,IF(G2>H2,1,0),0),0),0),0),0),0)
    > >
    > > Here it is again so that you can easily cut and paste it:
    > > =IF(A2>B2,1,0)+IF(C2<B2,IF(A2>B2,1,0),0)+IF(A2>B2,IF(B2>C2,1,0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,1,0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,1,0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,1,0),0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,1,0),0),0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,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 here:
    > > 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(M20>N20,1,0)+IF(O20<N20,IF(M20>N20,1,0),0)+IF(N20>O20,IF(M20>N20,1,0),0)+IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0)+IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0)+IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0)+IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,1,0),0),0),0),0),0)+IF(S20>T20,IF(R20>S20,IF(Q20>R20,IF(P20>Q20,IF(O20>P20,IF(N20>O20,IF(M20>N20,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...
    > > > > >
    > > > > >


  14. #14
    SteveC
    Guest

    RE: Ignoring error in cells

    Hi Jerry, thanks for your response. Last month I applied your formula and I
    tested it with a few examples, and it seemed fine so I didn't fully test it.
    To belatedly answer your question:

    a sequence of 10 9 10 -- count equals 1
    a sequence of 10 9 8 -- count equals 3
    my comment: yep

    seems no way to get a sequence of 2...
    my comment: yeah, I just noticed

    I do not think the second column has to be handled a special case. I just
    need to count consecutive improvements.

    It's easy to do if you substitute "10>9" as "Yes" and "9>10" as "No". Then
    you only have to count the number of consecutive "Yes." But the only way I
    know how to do that is add "helper columns" -- see this for example:
    http://www.microsoft.com/office/comm...=en-us&m=1&p=1

    But I thought it would be great if it could be done within a single cell...

    Thanks a lot for your time and advice... I've learned a lot over the past
    few weeks... SteveC




  15. #15
    SteveC
    Guest

    RE: Ignoring error in cells

    http://www.jlathamsite.com/uploads/SteveC_Sequences.xls

    Jerry, very much for posting the above link with a standalone formula and
    an associated macro. The formula works perfectly now! Very nice work with
    the macro too; I'll study that approach as well. Thanks again for your time.




  16. #16
    JLatham
    Guest

    RE: Ignoring error in cells

    The formula has reached it's limit at this point - or is only one away from
    the limit of 7 nested functions, so the code is the way to go if you have
    data extending out past column H (assuming start in A).

    "SteveC" wrote:

    > http://www.jlathamsite.com/uploads/SteveC_Sequences.xls
    >
    > Jerry, very much for posting the above link with a standalone formula and
    > an associated macro. The formula works perfectly now! Very nice work with
    > the macro too; I'll study that approach as well. Thanks again for your time.
    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1