Excel formula help

[thorns]

Lets say on my sheet I am recording results in column A which dont include commission. If I want the result after commission to be calculated into column B next to the result, What would be the formula to make it calculate a 5% commission on a positive result but leave a loss value as it is?

I'm guessing it will be some king of IF statement saying if positive multiply by 0.95, but IF negative value do not multiply and simply populate value with same figure.

ie
ColumnA/columnB
100/95
-100/-100

[Shaun]

=if(A1<0,A1,A1*.95)

[UselessBettor]

=if(A1<0,A1,A1*.95)

Yep Shaun has it right there. If your using OpenOffice instead of Excel you would just use ; instead of ,

If you are doing it on Aus racing I suggest using the real commission per race which varies. If your not sure then default to 0.935 (6.5% commission) for testing.


To sightly improve it :

=if(A1<0,A1,ROUNDDOWN(A1*.95,2))

[thorns]

perfect, thanks guys. Yeah planned on changing race state by state, but didnt want to make to confusing for a start.

Those formulas work perfect for what I am after though.

Cheers!

[Shaun]

When i am testing i prefer to use 8% if i don't have the exact percentage, If you use a whole number like 6 or 8 or 10 you can use this formula to covert it.
=1-(8/100) this will produce .92

[thorns]

Its amazing seeing the difference even a small change in commission makes when you do a graph of the two lines.

Really makes me think twice now about playing in the NSW markets at 10% base rate.

[UselessBettor]

Its amazing seeing the difference even a small change in commission makes when you do a graph of the two lines.

Really makes me think twice now about playing in the NSW markets at 10% base rate.

Yes it can make a huge difference but make sure your doing the commission by market. Remember if you had 2 or more horses in the same race then your commission is only on the profit for the race. Makes it a lot harder to work out.

[Chrome Prince]

Those formulae will work. The rise in commission has made Betfair very uncompetitive when you consider that bookies are offering *Goat, Odds boost, enhanced odds etc. Except for the longer end of the market.