Calculating results in Excel

I want to calculate some results in Excel. I have a column with daily changes of closing prices and a column with a signal value (Long, Short or Neutral). If the signal still is the same then I want to add (in case of Long) the daily change to the previous value or subtract (in case of a Short) the daily change to the previous value.
My data looks like:

A B C
-23 Short +23
-45 Short +68
+5 Long +73
+7 Long +80
+132 Long +212
+34 Long +256
-4 Long +252
+54 Long +306
-34 Neutral +306

Does someone know how I can calculate column C in Excel?

Thanks.

Hans

 

I want to calculate some results in Excel. I have a column with daily changes of closing prices and a column with a signal value (Long, Short or Neutral). If the signal still is the same then I want to add (in case of Long) the daily change to the previous value or subtract (in case of a Short) the daily change to the previous value.
My data looks like:

A B C
-23 Short +23
-45 Short +68
+5 Long +73
+7 Long +80
+132 Long +212
+34 Long +256
-4 Long +252
+54 Long +306
-34 Neutral +306


d2=if(b1=b2,abs(a1)+d1,d1)

note u might have to change "," to ";" depending on ur language settings

 

you can also use conditional formatting statements

it sounds like you're doing something similar to a basic black jack +/- counting system but your C column seems to be adding even when the B column says short.
maybe I'm missing something.

also, C6 should be 246 not 256 if you're adding A6(+34) to C5(+212).

 

You're right, it should be 246. Sorry for that mistake.

Basicly I had one short serie and one long serie. I want to add the total of the short serie to the total of the long serie. If the daily result is negative and I was short the result of the trade should be positive.

Hans

 

I don't want to make an addition when there is a neutral signal. I think this code don't make that comparison.

Hans

 

Another option might be:

A B C
-23 Short
-45 Short +68
+5 Long
+7 Long
+132 Long
+34 Long
-4 Long
+54 Long +228
-34 Neutral 0

Hans

 

Instead of using Long, Short, Neutral you could use 1, -1, 0, thus:

c2 = (a2 * b2) + c1

You can create constants in Excel (e.g., Long = 1, etc.), so you still use the names in Column B:

1. On the Insert menu, point to Name, and then click Define.

2. In the Names in workbook box, enter the name for the formula.

3. In the Refers to box, type = (equal sign), followed by the formula or the constant value.

 

I guess I'm not following you.
Are you saying it's not cumulative?

"If the daily result is negative and I was short the result of the trade should be positive."

So, if A is negative and B is "SHORT" then C becomes positive?
What would make C negative?

here's a formula for a cumulative approach, the plus/minus idea I mentioned. Although it sounds like it's not what you're looking for.

I left your column C as is and used column D.

Based off of your C1, I started with:
D1 =ABS(A1)
D2 =IF(B2="long",(D1+ABS(A2)),IF(B2="short",(D1-ABS(A2)),D1))

then drag the formula down the column.

 

is the daily result column A or C. I thought A was the daily change.

Are you saying you want to track the days you were right on market direction? long on up days and short on down days?

If this is the case, why is C equal to the entire daily change? Are you in your position from open to close?
If not, are you trying to track possible gains on the days you are right?

I may be entirely off base. Just trying to fugure out exactly what you're trying to do.

 

I post my original file. I want to calculate my results based on my position. My position is short (-1). neutral (0) or long (1). When I am short and the result is negative, my trading result should be positive. When I am long and the result is negative I want to subtract it from the total I have at the moment.

Hans