Table Tennis Tracker

[seriousdamage]

Hello All,

I am trying to put together a table tennis tracker of my matches.
The part I need help is to calculate the winning % over 10 matches.

So the team can make maximum of 10 point per night
We make 10 point and the % should be 100%, this I can do, where I get stuck is from the second match onwards

The table has 10 columns one per match where I will enter after every much the number of points we made which can be from 0 to 10
On the 11th column I have to total amount of point and on the 12th column I like to have the %
How can I write the formula that will not require me to modify it every time I play a new match?

Many thanks for your help

 

[Mel Smith]

Hi,

Try the following formula in the %age cell (formatted as percentage):

In row 1: =cell ref of total wins divided by 10
In row 2: = (cell ref to wins + row run cell ref of wins) divided by 20 and so on.

I.E. 1: =K2/10
2: =(K2+K3)/20
3: =(K2+K3+K4)/30
4: =(K2+K3+K4+K5)/40 etc etc

HTH

Mel

 

[seriousdamage]

I am not 100% sure what you mean sorry.
I added a template at the following dropbox link,
from column D to M I will update once a week after every match
The formula I need should go to column O

I hope you understand what I am trying to do
https://www.dropbox.com/sh/n5kvrxzdmd5so56/AAC-NrTjYZopYeTFMAuaaqvya?dl=0

 

[Mel Smith]

What is the maximum total that could be shown in N4:N9? Clearly it is more than 10 as seen in N5.

Mel

 

[seriousdamage]

the maximum number per cell is 10 over 10 matches we could accumulate 100 points assuming we win all matches 10:0

 

[Mel Smith]

First of all I have inserted a 'helper' column between Column N and O, which can be hidden. In the helper column cells O4:O9 I have put the following formula: =COUNTA(D4:M4)

In cell P4: =N4/(O4*M2)
In cell P5: =(N5+N4)/((O4+O5)*M2)
In cell P6: =(N4+N5+N6)/((O4+O5+O6)*M2)
In cell P7: =(N4+N5+N6+N7)/((O4+O5+O6+O7)*M2)
In cell P8: =(N4+N5+N6+N7+N8)/((O8+O7+O6+O5+O4)*M2)
In cell P9: =(N4+N5+N6+N7+N8+N9)/((O4+O5+O6+O7+O8+O9)*M2)

Once all formulas have been entered, you can hide the 'new' helper column (column O)

Mel