Excel Strike rate FORMULA?

[Brovashift]

Hi all,

Can some please tell me the excel formula to calculate strike rate?

I have 21 rows, of which 2 are loses... Can I use the PnL column that is 'currency' type and divide the total by number of rows?
There's lots on Google but nothing is working!



TIA

[jamesedwards]

Hi all,

Can some please tell me the excel formula to calculate strike rate?

I have 21 rows, of which 2 are loses... Can I use the PnL column that is 'currency' type and divide the total by number of rows?
There's lots on Google but nothing is working!



TIA

You could use an IFS formula to identify Winners and Losers.
=IFS(A1>0,1,A1<0,-1,A1=0,"")

Or you could use COUNTIFS to add up the number of Winners and the number of Losers:
=COUNTIFS(A1:A10,">0")
=COUNTIFS(A1:A10,"<0")

[Brovashift]

Thanks James...

Just realised, the text in the cell was White! I was wondering why I couldn't see any results populating it

I tried =COUNTIFS(A1:A10,">0") and this gives me the total PnL in the cell for that range, but I already have this using =SUM(U2:U27), displayed in U29.

So I tried this: =ROUND(U29/COUNT(U2:U27),2), which gives me 40% when setting the cell to Percentage type.

But this cant be right because there's only 2 losing trades out of 22, which I think is about 90ish %

I cant think of the logic in my head, but isn't it something like; Total number of positive rows >0 / Total number of rows? (and convert to percentage)

[Brovashift]

I think this has nailed it...

=SUM(COUNTIFS(U2:U27,">0")/COUNT(U2:U27))

Does that look right?

Result = 90.91%

[Taff15]

the following should also work:

=COUNTIFS(U2:U27, ">0") / COUNTA(U2:U27)