# Excel Magic Trick #735 - looking to elaborate on this

#### excel99

##### New Member
Hi,

This video has been very helpful, (MrExcel's Learn Excel #735 - Win Streak - YouTube) however I'd like if someone can expand on this to show the Home Win Streak, Home Loss Streak, Away Win Streak, Away Loss Streak.

I've been searching the net and with my limited excel knowledge I'm struggling with this. I'm open to a vba formula solution as well.

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### par60056

##### Well-known Member
welcome to the forum.

I think that this can be done with very similar formulas to the win/loss streak.

What you would do is add another if around it to see if they are home or away.

call them columns J-M

J1=0
k1=0
l1=1
m1=0
j2=if(\$e2="Home",if(F2=1,j1+1,0),j1)
K2=if(\$e2="Home",if(G2=1,k1+1,0),k1)
L2=if(\$e2="Away",if(F2=1,L1+1,0),L1)
m2=if(\$e2="Away",if(G2=1,M1+1,0),M1)

I think this would show what you want.

#### excel99

##### New Member
par60056 That worked for me! I have another challenge. The data sheet I am working with has all the teams results in one sheet. So with that being said in COLUMN A I have the team name:

COLA
Chicago
Chicago
Chicago
Detroit
Detroit
Detroit
Detroit

I need to incorporate something to test for the change in teams so that when I get into the results for Detroit the streaks reset. I would think something like testing if the value in cell A2= the value in the cell above A1?

I hope this makes sense.

Your help is greatly appreciated. Thank you

#### par60056

##### Well-known Member
Well then you will also want to change the other 2 streak formulas.

J1=0

k1=0
L1=1
m1=0

H2=if(A2=A1,
if(F2=1,j1+1,0),f2)
I2=
if(A2=A1,if(G2=1,k1+1,0),G2)
j2=
if(A2=A1,if(\$e2="Home",if(F2=1,j1+1,0),j1),if(\$e2="Home",F2,0)))
K2=if(A2=A1,if(\$e2="Home",if(G2=1,k1+1,0),k1),if(\$e2="Home",G2,0)))
L2=if(A2=A1,if(\$e2="Away",if(F2=1,L1+1,0),L1),if(\$e2="Away",F2,0)))
m2=if(A2=A1,if(\$e2="Away",if(G2=1,M1+1,0),M1),if(\$e2="Away",G2,0)))

There is probably a way to simplify this to use less IFs but this is straight forward to understand

#### excel99

##### New Member
Outstanding....works like a charm. Maybe there is a prettier way but this gets the job done.

My headache just went away with your prescription, thanks, all the best to you.

Enjoy

Replies
0
Views
547
Replies
4
Views
876
Replies
11
Views
1K
Replies
1
Views
4K
Replies
0
Views
1K

### Forum statistics

1,191,196
Messages
5,985,230
Members
439,950
Latest member
Xearo96 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back