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