Need formula for counting consecutive cells that have the same data in them

Joseph D. Casaletto

unread,

Dec 16, 2001, 1:39:30 AM12/16/01

to

Hello to all,
I am going to explain a feature I would like to add to my Excel spreadsheet
that I am using to manage my fantasy football league. Please reply to the
group with any suggestions or further questions.
Here is a brief description of how it works:
Each team has its own sheet, and there is a sheet for the standings as well.
One row on each team page has either a capital W or capital L in it to
indicate if the team won or lost that week. On the standings page of my
spreadsheet I am using a COUNTIF statement for W and L to fill the won and
lost columns.
-- example:
=COUNTIF(Soldiers!$B20:$Q20,"W")
=COUNTIF(Soldiers!$B20:$Q20,"L")
Now this is all working fine. But there is another enhancement that I would
like to do, but haven't figured out a way to do it yet. I would like to add
another column to the standings sheet that would specify the current streak
each team is on. For instance, it would say W3 if the team had won three in
a row, or L2 if they had lost two. This is giving me a lot of trouble
trying to figure out, and I thought I'd pose it to the newsgroup.

So you would be looking at a row like this for data:

result W W L L L L W L

and starting from a specified cell, go backwards, counting either L's or
W's, until there was a result that didn't match. Also note that games which
haven't been played yet will have a blank cell.
For example: W, W, L (stop counting) - gives result W2

Thanks in advance,
Joe Casaletto

MattMogul

unread,

Dec 16, 2001, 4:26:26 AM12/16/01

to

Hi Joseph

Assume the simple W W L L L L W L array you
gave goes from cells a1 to h1. The following formula will
give you the number of consecutive W by finding the
location of the first L in the array and subtracting 1 to
give the location of the last W in the run. The
concatenation simply presents the result as "W" + the
location of the last W in the run. Check out help for info
on the match function.

=CONCATENATE("W",MATCH("L",A1:H1,0)-1)

Good luck

Matt

>.
>

MattMogul

unread,

Dec 16, 2001, 4:39:48 AM12/16/01

to

After I posted I noticed the obvious that you wanted to
know what the loosing streak is as well. So you need to
first test the string for a loss ie match("L",A1:H1,0)=1
If match function = 1 then they lost their last match so
find the first W (using match function) subtract 1 and you
have the loosing streak. Combined with my previous post it
would look something like

=if(match("L",A1:H1,0)=1,concatenate("L",match
("W",A1:H1,0)-1),concatenate("W",match("L",A1:H1,0)-1))

Matt

>.
>

Joseph D. Casaletto

unread,

Dec 16, 2001, 1:05:00 PM12/16/01

to

Thanks for your reply. The formula works great, except that in my version
the range has to be read backwards - for example, instead of A1:H1, it needs
to go from H1 to A1. The reason for this is because the results of the
first game at the start of the season is in cell A1, and the last is in H1.
We want to know the most current streak, and thus it needs to be read from
right to left.
I tried to change the range to H1:A1 in the cell, but excel just pops is
back to A1:H1 automatically. Any ideas on how to make this change?
otherwise this is just what I needed.

Thanks,

--
Joe Casaletto

http://www.joecasaletto.com
"MattMogul" <mste...@idx.com.au> wrote in message
news:10b401c18615$9a0c7f30$a5e62ecf@tkmsftngxa07...

Kevin R. Weaver

unread,

Dec 16, 2001, 2:08:02 PM12/16/01

to

Maybe this is what you want.

1st, I borrowed the "reverse cell contents" from www.cpearson.com
2nd, assuming your win/loss letters are in A1:H1
3rd, I put the reverse cell formula in A2 (it's an ARRAY formula) and filled it
across to H2.

=OFFSET(rowlist,0,MAX(COLUMN(revrowlist))-COLUMN())

(see CPearson's site if you have questions on this)

The "rowlist" name is the original list (i.e., row 1) and the "revrowlist" name
is the new list (i.e. row 2)

4th, this formula goes somewhere else:

="W"&MATCH("L",INDIRECT(ADDRESS(2,MATCH("W",A2:H2,0)+1)&":H2"),0)

and should show you the streak of wins.

This one show the streak of losses:

="L"&MATCH("W",INDIRECT(ADDRESS(2,MATCH("L",A2:H2,0)+1)&":H2"),0)

Did I do what you wanted?

Kevin R. Weaver

unread,

Dec 16, 2001, 2:12:59 PM12/16/01

to

Well, I just realized that it's close but not quite.

Kevin R. Weaver

unread,

Dec 16, 2001, 2:23:42 PM12/16/01

to

This works (I think) for Wins -- but it's ugly:

="W"&IF(ISERROR(MATCH("L",INDIRECT(ADDRESS(2,MATCH("W",A2:H2,0)+1)&":H2"),0)),COUNTA(INDIRECT(ADDRESS(2,MATCH("W",A2:H2,0)+1)&":H2"))+1,MATCH("L",INDIRECT(ADDRESS(2,MATCH("W",A2:H2,0)+1)&":H2"),0))

Switch the W's and L's for losses.

Kevin R. Weaver

unread,

Dec 16, 2001, 4:35:37 PM12/16/01

to

revisited after football game and it still didn't catch all cases. This, I hope, does.

="W"&IF(ISERROR(MATCH("L",INDIRECT(ADDRESS(2,MATCH("W",A2:H2,0)+1)&":H2"),0)),COUNTIF(A2:H2,"W"),MATCH("L",INDIRECT(ADDRESS(2,MATCH("W",A2:H2,0)+1)&":H2"),0))

Daniel.M

unread,

Dec 16, 2001, 8:05:12 PM12/16/01

to

Assuming your data (for example, {"L","W","L","W","L","W","W","W"}) in A1:H1
(or until ... whatever in row 1), you can enter this ARRAY formula (so enter
it as control-shift-enter) in A2

=A1&MOD(
MIN(IF($A$1:A1<>A1,COLUMNS($A$1:A1)+CELL("col",$A$1:A1)-COLUMN($A$1:A1)))-1,
COLUMNS($A$1:A1)+1)

Then, copy it to B2, C2, etc.
Results (with the above data) would be
{"L1","W1","L1","W1","L1","W1","W2","W3"}

Let me know if this works for you.

Regards,
Daniel M.

"Joseph D. Casaletto" <spamall...@netscape.net> wrote in message
news:9vhfk...@enews1.newsguy.com...

David G. Patton

unread,

Dec 16, 2001, 8:22:14 PM12/16/01

to

Hello Joseph
With numbers 1-8 in A1:H1

Array Enter the following for row 3
=COUNTIF(A3:H3,">""")-MAX(IF(A3:H3="L",A1:H1))

HTH Dave Patton

Joseph D. Casaletto wrote in message <9vhfk...@enews1.newsguy.com>...

Joseph D. Casaletto

unread,

Dec 17, 2001, 12:44:21 AM12/17/01

to

Thanks all. I need a day or two to look at these suggestions as I will be
busy with work.. Will let you know how things work out.

--
Joe Casaletto

http://www.joecasaletto.com

"David G. Patton" <pat...@cadvision.com> wrote in message
news:#ELQtkphBHA.2212@tkmsftngp03...

David G. Patton

unread,

Dec 17, 2001, 1:32:11 AM12/17/01

to

Extended to show either consecutive Wins or Losses.
Row includes numbers increasing by 1

Formula at end of Row Array Entered Ctrl-Shift-Enter (CSE)

=IF(I5="W","W"&COLUMN()-1-MAX(IF($A5:I5="L",$A$1:I$1)),"L"&COLUMN()-1-MAX(IF
($A5:I5="W",$A$1:I$1)))

David G. Patton wrote in message <#ELQtkphBHA.2212@tkmsftngp03>...

MattMogul

unread,

Dec 17, 2001, 5:29:45 AM12/17/01

to

Hi Kevin

I could not find the reverse cell contents reference on
the site that you mentioned. Could you post the precise
url so that I can find it. The only time I was confronted
with this problem I had to write a vba procedure to do
reverse the order of members of a range object...glad to
see someone has written a function to do it so I am
interested in see how it works.

Thanks
Matt

>.
>

MattMogul

unread,

Dec 17, 2001, 7:53:49 AM12/17/01

to

Hi Joe

My only suggestion is to use a custom function to create a
text string in reverse order and then find the L and W in
the string to get your answer. You will need to copy the
following code into VBA. It is a custom function.
1.Open visual basic tools/macro/visual basic editor
2.Insert a new module. In the VBA window insert/module
3. Copy this code and paste in the module window (RHS
screen)

Function MakeReverseString(rngResults As Range)
Dim intFinish As Integer
Dim arrResults As Variant
Dim strRevString As Variant

On Error GoTo HandleErr
intFinish = rngResults.Columns.Count

arrResults = rngResults.Value
strRevString = ""
For i = intFinish To 1 Step -1
strRevString = strRevString + arrResults(1, i)
Next i

MakeReverseString = strRevString

ExitHere:
Exit Function

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " &
Err.Description,
vbCritical, "Module1.MakeReverseString" 'ErrorHandler:$$N=
Module1.MakeReverseString
End Select
' End Error handling block.
End Function

4. In your spreadsheet to get the result copy this
formula. Not pretty but...

=IF(ISERROR(FIND("L",MakeReverseString
(Result))),"W"&COLUMNS(Result),IF(ISERROR(FIND
("W",MakeReverseString(Result))),"L"&COLUMNS(Result),IF
(FIND("L",MakeReverseString(Result))=1,"L"&FIND
("W",MakeReverseString(Result))-1,"W"&FIND
("L",MakeReverseString(Result))-1)))

In my formula I have named the range of cells L W etc
as "result". To test in your sheet simply replace the
word result with your range.

It works

>.
>

Kevin R. Weaver

unread,

Dec 17, 2001, 9:13:48 AM12/17/01

to

CP's handy-work:

http://www.cpearson.com/excel/lists.htm#ReverseOrder

Chip Pearson

unread,

Dec 17, 2001, 9:14:53 AM12/17/01

to

Matt,

The formulas are on my "Working With Lists" page.
http://www.cpearson.com/excel/lists.htm#ReverseOrder .

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"MattMogul" <mste...@idx.com.au> wrote in message

news:15a001c186e5$bed57ee0$3bef2ecf@TKMSFTNGXA10...

MattMogul

unread,

Dec 17, 2001, 4:42:52 PM12/17/01

to

Thanks Chip...really useful site...well done. I have
never used array formulas and have simply relied on VBA to
handle array maths.
Matt

>.
>

David G. Patton

unread,

Dec 19, 2001, 12:23:23 PM12/19/01

to

The formula that follws shows the latest Win or Loss Streak.
The formula does not have to be adjusted as additional game results are
added.
Joseph D. Casaletto may not have required an answer; there is no
e-mail address.

=IF(OFFSET(B6,0,Y6-1)="W",Y6-MAX((B6:U6="L")*(rNum))& "
Wins",Y6-MAX((B6:U6="W")*(rNum))& " Losses")

Joseph D. Casaletto wrote in message <9vk0o...@enews2.newsguy.com>...