Excel formula help, if formula with calculation

[jackiekebab]

Hi All,

I'm working on a spreadsheet and I'm trying to work out a formula, but I'm having no success and I am hoping a guru on here wouldn't mind giving me a massive help and advising on a solution...

I think it may be an IF formula, but I've had a play with them and can't get it to do what I want...

Basically it's like this..

I'm collating some analysis for sports betting as I believe I can find value in odds and in which case with a sensible staking plan of say 3% of the total bank per bet I believe that over time I can accrue a decent ROI, however before staking any actual money I want to carry out a years worth of data analysis of making selections and see that I can first find value in odds and pick winning selections.

The key items in the sheet are as follows...

Column F has a multiplier value which changes per row (decimal odds).
Column J has one of two values, (won and lost).
Column L has a value which is always 3% of column N (the bank)
Column M however needs logic applying...

So lets say I start off with a balance of £10k in the bank and the first two bets one wins and one loses.

Then it would appear as follows..

A B C D E F G H I J K L M N
Date League Match Selection Odds Formula odds (to be hidden) Tips Confidence Value Result /////// Stake Profit/Loss Bank

N/A N/A N/A N/A 1.65 0.65 N/A N/A N/A Won £300 =L2*F2 (£195) £10,195

N/A N/A N/A N/A 3.30 2.30 N/A N/A N/A Lost =N2*3% (£305.85) -£305.85 =N2+M3 (£9,889.15)


What I need in column M is a formula to basically look at column J and see if the selection won or lost. If it won it would multiply the stake (column L) by the formula odds (column F) and if it lost it would simply deduct the stake (column L).

The column N formula could then be dragged down as could the column L formula with the only logic coming in at column M dependent upon the output of column J.

Anyone got any ideas, it's probably really simple and I'm a doughnut, but I've got no shitting clue...

Thanks in advance

[jackiekebab]

Shite, that hasn't formatted anything like it appeared while creating the post, apologies...

[FDibbins]

Hi and welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Also, it is often better to upload a (clean) sample workbook, showing what you have and what you want.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[jackiekebab]

cheers mate, just done the title will upload sheet now

[jackiekebab]

Think this should upload...

[jackiekebab]

Yeah so the formula would go in column M.

I have row 2 as a win and row 3 as a lose to show what I'm after.

I need column M to state if column J is win then L2*F2 and if column J is lose then show column L as a minus.

Hope that makes sense, sorry if not.