CHALLENGE - Formula to work out profit/loss even if negative numbers.

[rippedit123]

Hello everyone,

I'm currently trying to create a profits/loss spreadsheet for forex/spreadbetting which in my eyes a bit more complicated and challenging.

Here is an example scenario.....FTSE is at 1000, so I bet £1 per point on FTSE 100 to:-

1. go UP by 10 points, and the market goes UP in my direction by 10 points, then that is a WIN by 10 points
2. go DOWN by 10 points, and the market goes DOWN in my direction by 10 points, then that is a still a WIN by 10 points

however, if market goes in the opposite direction to either of my UP/DOWN bets then that is a LOSS.

You following me so far?


Okay now regarding the profits/loss spreadsheet:-

How can I create a profits/loss formula based on above scenario? It shouldn't matter if the number is negative or positive....so long as I have a cell text stating WIN or LOSE, the formula should be able to tell to calculate the positive/negative numbers, based on whether I've Won or Lost.

FYI, here is what my spreadsheet has:-

A1: Opening Price: 1000
B1: Closing Price: 1010
C1: Points Moved: 10
D1: Amount Betted: 1
E1: Bet Direction: UP
F1: Market Direction: UP
G1: Win/Loss?: WIN ****This is set to automatically change text based on whether I've won/lost****
H1: Profit/Loss: If G1 is WIN, then C1*D1=£10 profit, else If G1 is LOSS, then work this out as a loss ****Answer should be based on G1 (Win or Loss????).

A1: Opening Price: 1000
B1: Closing Price: 0990
C1: Points Moved: -10
D1: Amount Betted: 1
E1: Bet Direction: DOWN
F1: Market Direction: DOWN
G1: Win/Loss?: WIN ****This is set to automatically change text based on whether I've won/lost****
H1: Profit/Loss: If G1 is WIN, then C1*D1=£10 profit (EVEN IF C1 is negative!!!!), else If G1 is LOSS, then work this out as a loss ****Answer should be based on G1 (Win or Loss????).



A1: Opening Price: 1000
B1: Closing Price: 1010
C1: Points Moved: 10
D1: Amount Betted: 1
E1: Bet Direction: DOWN
F1: Market Direction: UP
G1: Win/Loss?: LOSS ****This is set to automatically change text based on whether I've won/los****
H1: Profit/Loss: If G1 is LOSS, then C1*D1=£10 LOSS so excel should SUBTRACT this from the profits, else If G1 is LOSS, then work this out as a loss ****Answer should be based on G1 (Win or Loss????).


I hope I have been clear, but please let me know if your not sure what I mean So can you guys please help me?

Thank you

[Richard Buttrey]

Please upload the workbook and manually add some results that you expect to see for all unique combinations. Tell us which are the results.

[rippedit123]

Hi Richard,

Thank you for your reply.

I've attached the spreadsheet with random mix of numbers.

The main issue is that the profit/loss is miscalculating and needs to base it's profit/loss calculations dependant on the condition of WIN/LOSS

EDIT: Sorry Richard, Forgot to tell you that I've added the requested combinations and the main area of problems is in the W/L in £££ and the Total...it's all highlighted in the spreadsheet for you.

[newdoverman]

The formula should in column J should be:

Please Login or Register  to view this content.

[rippedit123]

Newdoverman, thank you very much for your formula. I am genuinely very happy as your formula is fine, but I stumbled on a new issue now.

Assuming I've used your formula and applied it to the spreadsheet with the same numbers when I uploaded it to this forum, my profit/loss total is showing as minus £50. This is correct and your formula is fine. However, the total shows incorrectly if I change G3 from BUY to SELL.

Instead of adding £30 and showing it correctly as minus £20, my total shows as +£10.

Below the Total is Correct - i.e. without Changing any bets to match market:-

BET MARKET W/L W/L in £££
BUY BUY WIN £10.00
BUY SELL LOSS -£30.00
SELL BUY LOSS -£20.00
BUY BUY WIN £25.00
SELL SELL WIN £15.00
SELL BUY LOSS -£10.00
BUY SELL LOSS -£40.00

-£50.00


Below the Total is INCORRECT- i.e. After Changing bet to match market:-

BET MARKET W/L W/L in £££
BUY BUY WIN £10.00
SELL SELL LOSS £30.00
SELL BUY LOSS -£20.00
BUY BUY WIN £25.00
SELL SELL WIN £15.00
SELL BUY LOSS -£10.00
BUY SELL LOSS -£40.00

£10.00

[newdoverman]

The formula that I provided you is based on the results in column I. I don't understand the logic that you want applied with the BUY and SELL. It appears to me that you are really processing the -30 incorrectly in thinking that you should have a value of -20 when the BUY/SELL is changed. When you go from BUY to SELL there is really a difference of 60 and not just 30 (-30 to +30). Enter this somewhere on your spreadsheet and you should see what I mean. =30-(-30) the answer is 60.

[rippedit123]

In trading terms, if I've said that the market with go down (SELL) by 30 points and the market does go down (SELL) by 30 points, then that 30 x 1 (no. of stakes) = profit of £30 WIN for me. However if the market goes in the opposite direction (BUY), then I make a LOSS of 30 x 1 (no. of stakes) = minus £30

BUY+BUY = WIN = PROFIT
SELL+SELL = WIN = PROFIT
SELL+BUY = LOSS = LOSS
BUY + SELL = LOSS = LOSS



I want to add that your formula is perfect and does work even if I change the Bet BUY/SELL columns. I think it's messing up when it's totalling the whole lot at the bottom. If you add £30 to a total of -£50 it should say that I'm still at a loss of £20 and not at a profit of £10.

I hope I make some sense. Really sorry, buddy.....that's why I wrote on the thread title as a Challenge as I've tried to figure it out myself and couldn't and that's why I'm on here to get help from excel geniuses like yourself.

How can I fix this issue? Is it another column causing it or would I need to alter the way I've done the spreadsheet? Sorry....thats why I wrote the title as a challenge as I've tried to figure it out myself

[shg]

Maybe ...

Row\Col	G	H	I	J	K
1	BET	MARKET	W/L	W/L in £££
2	BUY	BUY	WIN	£10.00	J2: =ABS(E2*F2) * IF(G2=H2, 1, -1)
3	BUY	SELL	LOSS	-£30.00
4	SELL	BUY	LOSS	-£20.00
5	BUY	BUY	WIN	£25.00
6	SELL	SELL	WIN	£15.00
7	SELL	BUY	LOSS	-£10.00
8	BUY	SELL	LOSS	-£40.00

[rippedit123]

Thanks shg...that formula too works, but I still have a problem at the bottom as the amount is calculating incorrectly (unless I'm missing something????). Could you please take a look at that cell? Currently all it's doing is adding all the numbers up using formula =SUM(J2:J31), but I think it needs to be a bit more complex that that for example it should deduct any cell amounts which are associated with LOSS cell.

[newdoverman]

I don't think that there is a problem other than the way the logic looks. If you lose 30 and that 30 is changed to a gain the result is 60 not 30. That is why you don't get -20 but get +10. The only way that you would get -20 is if you neither lost nor gained in other words no bet.

If it was possible to have no bet then you would have the -20 but you either lose or win so there isn't any no bet situation.

As far as I can see the math is correct from start to finish. It is just that the result "looks" wrong. It is a brain twister that makes the correct answer look wrong.

[rippedit123]

Your absolutely correct....In which case my question is how, can we correct this result? Is there no way?

In simple maths terms, -50 + 30 should be -20...no question about it. But in excel the result not just looks, but is wrong...

There must be a formula to resolve this issue, otherwise me and you have come up with a major conundrum in excel.

[newdoverman]

When you add 30 to the -50 you have eliminated the loss and the result is -20. That 30 however is not gains from a WIN, it is just the elimination of the LOSS as if you had never bet. However, you are now in a WIN situation and have to add 30 to the -20 the position that you would have been in if you had never bet and the result is +10.

There is no way that I can logically make -20 the correct answer under the circumstances as I understand your explanation.

[rippedit123]

I understand mate and want to thank you for your input.

I'm thinking what if I changed the formula not to base it on the WIN/LOSS situation but on the BET/MARKET scenario...i.e. the formula calculates the amount dependant on the criteria of BET/MARKET's SELL/BUY...would that possibly work?

I've attached another excel spreadsheet to this post...could I alter or use the formulas from that?

[newdoverman]

In some ways this looks like a more simple way of doing things. I have never bet so don't know anything about the terms used.

I have simplified the formulae that you have in column B. The INDIRECT function isn't needed at all so I removed it from your formulae. As you can see, the formulae have become much shorter and are easier to read.

I think that you have the situation in hand and can handle it now.

[rippedit123]

Thanks mate.

I hope you don't mind me pointing out, but actually there is some issue with your altered sheet...at least according to the compared screenshot below, your SS is not counting the total level staked, the return, Total no. of losers, ROI:-

spreadbetcompare.jpg

[newdoverman]

The reason that the calculation isn't correct is because the macro that you use is inserting rows and upsetting the ranges set in the formula. That being the case, I changed the formula in B13 and B14 with an alternate in C14.

B13:

Please Login or Register  to view this content.

B14:

Please Login or Register  to view this content.

C14:

Please Login or Register  to view this content.

I don't usually enable macros in downloaded workbooks but took a chance to see what was going on.