Brain fade...

de-luded

New Member
Joined
Nov 25, 2011
Messages
3
Hi. I have a spreadsheet with 4 columns related to my trading. The columns are A: Fill Price, B: Current Price, C: Long/Short (I have used 1 or -1 as data) and D: Change.

I want to calculate D being a positive or negative figure.

This will need to work both for the situations where I am going Long, so hopefully B will be greater than A producing a positive figure, and also where I am going Short, so that hopefully B will be less than A in order to produce a positive figure.

I am assuming that C will be required as a "switch", but I can't resolve how to do this.

Thanks
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,944
Office Version
  1. 365
Platform
  1. Windows
Welcome to the board!

Could you post a sample table of (fictional) data showing some figures and expected results, this will make it much easier for us to understand what you're trying to do.

If you create a table (with borders) in excel, you can copy and paste it in your reply.

Remember to add borders in excel before you copy.
 

de-luded

New Member
Joined
Nov 25, 2011
Messages
3
Okay P45CAL

If only it were that simple... this works fine with Long positions, but not Short (-1) calcs..


Fill Price Current L/S Change Your answer
1.02740 1.04000 1 0.01260 0.01260
1.04641 1.02000 1 -0.02641 -0.02641
78.3400 79.70000 1 1.36000 1.36000
23.9000 22.76000 -1 1.40000 46.66000
23.4000 26.00000 -1 -2.60000 49.40000

Thanks,

Tony
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,944
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try

Sheet6

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 63px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Fill</TD><TD>Current</TD><TD>L/S</TD><TD>Change</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1.0274</TD><TD style="TEXT-ALIGN: right">1.04</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0.0126</TD><TD style="TEXT-ALIGN: right">0.0126</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1.04641</TD><TD style="TEXT-ALIGN: right">1.02</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">-0.02641</TD><TD style="TEXT-ALIGN: right">-0.02641</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">78.34</TD><TD style="TEXT-ALIGN: right">79.7</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1.36</TD><TD style="TEXT-ALIGN: right">1.36</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">23.9</TD><TD style="TEXT-ALIGN: right">22.76</TD><TD style="TEXT-ALIGN: right">-1</TD><TD style="TEXT-ALIGN: right">1.4</TD><TD style="TEXT-ALIGN: right">1.14</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">23.4</TD><TD style="TEXT-ALIGN: right">26</TD><TD style="TEXT-ALIGN: right">-1</TD><TD style="TEXT-ALIGN: right">-2.6</TD><TD style="TEXT-ALIGN: right">-2.6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">23.4</TD><TD style="TEXT-ALIGN: right">26</TD><TD style="TEXT-ALIGN: right">-1</TD><TD style="TEXT-ALIGN: right">-2.6</TD><TD style="TEXT-ALIGN: right">-2.6</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=C2*IF(B2<A2,-(A2-B2),B2-A2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

Not sure if row 5 result is wrong or if there was a typo in the sample.
 

de-luded

New Member
Joined
Nov 25, 2011
Messages
3
Thanks jasonb75

Thanks p45cal ~ that will do nicely.


Help gratefully received, cheers guys...


Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,480
Members
414,143
Latest member
lonnie451

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top