Results 1 to 3 of 3

Thread: If function formula - excel '07

  1. #1

    Exclamation If function formula - excel '07



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

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

  3. #3
    Here is a file to view if you prefer ...
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •