Ask Experts Questions for FREE Help !
Ask
    traveler911's Avatar
    traveler911 Posts: 6, Reputation: 1
    New Member
     
    #1

    Oct 21, 2013, 11:56 AM
    Count sequence if
    I am looking to display the the number where the event has occurred in sequential order.
    Column C has N for true, or O for false (Wingding font)
    What I want to do is display the running total for true events that occurred sequentially without interruption for the entire year. The first example shows the sequence in the last column. (counting from 6 to 13)

    Kind of if C4=n, then 1, ""; then I guess nest a SUM to keep a running total?

    Part two. Hopefully the sequence will extend to the next year. So in the second example you can see the current year, and below is the sequence from the previous year. Short example is 2013 ends with 8, so 2014 will start with 8, and add on from there.

    Thanks
    Kent

    Mo 10/14 n 3.103 0:33:15 10:43 389.0 5.60 22:00 1.0 1.0 H 3.1 6
    Tu 10/15 n 8.014 1:24:23 10:32 1007.0 5.70 13:10 1.0 1.0 H 5 7
    We 10/16 n 8.045 1:23:40 10:24 1004.0 5.77 19:45 1.0 1.0 H 6.2 8
    Th 10/17 n 2.013 0:21:03 10:27 247.0 5.74 05:10 1.0 1.0 W 2 9
    Fr 10/18 n 6.706 1:10:05 10:27 824.0 5.74 13:50 1.5 1.5 H 6.2 10
    Sa 10/19 n 2.110 0:22:13 10:32 263.0 5.70 05:20 1.0 1.0 W 2 11
    Su 10/20 n 3.104 0:32:41 10:32 386.0 5.70 14:50 1.0 1.0 H 3 12
    Mo 10/21 n 2.012 0:20:51 10:22 247.0 5.79 05:20 1.0 1.0 W 2 13


    2013 2014
    2012 0 2013 8
    N 1 n 9
    N 2 n 10
    N 3 n 11
    O n 12
    O o
    N 1 o
    N 2 n 1
    N 3 n 2
    N 4 n 3
    N 5 n 4
    N 6 n 5
    N 7 n 6
    N 8 n 7
    Kevin-Radstock's Avatar
    Kevin-Radstock Posts: 8, Reputation: 3
    New Member
     
    #2

    Oct 22, 2013, 10:27 AM
    Hi

    How about a sample spread sheet with dummy data and your expected result instead of typing all that.
    traveler911's Avatar
    traveler911 Posts: 6, Reputation: 1
    New Member
     
    #3

    Oct 22, 2013, 11:48 AM
    Quote Originally Posted by Kevin-Radstock View Post
    Hi

    How about a sample spread sheet with dummy data and your expected result instead of typing all that.
    I didn't see the attach file buttom when I wrote this, other wise believe me I would have.

    Column O is the field in question, and it references C. The sequence starts in rows 240's. This is for 2013, and hopefully the streak will continue into 2014. 2014 has it's own sheet. So then I am assuming once we figure this out, and I can just add a sum to reference a cell that I enter the total from 2013. I know I can have it linked to the field, but I am afraid that if I end up deleting this sheet in a few years it will cause a cascade of information loss if they are all referencing the previous sheet.

    Thanks
    Kent
    Attached Files
  1. File Type: xls Book1.xls (304.5 KB, 134 views)
  2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Oct 25, 2013, 07:40 AM
    THe formula you're looking for might be this, for example in O4, then copied down:

    =IF(C4<>"n", "", N(O3)+1)


    You can use that same formula on your new sheet, but for the first cell on that new sheet you reference the LAST cell from the 2013 sheet:

    =IF(C4<>"n", "", N('2013-Workout'!O331)+1)

    To avoid the possible loss of data from year to year, after you get the value from the first cell correct and you SEE the result, you can change it from a formula to a flat value. The rest of the formulas in column O refer to the same sheet, so they are safe.
    traveler911's Avatar
    traveler911 Posts: 6, Reputation: 1
    New Member
     
    #5

    Nov 4, 2013, 04:45 PM
    I came to post another question, and I found that my last reply on this thread disappeared. I wanted to thank you for your assistance. It worked out perfectly.

    I don't understand why the reply didn't stay.

    Thanks
    Kent
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #6

    Nov 6, 2013, 12:14 PM
    That's OK, no need to fret, even forums are subject to occasional quirks and flukes. Thanks for coming back to let me know.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Sequence help [ 2 Answers ]

Numbers are 5,7,11,15,19... what's the next number in the sequence.explain please.

I received a final judgment by defaul on count 1 on a 2 count lawsuit [ 0 Answers ]

1st count has been set by default On the second count my attorney told me that we have a trail date on Friday (nov 4th which I understood it was in front of a jury) somehow today he tells me we do not have the right to a jury trial What is the reason for this?

Sequence [ 0 Answers ]

What number comes next 115 133 161 104 922 977 020 282 135 780


View more questions Search