Excel problem: How to figure out whether I beat the spread

[brettd]

I can't figure out a formulaic way to figure out whether my bet was a win or loss against the spread on my evaluation spreadsheet.

Can someone with a bit of excel nouse help me out? I've attached the spreadsheet with a couple rounds of data attached.

[buby74]

Use the if function. Eg =if(result>adjline,"win",if(result<adjli ne,"loss","push"))
Where result is the cell with the result of the game and adjline is the cell with the line. I'm doing this on my iPhone so I can't type fast but if statements are essential to annalyse data like this. Can I ask how you derived the percentage win figure and what model do you use for the afl

[brettd]

Hey buby,
That solution doesn't work. The first few bets on the list and the spread result are incorrect. I just can't get my head around how to do this in excel.

This 'model' is just an ELO-based method I kacked up for a university assignment.

[roasthawg]

Can't post the formula... the website edits it for some reason. I'll pm it to you.

[Roy Halladay]

i'd like to have this formula as well

[roasthawg]

Tried again... won't let me post it, edits the formula. Sorry guys.

[brad89]

Tried again... won't let me post it, edits the formula. Sorry guys.

Put it in code tags and it should be fine.

[uva3021]

[uva3021]

he's right the formula gets automatically shortened, code tags or not

[Indecent]

Try using pastebin if you can't get it to work here.

[buby74]

Your terminology is a bit confusing but i think I have it sorted.

I assume that in the first game the home team melbourne lost to St.kilda by 31 points but the pointspread had melbourne as 9.5 point favourites but your system classed melbourne as a five point underdog (so the signs in columns E and F have opposite meanings) therefore your edge (adj line) was 14.5 against melbourne so you bet on the saints as team 2.


if I am right the formula to put in cell Q10 is =IF(N5+O5=0,"no pick",IF((E5+F5)*J5>0,"w","l"))
This firstly ignores games where you didint make a pick because your adjusted line was close to zero, it then works out the hometeams performance against the spread (e5+f5) if this is positive the home team won against the spread, if it is negative the home team lost against the spread. I then multiplied this by your adj line which was positive if you backed the home team and negative if you backed the away team. So if the product of these two numbers is positive you either backed the home team who covered (+ X +) or you backed the away team and the home team didint cover (- X -) which is a win in eithercase.

So in week 1 you went 1-5 ats in week 2 7-0 ATS

have I interpreted your spreadsheet correctly?

How big is pointspread compared to moneyline in AFL?

[brewers7]

I am with Buby74, I don't understand who is winning these games and what the spread is...The formula isn't hard to figure out if I knew who won the game and who is covering the spread...

[brettd]

I've got this sorted, but for people's reference in adapting to their own spreadsheets:

Multiply the 'Expected' column by -1, and you get the proper favourite/dog outputs.

For example, Melbourne should be a 5 point dog, -5 * -1 = 5.

[vyomguy]

try pastebin