# Tearing my hair out all night with this spreadsheet...HELP Me Pls....

#### rippedit123

##### New Member
Hi everyone,

I've started off a little project since Friday to create a spread-bet spreadsheet and mostly it seems to be almost there, but there are a few major formulas that I can't get my head around and would much appreciate it if someone could please help me with this.

Here is what the spreadsheet currently looks like (Ignore the colours which have been set using conditional formatting:-

As you can see from above, there is a problem with:-

1. Cells under I. If the market has gone in my favour i.e. I chose SELL and market goes SELL and VICE versa, then that should be a WIN. But my profit for Asset Germ 30 is showing as a loss of -£20.20 in cell I6....this should show as £20.20 automatically. Also as you can see, cell E3 shows -130 points, and because the market went opposite direction, this is showing correctly as a loss in cell J, however cell I2 is showing blank, when it should be showing a loss of £13.

2. Cells under J. Currently it shows a loss even if I've got a break-even. FYI, I want to show the following text dependant on choices between cells under G, H & E: "WIN", "LOSS","BREAK-EVEN" or "N/A". So for example:-
a)If cell G & H have equal text then this is a "WIN",
b)otherwise it's a "sell"...
c)If cells G & H are blank then it should say "N/A"
d)However, if Cell E (Pips Moved) is Zero, then show "Break-Even".

I hope I have explained myself as best as possible, but to help you further, please find the formulas I used (Apologies as they may/may not be correct):-

Thank you again, please let me know if you need further info.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### rippedit123

##### New Member
UPDATE: Please ignore question no.2 as I have managed to fix this with the following formula:-

=IF(AND(ISBLANK(G2)),"NO BET",IF(D2=C2,"N/A",IF(H2<>G2,"LOSS",IF(H2=G2,"WIN"))))

I still need help with question no.1 and would much appreciate it if anyone can guide me to the correct formula

#### CsJHUN

##### Active Member
For question 1 could you please share the formula on I (or I6) or upload the file on googledrive?

#### rippedit123

##### New Member
Since the time I posted this thread, I have made some changes. Currently the formula for I6 is: =IF(J6="WIN",ABS(F6*E6),IF(J6="LOSS",(F6*E6)))

#### CsJHUN

##### Active Member
Since the time I posted this thread, I have made some changes. Currently the formula for I6 is: =IF(J6="WIN",ABS(F6*E6),IF(J6="LOSS",(F6*E6)))
you dont need other "IF"
I6 is: =IF(J6="WIN",ABS(F6*E6),(F6*E6))

Replies
5
Views
401
Replies
9
Views
350
Replies
0
Views
312
Replies
2
Views
639
Replies
0
Views
147

1,195,905
Messages
6,012,222
Members
441,683
Latest member
XLGeezer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back