Results 1 to 2 of 2

Thread: Data validation with different formulas

  1. #1

    Data validation with different formulas



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

    Hello! I am trading currencies and have developed a log sheet. What I have is one cell with about 12 different currencies listed in the data validation with that cell. I have a formula that gives me the p/l after the entry and exit is entered. That formula is as follows:
    =IF(B14="Long",B21-B16,B16-B21)*10000
    So with that formula if a pairs price is .91572 using this formula the price comes out right but if the pair has a value of 138.000 I have to delete 2 zeros in the formula to make it right so it would be this: =IF(B14="Long",B21-B16,B16-B21)*100
    So what I want is this:


    Pairs: AUD/USD, USD/CHF, NZD/USD = =IF(B14="Long",B21-B16,B16-B21)*10000
    Pairs: EUR/JPY, USD/JPY, GBP/JPY = =IF(B14="Long",B21-B16,B16-B21)*100


    If I pick a pair from the first list I want the formula to come out as it should and if I pick from the other list the formula changes to what it should be with all the pairs listed in one cell using data validation. Hopefully I didn't confuse anyone! Your help is appreciated. Thanks! and God bless!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Perhaps?

    =IF(B14="Long",B21-B16,B16-B21)*IF(OR(A1={"AUD/USD", "USD/CHF", "NZD/USD"}),10000,100)

    where A1 contains your dropdown selection of "pairs".


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
  •