Excel help please

[westcoastgsr]

Working on a work project and Im having problems with a formula. If i have 2 columns name and another with result I want to set up a formula to show the amount of times in a row someone gets.

Sample:
Name Result
bob Yes
bob no
bob no
bob no
bob yes
bob maybe
bob no
bob no
ted no
ted no
ted yes
ted no
ted no

So assumeing I have different names too but only 3 results I would want the fomula to show Bob had no 3 times in a row once and no 2 times in a row once. Ted got no 2 times in a row twice.

Fuck I hate when I forget how to do things in University. All help is appreciated of course!!!

[Ferra]

Do you need the output in a particular way? if not you can probably do something like this (assuming names are sorted /separated)
C3 =IF(B3="N", IF(C2>=1, IF(B2=B3, C2+1, 0), IF(B2=B3, 2, 0)), 0)

Col A = Name
Col B = Result
Col C = Formula
Values start row 2, formula starts at row 3

So assumeing I have different names too but only 3 results I would want the fomula to show Bob had no 3 times in a row once and no 2 times in a row once. Ted got no 2 times in a row twice.

You can probably use a pivot table to sort and display the tables results...i.e. showing how many 2/3/4 in a rows total sum counts

[westcoastgsr]

Thanks the formula works. But the names are not sperated but they are sorted alphabetically. So the problem is the results blend together. For example:

Bob n
Bob n
Bob n
Ted n
Ted n

Then it say there was 4 in a row.

[Ferra]

Originally Posted by westcoastgsr

Thanks the formula works. But the names are not sperated but they are sorted alphabetically. So the problem is the results blend together. For example:

Bob n
Bob n
Bob n
Ted n
Ted n

Then it say there was 4 in a row.

you can add another "if (A2=A3, x, 0) function infront of the formula
> C3 = IF(A2=A3, IF(B3="N", IF(C2>=1, IF(B2=B3, C2+1, 0), IF(B2=B3, 2, 0)), 0), 0)

Btw..just notice the formula I posted is "cumulative" (i.e. if you miss 4 in a row, it will show both 2, 3, and 4 in the results)
depending on how you want your result....you might need to add another function or another column to take out the cumulative results.