Excel - Linking if-and-or statements

April 23, 2009 at 19:12:47
Specs: Windows Vista
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.

See More: Excel - Linking if-and-or statements

Report •


#1
April 23, 2009 at 19:22:08
Try this:

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

MIKE

http://www.skeptic.com/


Report •

#2
April 24, 2009 at 19:35:52
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?


Report •

#3
April 24, 2009 at 21:28:51
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 false

Which can be written as follows:

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

if Cell C10 has the word "Sell" and E10<F10, then F10-E10

Which 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.


Report •

Related Solutions

#4
April 24, 2009 at 22:55:03
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 negative

Do you see a pattern that simplifies the formula and will work?

Thanks so much for your time and patience -
Dana


Report •

#5
April 25, 2009 at 18:24:33
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


Report •


Ask Question