If function formula - excel '07

dunmore83

New member
Joined
Jul 11, 2012
Messages
1
Reaction score
0
Points
0
This IF function is not working. I think I am close so if anyone can solve it I would be very appreciative!

=IF(D6<>"jpy",IF(G6="buy",(H6-I6)*100,(I6-H6)*100),IF(G6="buy",(H6-I6)*10000,(I6-H6)*10000))

Cell G6 is a text cell which contains either "BUY" or "SELL". If BUY then (H6-I6) / If SELL then (I6-H6)

The formula is then based on cell D6 which is also a text cell. If cell D6 contains the following text entries then the formula needs mulitply by 1000 i.e IF(G6="buy",(H6-I6)*10000,(I6-H6)*10000)

EUR / USD
GBP / USD
AUD / USD
NZD / USD
USD / CHF
USD / CAD
GBP / CHF
GBP / CAD
GBP / NZD
GBP / AUD
EUR / GBP
EUR / CHF
EUR / CAD
EUR / NZD
AUD / CHF
AUD / CAD
AUD / NZD
CAD / CHF

However if the following text entries are found in cell D6 then the formula needs to multiply by 100 i.e. IF(G6="buy",(H6-I6)*100,(I6-H6)*100)

USD / JPY
GBP / JPY
EUR / JPY
CHF / JPY
CAD / JPY
AUD / JPY
NZD / JPY
XAU / USD
XAG / USD

Alternatively the formula could just be based on the discrepancy in the text entries in the cells i.e. if JPY or XAU or XAG is found in cell D6 then *100 / if these text entries are not found *1000.

Thanks!!
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
try this .... your instructions were a little confusing, took a chance i saw what you want to do.

put this in column e and copy down starting in E6 , this trims down your currency pairs in column D to the 3 far right abbreviations. EUR/USD gets trimmed down to just USD. this is so you can look for just "jpy"
in your formula
Code:
=RIGHT(D6, LEN( D6 ) -6 )

put this in like K6 and copy down


Code:
=IF(E6<>"JPY",IF(G6="BUY",(H6-I6)*100,(I6-H6)*100),IF(G6="BUY",(H6-I6)*1000,(I6-H6)*1000))

you can hide column E once you see that it works like you need it too. or not

You must be doing some Forex trading.
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
Here is a file to view if you prefer ...
 

Attachments

  • CURRENCY.xlsx
    12.2 KB · Views: 24
Top