count of consecutive numbers greater than 0 in a row

  • Hello!


    I need a formula that will return the following in the Consecutive Years Given column. Any help is greatly appreciated.
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 147"]A[/TD]
    [TD="width: 71"]B[/TD]
    [TD="width: 79"]C[/TD]
    [TD="width: 71"]D[/TD]
    [TD="width: 71"]E[/TD]
    [TD="width: 79"]F[/TD]
    [TD="width: 71"]G[/TD]
    [TD="width: 71"]H[/TD]

    [/tr]


    [tr]


    [TD="width: 147"]Donor[/TD]
    [TD="width: 71, align: right"]2017[/TD]
    [TD="width: 79, align: right"]2016[/TD]
    [TD="width: 71, align: right"]2015[/TD]
    [TD="width: 71, align: right"]2014[/TD]
    [TD="width: 79, align: right"]2013[/TD]
    [TD="width: 71, align: right"]2012[/TD]
    [TD="width: 71, align: right"]Consecutive Years Given[/TD]

    [/tr]


    [tr]


    [TD="width: 147"]Test[/TD]
    [TD="width: 71, align: right"]100[/TD]
    [TD="width: 79, align: right"]0[/TD]
    [TD="width: 71, align: right"]100[/TD]
    [TD="width: 71, align: right"]100[/TD]
    [TD="width: 79, align: right"]100[/TD]
    [TD="width: 71, align: right"]0[/TD]
    [TD="width: 71, align: right"]3[/TD]

    [/tr]


    [tr]


    [td]

    Test

    [/td]


    [TD="align: right"]100[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]100[/TD]
    [TD="align: right"]50[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]100[/TD]
    [TD="align: right"]2[/TD]

    [/tr]


    [tr]


    [td]

    Test

    [/td]


    [TD="align: right"]100[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]100[/TD]
    [TD="align: right"]50[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]2[/TD]

    [/tr]


    [tr]


    [td]

    Test

    [/td]


    [TD="align: right"]100[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]100[/TD]
    [TD="align: right"]50[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]2[/TD]

    [/tr]


    [tr]


    [td]

    Test

    [/td]


    [TD="align: right"]50[/TD]
    [TD="align: right"]50[/TD]
    [TD="align: right"]50[/TD]
    [TD="align: right"]50[/TD]
    [TD="align: right"]50[/TD]
    [TD="align: right"]50[/TD]
    [TD="align: right"]6[/TD]

    [/tr]


    [tr]


    [td]

    Test

    [/td]


    [TD="align: right"]50[/TD]
    [TD="align: right"]505[/TD]
    [TD="align: right"]50[/TD]
    [TD="align: right"]100[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]4[/TD]

    [/tr]


    [/TABLE]

  • Hi,


    In cell H2 you can have the following array formula :


    Code
    =MAX(FREQUENCY(IF($B2:$G2>0,COLUMN($B2:$G2)),IF($B2:$G2=0,COLUMN($B2:$G2))))


    Instead of the Enter key ... you need to use simultaneously the keys Control Shift Enter ...


    Hope this will help ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!