# Thread: If function formula - excel '07

1. ## If function formula - excel '07

This IF function is not working. I think I am close so if anyone can solve it I would be very appreciative!

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
GBP / CHF
GBP / NZD
GBP / AUD
EUR / GBP
EUR / CHF
EUR / NZD
AUD / CHF
AUD / NZD

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
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!!

2. 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"
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.

3. Here is a file to view if you prefer ...