excel math question

[tbiala]

ok so im trying to create a spreadsheet for myself to track my wagers and keep track of amount won/lost. in cell H2 i have the juice ie-110, in cell I2 i have amount risked, ie $100, in cell J2 i have result, win/loss/push

In cell K2 i have a if function to figure out how much was won/lost. heres what i got so far

=IF(J2="Win",(I2*H2)/100,IF(J2="Push",0))

I didnt ad in the,IF for the loss becasue i am stumped on the If J2=Win. this is not the right forumula because this only works for when i have positve juice. If i have juice of +110, and risking $100 and the result is a win this formula gives me the correct earnings of $110. but if i have juice of -110 and risking $110 it gives me earning of $-121on a win. how do i make the earning be $100.

What am i doing wrong in this formula?

[twister]

=if(and(j2="win", h2>0), i2*(h2/100), if(and(j2="win",h2<0),1/abs(h2)*100*i2, if(j2="push", 0, -i2)))

[tbiala]

Awesome!! thanks so much i probably never would have figured that out

[Ganchrow]

Or, if you had my http://forum.sbrforum.com/handicappe...ate-excel.html installed (and you certainly should) then you could drop a conditional and just use:

=IF(J2="WIN",US2WIN(H2,I2),IF(J2="LOSS",-I2,0))

(Note that US2Win() was only added as of version 1.0.1.14g. If you have an earlier version you can just use (US2DEC()-1) in place of US2Win(). Or better yet ... you could just download the newer version. To determine version number use the function =SBRVer()).

[twister]

Awesome!! thanks so much i probably never would have figured that out

I noticed I had made a mistake, left one of the cells as "59" instead of "2" (as I was testing it in row 59), so just make sure the whole formulae says "2", and not "59".

Unless you wrote it into excel yourself, in which case I assume you used the correct cells/rows.

[Ganchrow]

Or, if you had my http://forum.sbrforum.com/handicappe...ate-excel.html installed (and you certainly should) then you could drop a conditional and just use:

=IF(J2="WIN",US2WIN(H2,I2),IF(J2="LOSS",-I2,0))

(Note that US2Win() was only added as of version 1.0.1.14g. If you have an earlier version you can just use (US2DEC()-1) in place of US2Win(). Or better yet ... you could just download the newer version. To determine version number use the function =SBRVer()).

Or ... with Simple VBA Sports Betting Functions Template for Excel v1.0.1.15a or higher installed one could use the even simpler:

=US2Res(H2,I2,J2)

Where:

H2 = US-style Odds
I2 = Wager Amount
J2 = Result ("W", "Win", or "1", for win; "L", "LOSS", or "-1" for loss;
     or otherwise for push)

[tbiala]

I read this from my phone so I am posting this without being able to open up the excel book. But these are just formulas saved in excel that all relate to calculating gambling stuff? They just seem complicated I'm sure there not to the avwerage excel user but I would love to figure them out. Ill be home in about a hour to be able to open the book n play with them

[tbiala]

1. {Bets2Stats(range of Odds, range of Wager Quantities {default=1}, range of Outcomes, range of Edges {default=0%), Decimal Odds Flag = {default = FALSE})}: Array function. Takes a range of betting odds (US odds are the default, but will take decimal odds if the Decimal Odds Flag argument) is set to TRUE, an optional range of wager quantities (if not provided then 1 unit per wager is assumed), a range of outcomes (1 or a string starting with 'W' for a win, -1 or a string starting with 'L' for a loss, anything else for a push/no action), and a range of expected edges (defaults to 0). Returns an array with the following values:
   1. Number of Non-Pushed Bets
   2. Number of Wins
   3. Win %
   4. Unit Return
   5. % Return
   6. Unit Standard Deviation
   7. % Standard Deviation
   8. Standard Score
   9. p-value (from t-distribution)

How do i do this???? And am i able to with what i am currently working with. Here is a paste of what i will input for each bet and i used ur formula to calculate the money won/lost in the far right column:


DateTimeBookVisitorHomeAngleWagerTeamSpreadJuiceRiskResult$ Won/Lost-110$100.00Win$90.91 $0.00 $0.00 $90.91

This is a example of my baseball sheet theres drop down boxes for every box. i am also going to keep a main page and have mu beginning bankroll then calculate the money won lost to give me a current bankroll. i also want to figure out a way to keep track of how much money i have in each book. im going to use about 4 books so i can shop lines. and i have a column for book where i will select which book i am going to use to bet. i am going to figure out a way to list out on the main page the 4 books i used followed by current bankroll in each based on the results of the wagers

[tbiala]

A B C D E F G H I J K L M
1. Date Time Book Visitor Home Angle Wager Team Spread Juice Risk Result $Won/Lost

the paste of the spreadsheet didnt work. so these are the headings i will be using. book, is where ill select which book to use. angle is just my way of tracking how i came up with bet, ie line movement, hunch, etc. wager is type of wager ie, run line, moneyline, over, under.

i suppose i could keep track of how much money i have in each book by a pivot table? would that be the easiest