Excel Magic Trick #735 - looking to elaborate on this

[excel99]

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.

Thanks for any help you can provide.

 

[par60056]

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]

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 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]

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.

 

[par60056]

Glad it worked for you.

Enjoy