IF OR Formula with Multiple Criteria

[williamscheidt]

Hi all,

Can anyone help with a correction to a formula I'm working on? I think I may be pretty close, but just not quite there. Here's what I've got:

Column B is a text value, "Long" or "Short" (the name of the column is Entry Signal)
Column E is a numerical value (the name of the column is Entry Value)
Column H is a numerical value (the name of the column is Exit Value)

I'm looking for a formula such that when the value of column B is "short", the cell returns a value of E minus H; and when the value of column B is "long", the cell returns a value of H minus E.

What I've got at the moment is: =IF(OR(B5="Short",B5="Long"),E5-H5,H5-E5)
The problem I'm encountering with this formula is that it is treating each row as if the value of B is "short." In other words, it always returns the value of E minus H.

Any thoughts?

Thanks in advance!
Bill

 

[Cindy Ellis]

The "OR" is what's messing up the formula.
Instead try =IF(B4="Short", E5-H5, If( B5="Long",H5-E5,""))
Hope that helps,

 

[vogel997]

Try this

=IF(B1="Short",E1-H1,H1-E1)

 

[Cindy Ellis]

The "OR" is what's messing up the formula.
Instead try =IF(B5="Short", E5-H5, If( B5="Long",H5-E5,""))
Hope that helps,

EDIT of above post...sorry for any confusion.
Also, Vogel997's formula will work fine if column B can ONLY have "Long" or "Short".

 

[williamscheidt]

Hi Cindy and vogel977,

Thanks very much for your answers! Worked perfectly!

Cheers!
Bill