696 | 579 |
698 | 676 |
708 | 696 |
714 | 749 |
696 | 635 |
696 | 738 |
747 | 610 |
753 | 696 |
754 | 696 |
696 | 675 |
Team name | Team name | Rating | Result - 6 | Result - 5 | Result - shift 0 | Result win % - 5 both col | |
Wigan Athletic | Bolton Wanderers | 5 | 10 | 9 | 1 | 80% | |
Fulham | Everton | 4 | |||||
Manchester City | Tottenham Hotspur | 2 | |||||
Wigan Athletic | Newcastle United | 5 | |||||
Manchester United | Bolton Wanderers | 4 | |||||
Tottenham Hotspur | Chelsea | 1 | |||||
Reading | Birmingham City | 4 | |||||
Wigan Athletic | Derby County | 2 | |||||
Newcastle | United Fulham | 4 | |||||
Bolton Wanderers | Manchester City | 4 | |||||
Wigan Athletic | Portsmouth | 1 | |||||
Aston Villa | Sunderland | 1 | |||||
Everton West | Ham United | 1 | |||||
Blackburn Rovers | Wigan Athletic | 0 | |||||
Wigan Athletic | Arsenal | 2 | |||||
Manchester United | Liverpool | 2 | |||||
Wigan Athletic | Arsenal | 4 | |||||
Manchester United | Aston Villa | 1 | |||||
Wigan Athletic | Blackburn Rovers | 2 | |||||
Derby County | Fulham | 1 | |||||
Birmingham City | Manchester City | 1 | |||||
Wigan Athletic | West Ham United | 0 | |||||
Portsmouth | Wigan Athletic | 2 | |||||
Liverpool | Everton | 4 | |||||
Chelsea | Middlesbrough | 1 | |||||
Wigan Athletic | Newcastle United | 1 | |||||
****************** | ****************** | ******* | ** | ********* | ********* | ************** | *********************** |
Use Ctrl+Shif+Enter to enter the formulas
In E2
=SUM(IF(ROW($C$2:$C$27)>=LARGE(IF($A$2:$A$27=$A$27,ROW($C$2:$C$27)),MIN(6,COUNTIF($A$2:$A$27,$A$27))),IF($A$2:$A$27=$A$27,$C$2:$C$27)))
In F2
=SUM(IF(ROW($C$2:$C$27)>=LARGE(IF($A$2:$A$27=$A$27,ROW($C$2:$C$27)),MIN(5,COUNTIF($A$2:$A$27,$A$27))),IF($A$2:$A$27=$A$27,$C$2:$C$27)))
In G2
=SUM(IF(ROW($C$2:$C$27)>=LARGE(IF($A$2:$A$27=$A$27,ROW($C$2:$C$27)),MIN(5,COUNTIF($A$2:$A$27,$A$27))),IF($A$2:$A$27=$A$27,IF($C$2:$C$27=0,1))))
In H2
=SUM(IF(ROW($C$2:$C$27)>=LARGE(IF(($A$2:$A$27=$A$27)+($B$2:$B$27=$A$27),ROW($C$2:$C$27)),MIN(5,COUNTIF($A$2:$A$27,$A$27)+COUNTIF($B$2:$B$27,$A$27))),IF($A$2:$A$27=$A$27,1)))/5
It's very simple database, looks like this.
WINNER LOSER
696
579
698
676
708
696
714
749
696
635
696
738
747
610
753
696
754
696
696
675
<TBODY>
</TBODY>
So in the last row I would like to have winning percentage of team 696 from previous 5 matches - it should be 0.4 in this case.
=SUM(IF(ISNUMBER(MATCH(ROW($A$2:$A$11),
LARGE(IF($A$2:$A$11=D$1,ROW($A$2:$A$11)),
ROW(INDIRECT("1:"&MIN(5,COUNTIF($A$2:$A$11,D$1))))),0)),1))/
ROWS($A$2:$A$11)
WINNER | LOSER | |||||
696 | 579 | |||||
698 | 676 | |||||
708 | 696 | |||||
714 | 749 | |||||
696 | 635 | |||||
696 | 738 | |||||
747 | 610 | |||||
753 | 696 | Last 5 games 696 | Last 5 games 696 | Last 5 games 696 | Last 5 games 696 | Last 5 games |
754 | 696 | Win | Win/last 5 games 696 | Win/all games 696 | Win/all games | Win 696/all games |
696 | 675 | 3 | 0,60 | 0,43 | 0,30 | 0,40 |
******** | ******* | **************** | ********************* | ****************** | **************** | ****************** |
In C11 - use Ctrl+Shift+Enter to enter the formula
=SUM(IF(ROW($B$2:$B$11)>=LARGE(IF(($A$2:$A$11=$A$11)+($B$2:$B$11=$A$11),ROW($B$2:$B$11)),
MIN(5,COUNTIF($A$2:$A$11,$A$11)+COUNTIF($B$2:$B$11,$A$11))),IF($A$2:$A$11=$A$11,1)))
In D11 - use Enter to enter the formula
=$C11/5
In E11 - use Enter to enter the formula
=$C11/(COUNTIF($A$2:$A$11,$A$11)+COUNTIF($B$2:$B$11,$A$11))
In F11 - use Enter to enter the formula
=$C11/ROWS($A$2:$A$11)
In G11 - use Ctrl+Shift+Enter to enter the formula
=SUM(IF(ROW($B$2:$B$11)>=LARGE(IF($A$2:$A$11=$A$11,ROW($B$2:$B$11)),MIN(5,COUNTIF($A$2:$A$11,$A$11))),IF($A$2:$A$11=$A$11,1)))/ROWS($A$2:$A$11)
Hi Guys
Thanks to all the people who have tried to solve this issue. This thread was exactly what I was looking for. Although it has been more than a year since someone used this thread, I still hope to receive an answer
Unfortunately the provided solutions do not work for my excel 2007.
I wasn't really able to track the error, but in example for the code
=SUM(IF(ROW($C$2:$C$27)>=LARGE(IF($A$2:$A$27=$A$27,ROW($C$2:$C$27)),MIN(6,COUNTIF($A$2:$A$27,$A$27))),IF($A$2:$A$27=$A$27,$C$2:$C$27)))
there is no solution, excel gives #VALUE?. If I try =IF($A$2:$A$27=$A$27,ROW($C$2:$C$27) also #VALUE?
Hopefully someone is able to help me.
Also if you have an other solution, this would be fine for me. I am only looking for an answer to the initial question in this thread, as I am trying to generate a trend for the quality of soccer teams.
Thank you guys!
You need to confirm this formula with control+shift+enter. That means: Press down the control and the shift keys at the same time while you hit the enter key.
You need to confirm this formula with control+shift+enter. That means: Press down the control and the shift keys at the same time while you hit the enter key.