N
Neil Grantham
Hi
I have a sheet that works out averages for scores by riders in home
and away matches in different competitions.
The summary is basically
Matches, Rides, Points, Bonus, Total (Points + Bonus) Average
(Total/Rides * 4)
Each person will not compete in each meet, so for the total matches I
use
=COUNTIF($P2:$P49,">0") - This counts the number of occasions that
there is a number above 0, with 0 being a non-competed match.
For All matches the other columns are =SUM
Total adds 2 cells
Average is a Total divided by rides and multilied by 4
Now I want to do a breakdown of Home and Away for each competition.
I have columns denoting competition code and Home (H) and Away (A)
Thus, my Points cell for Home is
=SUMPRODUCT(($D2:$D49="H")*($E2:$E49="ELA"),P2
49)+SUMPRODUCT(($D2:$D49="H")*($E2:$E49="ELB"),P249)
So, it is Competitions ELA and ELB at Home
Works fine so far, as do other cells for the Bonus and number of
rides.
Its the Home and away match totals in Competition ELA and ELB that has
stumped me.
I think I need a combination of COUNTIF and SUMPRODUCT?
The final result should look like
M R Pts B Ave
All Matches 2003 38 185 394 29 9.15
League (Pink Average)Home 14 69 150 10 9.28
Away 12 61 112 11 8.07
Total 26 130 262 21 8.71
It's those Match figures 14 and 12 I need to calculate
I hope I've put that across clearly
Thanks for any advice
Neil
I have a sheet that works out averages for scores by riders in home
and away matches in different competitions.
The summary is basically
Matches, Rides, Points, Bonus, Total (Points + Bonus) Average
(Total/Rides * 4)
Each person will not compete in each meet, so for the total matches I
use
=COUNTIF($P2:$P49,">0") - This counts the number of occasions that
there is a number above 0, with 0 being a non-competed match.
For All matches the other columns are =SUM
Total adds 2 cells
Average is a Total divided by rides and multilied by 4
Now I want to do a breakdown of Home and Away for each competition.
I have columns denoting competition code and Home (H) and Away (A)
Thus, my Points cell for Home is
=SUMPRODUCT(($D2:$D49="H")*($E2:$E49="ELA"),P2
49)+SUMPRODUCT(($D2:$D49="H")*($E2:$E49="ELB"),P249)So, it is Competitions ELA and ELB at Home
Works fine so far, as do other cells for the Bonus and number of
rides.
Its the Home and away match totals in Competition ELA and ELB that has
stumped me.
I think I need a combination of COUNTIF and SUMPRODUCT?
The final result should look like
M R Pts B Ave
All Matches 2003 38 185 394 29 9.15
League (Pink Average)Home 14 69 150 10 9.28
Away 12 61 112 11 8.07
Total 26 130 262 21 8.71
It's those Match figures 14 and 12 I need to calculate
I hope I've put that across clearly
Thanks for any advice
Neil