Making a gambling spreadsheet

**damian5000** · 09-05-2007, 04:18 PM

I'm making a boxing spreadsheet to keep track of picks with a "fantasy stake". I need help with just 1 expression.

It needs to :

=the amount above it (total) +/- the cell above 1 and to the left 1 (win$ or lose$ amount)

Can anyone help with this? The total is in K:K and the win/lose $ is in J:J

Thanks!

**BigBas** · 09-05-2007, 04:43 PM

I dont fully understand your request. Can you provide an example of sample data, outlining what you would like to do?

With that said, generally speaking, i think the OFFSET function can help you out.

**damian5000** · 09-05-2007, 05:55 PM

Hey... thanks...

it's Total=Total+$ lost or won on the bet

I want to make it so, what I type in the $ won or lost in one column and make it automatically add to the running table in the next column as shown:

Win/Loss$ Total
20--------5000
-50-------5020
800-------4970
30--------5770
-500------5800
-----------5300

**Dean England** · 09-05-2007, 08:23 PM

I think this is what you want. If not come back. With your $5000.00 figure in cell K1, place this formula in K2 and copy down.

=IF(J2="","",K1+J2)

Then just plug your -50 or 100 or whatever in J2, J3, J4, etc.

Is that what you want?

Dean

**damian5000** · 09-05-2007, 09:03 PM

Actually this is exactly what I wanted! Only problem is I have some other equations involving the K column and in place of the equation I get "#VALUE!" now. Which makes sense because K is blank until the total gets there.

I've got one more hard one, that's not too important. The sheet is functional as is now

But I had to make 2 columns because there are 2 equations depending on if my pick is the favorite or the underdog. American odds are posted like this:

These equations show me what my winnings would be if I won the bet.

G:G=wager C:C=odds

Favorite---Fighter A : -250 G:G*-1*(100/C:C)
Underdog--Fighter B : +160 G:G*(C:C/100)

Is there some way to have one expression that says something to the effect of "if number in C:C is positive then use G:G*(C:C/100)... if number in C:C is negative use G:G*-1*(100/C:C) so I don't have to have 2 columns?

**Dean England** · 09-06-2007, 09:38 AM

damian5000,

Glad that worked for you.

It might be easier to envision what's going on with your project, if you could post a small zipped sample of your data. Then I, or someone else may be better able to help you.

Dean

**damian5000** · 09-06-2007, 10:51 PM

ok!

Pick------------Odds--Wagered---To Win-----Total

Mayweather===-270====100======$37=====5000

Glass Joe=====+800====100=====$800=====5037

If the odds are positive, I need to use

amount wagered*(odds/100)

If the odds are negative, I need to use

amount wagered*-1*(100/odds)

As it is now I have 2 columns for "To Win" because of the 2 different equations needed. What I want is to have one "To Win" column that detects if the odds entered are positive or negative and then to use the appropriate equation