[Semper Fidelis]

First, I apologize if I have posted this in the wrong forum. I saw some other Excel and spreadsheet questions here, so I assumed this was the correct forum. I just need some help with some formulas. It's been forever since I've used Excel, and this version of course is more advanced than what I originally learned several years ago. I have Excel 2007, and I am trying to track an underdog system for baseball to check its profitability. What formula do I use so that totals are automatically calculated? For example, say I use the A column for the team names, the B column for the odds, the C column for the result (W/L), and the D for the $Amount won/lost. Since the odds in the B column will always be + because I am using underdogs, what formula do I use so that when I type in W (for win) in the C column, D will automatically put in whatever the odds were in the B column because that is the $Amount actually won? Conversely, if I type in L (for loss) in the C column, it'll automatically put -100 (assuming 1 unit is $100)? I also want to keep a running calculation every day of the W/L, %, and total $ amount won/lost for the day, as well as for the season. Maybe it'll help to show an example:


A B C D
1. June 1
2. Braves +125 W +125
3. Orioles +137 L -100
4. Results
5. 1-1 50% +25
6.
7. June 2
8. Phillies +113 W +113
9. Rays +142 L -100
10. Blue Jays +117 W +117
11. Results
12. 2-1 (3-2) 66% (60%) +130 (+155)

I don't know where or if it's even possible to keep a running tally for the entire season, as well as specific days of the week. In this example the numbers in the ( ) represent the season totals. I also want to keep a tally of the bankroll and the % increase as well as units won. I know I could just calculate it myself and just type in the answers, but I know there is an easier way to where it'll automatically input the answer for me in the correct columns. I just don't quite remember how to create the formulas for this. Sorry if this is confusing or if I didn't do a good job explaining. Any help at all would be greatly appreciated!

[Semper Fidelis]

Crap I meant to put it in a table so it actually looks right and makes sense:

A	B	C	D
1. June 1
2. Braves	+125	W	+125
3. Orioles	+137	L	-100
4. Results	1-1	50%	+25
5. June 2
6. Phillies	+113	W	+113
7. Rays	+142	L	-100
8. Blue Jays	+117	W	+117
9. Results	2-1 (3-2)	66% (60%)	+130 (+155)

[thom321]

I am ignoring the word "system", since if this is a system without regard to the odds offered vs some fair odds, it will fail in the long run and tracking it is pointless.

In the attached spreadsheet, I have created something that might work for you. I included a way to look at a cumulative total as well as for each date. I did not do anything regarding "days of the week" since I hope you were not trying to see how games on Fridays did vs games on Tuesdays.

Cells highlighted in light green are formulas and you should not enter any data in those cells.

You can enter any line odds, not just for dogs, in case you decide to use it for some other purpose.

[PharaohUB]

something like this

=IF(C2="W",B2,IF(C2="L","-100","Result Pending"))

this basically says if the cell C2 is W then it returns the value in b2 (your plus money), if value in cell C2 is L then it would put -100 in that cell. If nothing is entereted is cell C2 it would say Result Pending.

This formula only works if you are betting 1 unit ($100) at a time, and only betting dogs. It would get more complicated if you wanted to put favorites in there too. Just wanted to keep formula as simple as possible. You may want to add a column for units and build that into formula as well.

Let me know I don't mind helping it will take me like a minute to write any formula you need.

[Waterstpub87]

something like this

=IF(C2="W",B2,IF(C2="L","-100","Result Pending"))

this basically says if the cell C2 is W then it returns the value in b2 (your plus money), if value in cell C2 is L then it would put -100 in that cell. If nothing is entereted is cell C2 it would say Result Pending.

This formula only works if you are betting 1 unit ($100) at a time, and only betting dogs. It would get more complicated if you wanted to put favorites in there too. Just wanted to keep formula as simple as possible. You may want to add a column for units and build that into formula as well.

Let me know I don't mind helping it will take me like a minute to write any formula you need.

You could edit that formula to in put in the favorite wins and loses

=if(c2="w",if(B2 >0,B2,-100),if(B2>0,-100,B2)

Note:don't use the keyboard for the >, find character map on your computer and copy paste the symbols, otherwise excel won't recognize it.

[Semper Fidelis]

I think I'm starting to understand now. Where would I go to insert the actual formula? I'm trying to make it where if the result in C (for the entire column, regardless of the row) is W then D will automatically input whatever is in B, and if C is L then it will input -100 in D. How do you make it effective for the entire column? Thanks for all of your help and advice!

[thom321]

Did you check my Excel file?

[Semper Fidelis]

Yes, that's pretty much everything I need! To continue the formulas for the entire column, I can just drag the last box all the way down to whatever number row I want, correct? That seemed to work. Is this pretty much the spreadsheet you use to track your bets as well? Thank you so much for your help!

[thom321]

Copying the formulas should work just the way you said. This spreadsheet I just created today and not something I use myself, although the one I use has similar features.

[Semper Fidelis]

Copying the formulas should work just the way you said. This spreadsheet I just created today and not something I use myself, although the one I use has similar features.

How different is the one you use? Is it more in depth with the stats? Maybe it is a better spreadsheet to keep track of everything.

[thom321]

The one I created for myself is complicated, not user friendly and not intuitive so it is not one I am going to share. However, take a look at the one that was mentioned in the post I link to below.

http://www.sportsbookreview.com/forum/handicappe...l#post14728388