Calculating the average ("hitrate") of fotball results

[matchedbettingnorge]

<style><!--table {mso-displayed-decimal-separator:"\,"; mso-displayed-thousand-separator:" ";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {color:black; font-weight:700;}.xl64 {font-weight:700; background:yellow; mso-pattern:black none;}.xl65 {color:black; font-weight:700; background:yellow; mso-pattern:black none;}--></style>I want to find the average (or the "hit rate") of World Cup football results. I have calculated 2 points for correct 1X2, and 3 points for correct 1X2 and correct score. So the hit rate should be "yes" or "1" for 2 and 3 points and "no" or "0" for 0 points. I tried using the AVERAGEIFS, but get a DIV/0 error.

So, the result should be a % of the correct outcomes of the total games played, but not including the games not yet played in the average. In this case (after 10 games) the hit rate is 70%.

Probably not a difficult question, but I'd really appreciate the solution

[TABLE="width: 261"]
<!--StartFragment--> <colgroup><col width="87" span="3" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 87"]1X2[/TD]
[TD="class: xl65, width: 87"]Points[/TD]
[TD="class: xl64, width: 87"]"Hit rate"[/TD]
[/TR]
[TR]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Away[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Draw[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Away[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Away[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Draw[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Draw[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Away[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Away[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Not Played[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]1X2[/TD]
[TD]Points[/TD]
[TD]"Hit rate"[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Home[/TD]
[TD]

2​

[/TD]
[TD]

70%​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Away[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Draw[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Away[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Home[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Away[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Draw[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Home[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Draw[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Away[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Away[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

28​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

29​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

30​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

31​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

32​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

33​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

34​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

35​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

36​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

37​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

38​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

39​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

40​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

41​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

42​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

43​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

44​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

45​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

46​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

47​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

48​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

49​

[/TD]
[TD]Not Played[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



c2=SUMPRODUCT(COUNTIF($B$2:$B$49,B4:B5))/10 format c2 as %

 

[matchedbettingnorge]

c2=SUMPRODUCT(COUNTIF($B$2:$B$49,B4:B5))/10 format c2 as %

Thanks! But I want to update C2 when the results of the upcoming games are played. If i /10, that would only calculate the hitrate for the first 10 games. I need the formula to calculate the average for B2:49, and excluding the cells where A="Not played".

 

[matchedbettingnorge]

c2=SUMPRODUCT(COUNTIF($B$2:$B$49,B4:B5))/10 format c2 as %

Thanks! Looks like my answer did get posted. I need the hit rate calculated after the matches are played. If I put /10 I only get the first 10 matches. I need a formula where I calculate the average for the matches played (only the cells with "Home", "Draw", and "Away". Excluding "Not played".) which automatically includes the played matches as they are played (When A13:49=Home/Draw/Away, it will be included in the average hitrate)

Thanks again!