+ Reply to Thread
Results 1 to 7 of 7

Help Tracking Transitions

  1. #1
    Registered User
    Join Date
    08-23-2017
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    4

    Help Tracking Transitions

    I have a bunch of data of 1's and 0's in different columns. I need help tracking how many 1's, turn to 0's from one column to the next.

    For example using the following data:

    1 1 0
    1 0 0
    1 1 1

    I would need the formula to show in a row I would be using for totals:

    0 1 1

    as there was 1 change from column 2 to 1 (the second row) and 1 change from column 3 to 2 (the first row)

    Thanks!
    Attached Files Attached Files
    Last edited by uhf77; 08-24-2017 at 10:38 AM. Reason: Added attachment

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,743

    Re: Help Tracking Transitions

    a sample sheet would help

    i dont fully follow the result
    I understand that whenever there is a transition change from 1 to 0 you want to show a 1
    But I dont quite follow the result, or at least i want to make sure

    you are not interested in a change from 0 to 1 - is that correct only 1 to 0

    how
    are the 1's an 0s all in a seperate cell
    so
    A1 = 1 B1 =1 C1 =0
    A2 = 1 B1 =0 C1 =0
    A3 = 1 B3 =1 C3 =1

    Then the total
    is that in seperate cells

    And why are you showing the last row first
    Ie for Row 3 which is 111

    you are showing a 0 - But its before the results of row 2 or row 1

    and can you have many columns

    1 1 1 0 0 0 1 1 0 1 0 1 0
    etc

    Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "Choose File" (top Left corner).
    Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    08-23-2017
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Help Tracking Transitions

    I have added the attachment

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,743

    Re: Help Tracking Transitions

    it looks like its the difference between the 1's in the previous column and current column

    =COUNTIF(B2:B11,1)-COUNTIF(C2:C11,1)

    works for your example
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-23-2017
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Help Tracking Transitions

    Thanks so much for the help Wayne. I have run into a new problem, however, and I hope that you or another kind soul may be able to help again. While this formula does fix the problem based exclusively on the example that I poseted, I did neglect to include that there may be some lines that begin with 1's along the way (see example).
    Attached Files Attached Files

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,743

    Re: Help Tracking Transitions

    i thought that may not work for all conditions

    played with some other ideas, and not found a solution yet

  7. #7
    Registered User
    Join Date
    08-23-2017
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Help Tracking Transitions

    Figured it out (using roundup b/c some values are like .8 or.5):

    {=SUM(IF(ROUNDUP(C2:C15,0)=ROUNDUP(B2:B15,0),0,IF(B2:B15=0,0,IF(C2:C15=0,IF(ROUNDUP(B2:B15,0)=1,1,0),0))))}
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Counting Transitions (cycles)
    By jonrack in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-02-2015, 04:21 PM
  2. [SOLVED] SlideShowSettings.Run doesn't start transitions
    By inspector-71 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-04-2013, 06:57 AM
  3. [SOLVED] Detecting transitions between alpha/numeric characters
    By quietsnow in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-17-2013, 08:23 PM
  4. count transitions in a matrix
    By simone77 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-02-2012, 02:38 PM
  5. how to count transitions in a matrix
    By simone77 in forum Excel General
    Replies: 21
    Last Post: 05-02-2012, 02:22 PM
  6. Calculate number of transitions in a matrix
    By simone77 in forum Excel General
    Replies: 6
    Last Post: 04-27-2012, 05:49 AM

Tags for this Thread

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