Data validation with different formulas

ChaosTrader63

New member
Joined
Nov 25, 2013
Messages
10
Reaction score
0
Points
0
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!
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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".
 
Top