I'm trying to link this together - if E10>F10,then E10-F10 if true, and F10-E10 if false; but if Cell C10 has the word "Sell" and E10<F10, then F10-E10 and calculate the number positive or negative.

Try this: =IF(C10="sell",IF(E10<F10,F10-E10,0),IF(E10>F10,E10-F10,F10-E10))

MIKE

Mike - Thanks for formula - I plugged it in, but it returns a positive number when is should be negative and a positive number when it should be positive. Here is the data when the result is positive and should be so -

E5 = 0.8691

F5 = 0.8651

P5 = Sell

*10000 to produce a 2 digit value in G5 the cell I have the formula inside.Formula =IF(P5="Sell",IF(E5>F5,E5-F5,0),IF(E5<F5,F5-E5,E5-F5)*10000)

Here is the data when the result should be negative, and it returns a positive number

E6 = 1.6024

F6 = 1.61

P6 = Sell

*100 to produce a 2 digit value in G6 the cell I have the formula inside.Formula =IF(P6="Sell",IF(E6>F6,E6-F6,0),IF(E6<F6,F6-E6,E6-F6)*100)

What am I missing?

Let's go back to your original post and see if we can break it down to make it easier to understand.

if E10>F10,then E10-F10 if true, and F10-E10 if falseWhich can be written as follows:

=IF(E10>F10,E10-F10,F10-E10)

if Cell C10 has the word "Sell" and E10<F10, then F10-E10Which can be written as follows:

=IF(AND(C10="Sell",E10<F10),F10-E10)

The confusing party of trying to "link" these together is that your first set of criteria infers that if E10<F10 the result should be F10-E10, therefore it doesn't matter if C10=Sell or not.

What is the formula supposed to return if C10= Sell and E10>F10? That doesn't seem to be covered anywhere.

I guess what I'm trying to say here is that I'm not sure you've stated your criteria in a manner that allows us the write a formula that meets your needs.

I'm sorry for the confusion. I am tracking the gain/loss on Buying and Short Selling Currency Pairs.

When a pair is 'bought' (Buy), the math is pretty simple, Exit Price - Entry Price = P/L. But with a Short Sell (listed on spread sheet as "Sell"), it needs to reflect Entry Price - Exit Price = P/L.

Column E holds the Entry Price, Column F holds the Exit Price, and Column P holds the word "Buy" or "Sell" depending on the type.

The calculation for P/L is in Column G. I add either *100 (JPY base pair) or *10000 (non-JPY base pair) to have the result display in a whole number.

I thought I would need to include whether the Entry Price was > or < the Exit Price, but maybe that isn't necessary.

The 4 possible scenarios I need to account for in a single repeatable formula are:

"Buy" (win) Entry<Exit.....Exit-Entry= Result positive

"Buy" (loss) Entry>Exit.....Exit-Entry= Result negative

"Sell" (win) Entry>Exit.....Entry-Exit= Result positive

"Sell" (loss) Entry<Exit.....Entry-Exit= Result negativeDo you see a pattern that simplifies the formula and will work?

Thanks so much for your time and patience -

Dana

I put this in G2 and dragged it down to G5 to get these results. They appear to match the criteria in your most recent post. The assumption is that Column P will always contain either Buy or Sell. =IF(P2="Buy",F2-E2,E2-F2)

E F G P 1 Entry Exit Entry<Exit.....Exit-Entry: 2 4 5 1 Buy Entry>Exit.....Exit-Entry: 3 5 4 -1 Buy Entry>Exit.....Entry-Exit: 4 5 4 1 Sell Entry<Exit.....Entry-Exit: 5 4 5 -1 Sell

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History