CURRENT home and away streaks (Not longest)

JD18

New Member
Joined
Mar 3, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi, so im relatively new to using excel and one thing has stumped me on my spreadsheet, Current Home and away streaks.

So my spreadsheet is looking at team data from fixtures for teams the premier league and all the previous data between the two clubs in that upcoming fixture, so it determines the wins loses draws goals for and against goal difference and total goals in the premier league, the all time First division and all comps for that fixture. Now onto my issue. i wanted to create win streaks for current and max in the Premier league, then separately the same thing for home and away in the prem, And then again but for home and away in all comps. I did the max win streak using XLookup, but cant find away to create a current streak as cant get the formula to solve all the problems. So, i have the date in column B, the fixture in column C which is used to determine home and away based on order of the teams in the fixture (Home team v Away team), the result so W L or D in column D the scores for the home team in column E and then away in F and competition in G. My issue was when i was doing Home streaks in the premier league for example i would refer to column C where it could determine who was home or away, column G to determine if it was Premier league or not and then D to see if it was a win loss or draw. But the streak would reset as i could only get each cell to refer to the previous cell in the column and not the previous match at home in the premier league, and as it didnt meet the conditions the streak would reset to 0. I need a way to be able to use a formula to be able to only take into account matches in the prem at home for this, or a way to give all matches in this column that arent in the prem at home a 3rd value that it could ignore or something. I cant remove the other results as they are used for all the other calculations in other columns, and i would like to avoid using specific formulas for each cell as this is attached to a query supplying the data and i need it to update and carry on with each new row for a new fixture addition. And i would also like to avoid using more query's as this could easily quadruple the amount of query's that would have to be involved.

Thanks for any help!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Threads
1,127,266
Messages
5,623,714
Members
415,983
Latest member
MusicMan

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top