Evaluating Betfair betting history

If you have  have  10 to 15 minutes and want to do a reasonable job of setting up your spreadsheet
to analyse your downloaded betting history  ~  continue.

Here we go!

Do this step by step ~
After downloading and the spreadsheet is open, select the first five rows and click insert rows.
These extra rows can now be used for analysis with subtotals and other formulas.

Now click in any blank cell and click copy
Now select cell I8
Now while holding down the Shift Key click on the last cell that has value in column M
On the home menu there is a paste box (top left) Click on the word paste, in the drop down menu click
paste special, in the box that opens click values and the add then click Ok
All the columns of numbers that were text should now be numbers that can be calculated.

In cell O1 type 100
In cell O8 type =$O$8
In cell =P8 type (replace the ?  With the less than symbol (shift coma key))
=IF(M8?0,-100,ROUND(O8/(L8-1),2))
Select O8 and P8 and fill down to the bottom of the data.

Now for some formulas to sum, average and count.
In cell K1 type Number of bets
In cell K2 type  Profit or Loss
In cell K3 type Average Dividend
In cell L1 type  =Subtotal(2,P8:P9999)
In cell L2 type  =SUBTOTAL(9,P8:P9999)
In cell L3 type  =SUBTOTAL(1,L8:L9999)

Now  highlight cells A7 through P7 and click on the filter symbol in the data menu
You can now filter your data to your hearts content, just by clicking those little triangles
The results will come up in column L at the top.
That's the basics now you can expand your dissections by adding different formulas anywhere in the top 5 rows.

Typo in
In cell O8 type =$O$8
should be =$O$1

When and if you do the above, say you wanted to find how many losers you had
click on the triangle in cell P7 click select all to empty all boxes then tick
next to -100. In L column up the top it will tell you how many losing bets you had and
how much you lost on those. When finished click on the triangle again and click clear all.
the 100 in cell O1 can be any amount, that amount will be reflected in all of column O

Any questions or problems you have I will try to answer.
Cheers.

Have to change the formula in cell P8
The original formula was for lays only at a liability of 100

New formula for cell P8 and then fill down. Replace all Question marks (?) with less than symbol.
and replace all # with more than symbol. I can't get those symbols to show in the formula.
Copy and paste this formula into cell P8 and make the edits as above.

=IF(AND(D8="Lay",M8?0),-O8,IF(AND(D8="Lay",M8#0),ROUND(O8/(L8-1),2),IF(AND(D8="Back",M8?0),-O8,IF(AND(D8="Back",M8#0),ROUND(O8*(L8-1),2),0))))

Should be ok now.

Much appreciated - will give this a go this afternoon.

Thanks again.