Counting challenge in Excel

December 2, 2006 8:25 AM

Excel question: I can't figure out how to count what I want to count...

I'm trying to set up a spreadsheet that will tell me two things: first, the number of consecutive days I've accomplished a goal (ending today), and second, the longest run of consecutive days I've done something So, for the row:

x x x o o o o o o o o x x x o o o o o

I want the output "5" to represent the number of consecutive days I've achieved my goal to date and the output "8" to represent my longest run. But, I don't see how I can use countif because I must select the whole row and I don't want to count the earlier o's, just specific ones.

I am using conditional formatting so I cannot use blank cells to represent completed or uncompleted--I must use values to do this.

Excel gurus, please help!

posted by underwater to Computers & Internet (6 answers total)

Perhaps you could express the values as:

0 0 0 1 1 1 1 1 1 1 1 -8 0 0 1 1 1 1 1?

In that case, the largest run is abs(min value),
the current run is the sum.

When you fail on a day, you just have to make the next value be -(sum)
posted by JMOZ at 8:38 AM on December 2, 2006

If you are willing to use other rows (hidden or otherwise), you can do the following:

Row 1 is the x x x 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0
Row 2, starting in col B has a formula like:

=if(A2="o",B1+1,0)

in 2A it would be =if(A1="o",1,0)

Then in row 3 you could do

=if(B2>A3,B2,A3)

3a would be =2a

The last cell in row 3 would be the maximum number of consecutive days. The last cell in row 2 would be the length of the current streak.
posted by i love cheese at 8:48 AM on December 2, 2006

The cleverest way would be to cook up a custom function.
posted by Well that's a lie at 12:27 PM on December 2, 2006

Here's my solution using two custom functions. Clicky for sample workbook.

Option Explicit

Function ConsecutiveToday(cells As Range)
    Dim pos As Range, count As Integer

    count = 0
    Set pos = cells(1, 1).Offset(0, cells.count - 1)

    Do
        If pos.Text = "x" Then count = count + 1 Else Exit Do
        If pos.Column = 1 Then Exit Do
        Set pos = pos.Offset(0, -1)
    Loop
    
    ConsecutiveToday = count
End Function

Function ConsecutiveMax(cells As Range)
    Dim pos As Range, count As Integer, max As Integer

    max = 0
    count = 0
    Set pos = cells(1, 1).Offset(0, cells.count - 1)

    Do
        If pos.Text = "x" Then
            count = count + 1
        Else
            If count > max Then max = count
            count = 0
        End If
        
        If pos.Column = 1 Then Exit Do
        Set pos = pos.Offset(0, -1)
    Loop
    
    ConsecutiveMax = max
End Function

posted by Well that's a lie at 2:20 PM on December 2, 2006

Well that's a lie, that was awesome of you to write those functions (that is definitely a solution outside my expertise). Thank you! But I tried using them and the functions seem to have the unfortunate feature that the cell just to the left of consecutivetoday and 2 to the left of consecutivemax must be filled in or the calculation will not work. This is a relatively big problem because I want to keep count as the row expands to the right. Is this fixable?
posted by underwater at 11:13 PM on December 2, 2006

ok, I figured it out using i love cheese's suggestion. The tricky part was that (although I didn't specify) I want these numbers in a column to the left of the data, and I didn't want to have to look at the end of the row to see them (I wanted the answers isolated from the calculation data and prominently displayed).

The max streak was easier. I used ILC's formula, and then did a =max(5:5) to get the biggest streak in row 5.

The consecutive days up to today was harder, because ILC's formula results in a zero when there is no data yet, and there's no easy way to report the last non-zero value in a row.

So, I get the last non-zero value with:

=IF((ISBLANK(G9)=TRUE),F55,"")

Where row 9 is the row with the X's and O's, and 55 is the row with ILC's formula.

Essentially, that formula results in a bunch of blank cells wherever there is data until the last column of data, when it reports the number of consecutive successful days to that point. To the right of that cell, it gives a string of zeros. So I take the max of that row and that's the number of consecutive days to date. Thanks to all responders!
posted by underwater at 12:40 AM on December 3, 2006