How to count consecutive numbers based on the first number of the column

  • Hi


    Looking for a formula to count the consecutive numbers in 1 column based on the first Number/Column.


    Then if the first number in the column is Zero the result is shown as a negative number but if the first number in the column is 1 the result is shown as a positive number.


    If you can help it would be greatly appreciated.


    Here is an example.







    [TABLE="width: 145"]

    [tr]


    [TD="width: 29"][TABLE="width: 168"]

    [tr]


    [TD="width: 52"]Result [/TD]
    [TD="class: gmail-xl67, width: 29"]-1[/TD]
    [TD="class: gmail-xl67, width: 29"]-1[/TD]
    [TD="class: gmail-xl67, width: 29"]3[/TD]
    [TD="class: gmail-xl67, width: 29"]2[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl63"] [/TD]
    [TD="class: gmail-xl64"]A[/TD]
    [TD="class: gmail-xl64"]B[/TD]
    [TD="class: gmail-xl64"]C[/TD]
    [TD="class: gmail-xl64"]D[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl63"] [/TD]
    [TD="class: gmail-xl65"] [/TD]
    [TD="class: gmail-xl65"] [/TD]
    [TD="class: gmail-xl65"] [/TD]
    [TD="class: gmail-xl65"] [/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]1[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]2[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]3[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]0[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]4[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]0[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]5[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]0[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]6[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]7[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]8[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]9[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]10[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]11[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]0[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]12[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]0[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]13[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]0[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]14[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]0[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]15[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]16[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]0[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]17[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]18[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]19[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"]20[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]
    [TD="class: gmail-xl67"]1[/TD]

    [/tr]


    [tr]


    [TD="class: gmail-xl66"] THX[/TD]
    [TD="class: gmail-xl68"]



    [/TD]
    [TD="class: gmail-xl68"] [/TD]
    [TD="class: gmail-xl68"] [/TD]
    [TD="class: gmail-xl68"] [/TD]

    [/tr]


    [/TABLE]
    [/TD]
    [TD="class: gmail-xl67, width: 29"][/TD]
    [TD="class: gmail-xl67, width: 29"][/TD]
    [TD="class: gmail-xl67, width: 29"][/TD]
    [TD="class: gmail-xl67, width: 29"][/TD]

    [/tr]


    [/TABLE]


  • Re: How to count consecutive numbers based on the first number of the column


    Your data makes no sense to me. Please explain your results.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: How to count consecutive numbers based on the first number of the column


    Sure. Let's look at column A as each Column will have it's own result. The starting point is to look at row 1 first. This will determine if the result will be expressed as a positive number or a negative number. Zero is negative and 1 is positive. In column A the first number is negative making the result expressed as a negative number, we then look at row 2. Row 2 is a 1. This means that based on the first row the Zero was followed by a 1 and the formula end as simply one consecutive negative number giving the result of -1

    If you look at column C the first row number is 1 making the result a positive number of 1. you then look at row 2 and you still have a 1 bringing the total to 2. Then row 3 has another 1 with a new total of 3 but then when you look at row 4 you have a zero so the total consecutive numbers for this column would be 3 as the zero in row 4 stops the consecutive number count.




    I use this to track consecutive days up or down for stocks. You can think of the rows(1 threw 20) as days. 1 would be today, 2 yesterday and so on. What I want to know is based on today how many consecutive days were up (positive number) or down (negative numbers)


    I hope this makes sense. Thank you for the reply.

  • Re: How to count consecutive numbers based on the first number of the column


    [cp]*[/cp]

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: How to count consecutive numbers based on the first number of the column


    Cross Posted here:


    https://www.mrexcel.com/forum/…-first-number-column.html




    [TABLE="width: 64"]

    [tr]


    [TD="width: 64, bgcolor: transparent"]A message to forum cross posters.[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Please read this:[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]http://www.excelguru.ca/node/7[/TD]

    [/tr]


    [/TABLE]