Anyone a spreadsheet/macro whizz?

southernscouser

:undecided

Got a spreadsheet for calculating matched betting profits that I need to work in open office. The final two columns, which are profit/loss and total profit aren't calculating? All I get is #MACRO?

I don't understand this at all but it's made more complicated by depending on what is in column C from the drop down menu. For example if I had;

Date, Bookie, Type, Back stake, Lay Stake, Back odds, lay odds, Returns, P/L, Total Profit

9 Aug, BetFred, Qualifier, £100, £100, 2, 2, £100, £0, £0


But if I changed the bet type to Stake returned (SR) it should read;


Date, Bookie, Type, Back stake, Lay Stake, Back odds, lay odds, Returns, P/L, Total Profit

9 Aug, BetFred, SR, £100, £100, 2, 2, £100, £100, £100


Has anyone got a clue what I'm going on about, let alone how to fix it?

UPDATE: Post 13.

donny-gal

I presume that your Total Profit column has some kind of IF statement depeding on the conditio of Column C.

If you changed your spreadsheet so that you have a one digit to enter on colum C depeding on the type instead of a drop down box does that solve it?

DG

southernscouser

donny-gal wrote: »

I presume that your Total Profit column has some kind of IF statement depeding on the conditio of Column C.

If you changed your spreadsheet so that you have a one digit to enter on colum C depeding on the type instead of a drop down box does that solve it?

DG

Total profit says; =IF(A3="";"";(I3+J2))

It's the profit/loss column that is dependant on column C. This says; =IF(A3="";"";(ProfitCalc(D3;H3;C3)))

Sorry I haven't got a clue what you're on about. :rotfl:

Someone knocked this up for me.

donny-gal

OK

"" usually means there is nothing in that field

I'm a little rusty, so would need to look at it hands on, but if there is no sensitive data on there and you are prepared to email me a copy PM me with your email.
DG

c0rneL

in what you wrote there is a call to a macro called ProfitCalc. This can be either a macro or a a simple defined function to return a value out of 3 values imputed (D3;H3;C3).

If you do not have that function(it might have been in the normal template of the pc created the spreadsheet), you will need to create that function. This is relatively simple all you need to to is to identify the formula used to calculate the profit (out of the 3 values called ((D3;H3;C3)).

If you write me the formula to find the profit I can correct your forumla bypassing the macro.

m5rcc

For future reference: http://www.excelforum.com/excel-general/

PhilCo_2

If it's an xls file and using VBA than it won't work in Open Office as it uses a different language. Open Office basic is far more painful to work with. If you press Alt-F11 then you'll be able to get to the basic editor and find out what ProfitCalc is doing.

southernscouser

If anyone can help, I've uploaded it here.

http://www.mediafire.com/?1k1wsfntixd

I've put what the columns should calculate further along if that makes any sense. It's now got #NAME? Don't ask. :rotfl:

If anyone can fix it I'd be eternally grateful. :beer:

PhilCo_2

The Profitcalc function is basically saying if type equals "Qualifier", "Arb" or "Casino Offer" then do returns minus backstake. If "SR Free Bet", "SNR Free Bet", "Cashback" or "Referral" then just put returns in.

If you put this in I3 it should do the same...

=IF(OR(

C3="Qualifier";C3="Arb";C3="casino offer");H3-D3;H3)

southernscouser

PhilCo wrote: »

The Profitcalc function is basically saying if type equals "Qualifier", "Arb" or "Casino Offer" then do returns minus backstake. If "SR Free Bet", "SNR Free Bet", "Cashback" or "Referral" then just put returns in.

If you put this in I3 it should do the same...

=IF(OR(

C3="Qualifier";C3="Arb";C3="casino offer");H3-D3;H3)

Thank you so much. :j :beer:

Just one more question though.

How do I get it to calculate all the way down the I column? :undecided At the moment it only does it on that line. :think:

donny-gal

Just copy the formula down the column, the I3 should change to I4,I5 etc automatically, but if not you have to change it manually.
DG