A formula for strike rate in excel

[Jonny2621]

Its me again (sorry Mat :peeky ) with another excel question....

I want to have a running calculation of strike rate in a spreadsheet where column F from starting with row 4, has 1 in it for a winner or is left blank for a losing bet.

Anyone able to guide me as to the formula to put in to show the running s/r ?

TIA

[presto]

what i do is have collum A named 'selection' (put a 1 for every selection i have) - and as you do have a collum F (win / lose) - 1 for a win.
then at the bottom of the spreadsheet i total up how many selections there have been (sum of collum a) and how many winners there have been (sum of collum F). - i then work out the SR form the total numbers of selections and winners.

[Jonny2621]

Thats an idea Presto, I can convert it reasonable easily to two colums, then even i can do the sum from there......yes even me :)

[mathare]

What you can do is for each row in column G is enter:
=sum(F$1:F[row])/(count(F$1:F[row])+counta(F$1:F[row]))

That assumes row 1 is the start of the data. If not, change it to 2, 3 or whatever. Enter the formula on the first row of your data (setting <row> to whatever the row number is) and you can then select many cells in column G and do Fill -> Down to fill the equation down into the selected cells. The equation will autoupdate [row] in each cell.

What it is doing is summing the number of winners and dividing by the number of cells (which itself is the number of cells containing data plus the number of cells which don't).

You can improve on this by making it:

=if(isblank(F[row]),"",sum(F$1:F[row])/(count(F$1:F[row])+counta(F$1:F[row])))

if you don't want the SR to display on a row that doesn't have a result filled in.

NB I haven't tested that formula for syntax but I am pretty sure it's either right or close to being right

[Jonny2621]

Am I the only one who doesn't understand all this ?? :D :splapme :yikes: :waver :lickme :helper


What you can do is for each row in column G is enter:
=sum(F$1:F[row])/(count(F$1:F[row])+counta(F$1:F[row]))

That assumes row 1 is the start of the data. If not, change it to 2, 3 or whatever. Enter the formula on the first row of your data (setting <row> to whatever the row number is) and you can then select many cells in column G and do Fill -> Down to fill the equation down into the selected cells. The equation will autoupdate [row] in each cell.

What it is doing is summing the number of winners and dividing by the number of cells (which itself is the number of cells containing data plus the number of cells which don't).

You can improve on this by making it:

=if(isblank(F[row]),"",sum(F$1:F[row])/(count(F$1:F[row])+counta(F$1:F[row])))

if you don't want the SR to display on a row that doesn't have a result filled in.

NB I haven't tested that formula for syntax but I am pretty sure it's either right or close to being right

[mathare]

Post your spreadsheet...

[Jonny2621]

I'll IM you Mat if I may, I seem to have achieved a 99% S/R......which is nice obviously

[mathare]

E-mail/PM me, yeah. I'll fix it for you. Although if you have got a 99&#37; SR system I may want the rules in return for my help :D

[Jonny2621]

E-mail/PM me, yeah. I'll fix it for you. Although if you have got a 99% SR system I may want the rules in return for my help :D

Rixxy charges £3000 a year doesnt he ? :D

[MarcusMel]

Assuming 1 is a winner and 0 is a loser and all other cells in the col are blank. Then it does not matter how many entries you have with the following.

=Sum(F(start row):F(65536))/Count(F(start row):F(65536))

Which is a lot easier than trying to count blank cells.

[mathare]

But that assumes 0 is a loser which is not what Jonny's s/sheet does at present.

And counting blank cells is easy. MS wrote a function to do it for you.

[MarcusMel]

Just saying it is easier. Filling in the blanks with zeros is easy too. If you want to be difficult and create formulas you later wonder what they are doing in them that is also fine.

In fact if you just record profit and loss you are also recording your winners and losers at the same time so if profit/loss was recorded in say colmn D you could do in Col F

=If(D4<0,0,if(D4>0,1,"No bet"))

[mathare]

I do it the other way round, let the result (won/lost) determine my profit

[MarcusMel]

Not always accurate as you get the occasional rule 4.

[mathare]

Not always accurate as you get the occasional rule 4.I check for them and update my odds accordingly

[MarcusMel]

You can find the odds you bet at by profit/stake both of which are easily available! So you dont need to check that either. All you need to know is the amount you won and stake. Keeps things simple in my opinion.

[mathare]

You can find the odds you bet at by profit/stake both of which are easily available! So you dont need to check that either. All you need to know is the amount you won and stake. Keeps things simple in my opinion.But to know the amount won on each bet means having the bookies/exchange site logged in and going through the accounts. My way means I can just use the SL pages to update my s/sheet and double check any R4s every few days or so.

Anyway, it's about what finding what works best for you. My way suits me, your way obviously suits you. There is no single right way of doing things