if statement
 

is there a limit on how many possibilities you can have in
an if statement.

i'm trying to write a statement like this.

=IF(P2="AUDCAD","AS",IF(P2="EURJPY","EJ",IF
(P2="EURGBP","GB",IF(P2="EURAUD","RA",IF
(P2="EURCHF","RZ",IF(P2="GBPJPY","SY",IF
(P2="AUDUSD","AD",IF(P2="GBPUSD","BP","")))))))

and this works fine, but when i try to add more if's i get
an this formula contains and error messege. for example if
i add one more case at the end for "CADUSD", i get an
error, but it works fine up to "GBPUSD"

=IF(P2="AUDCAD","AS",IF(P2="EURJPY","EJ",IF
(P2="EURGBP","GB",IF(P2="EURAUD","RA",IF
(P2="EURCHF","RZ",IF(P2="GBPJPY","SY",IF
(P2="AUDUSD","AD",IF(P2="GBPUSD","BP",IF
(P2="CADUSD","C1","")))))))))

if statement
 

Hi Bryan
Excel has a limit of 7 nested functions. For this kind of formula I
would suggest you use VLOOKUP. Try the following:
- create a separate sheet with a lookup table (lets call this sheet
'lookup) and the following layout:
A B
1 AUCAD AS
2 EURJPY EJ
....

noy on your toher sheet use the following formula
=IF(ISNA(VLOOKUP(P2,'lookup'!$A$1:$B$20,2,0),"",VL OOKUP(P2,'lookup'!$A$
1:$B$20,2,0))



--
Regards
Frank Kabel
Frankfurt, Germany

Bryan wrote:
is there a limit on how many possibilities you can have in
an if statement.

i'm trying to write a statement like this.

=IF(P2="AUDCAD","AS",IF(P2="EURJPY","EJ",IF
(P2="EURGBP","GB",IF(P2="EURAUD","RA",IF
(P2="EURCHF","RZ",IF(P2="GBPJPY","SY",IF
(P2="AUDUSD","AD",IF(P2="GBPUSD","BP","")))))))

and this works fine, but when i try to add more if's i get
an this formula contains and error messege. for example if
i add one more case at the end for "CADUSD", i get an
error, but it works fine up to "GBPUSD"

=IF(P2="AUDCAD","AS",IF(P2="EURJPY","EJ",IF
(P2="EURGBP","GB",IF(P2="EURAUD","RA",IF
(P2="EURCHF","RZ",IF(P2="GBPJPY","SY",IF
(P2="AUDUSD","AD",IF(P2="GBPUSD","BP",IF
(P2="CADUSD","C1","")))))))))

if statement
 

Up to 7 IFs can be nested.

Try something like: =VLOOKUP(P2,CurrencyExchange,2,0)

where CurrencyExchange is a named range referring to a 2 column table
in say, another sheet with col1 containing: AUDCAD, EURJPY etc
and col2 containing the correspondings: AS, EJ etc

Besides overcoming the limitations of nested IFs,
a VLOOKUP is much easier to maintain

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <atyahoo<dotcom
----
"Bryan" wrote in message
...
is there a limit on how many possibilities you can have in
an if statement.

i'm trying to write a statement like this.

=IF(P2="AUDCAD","AS",IF(P2="EURJPY","EJ",IF
(P2="EURGBP","GB",IF(P2="EURAUD","RA",IF
(P2="EURCHF","RZ",IF(P2="GBPJPY","SY",IF
(P2="AUDUSD","AD",IF(P2="GBPUSD","BP","")))))))

and this works fine, but when i try to add more if's i get
an this formula contains and error messege. for example if
i add one more case at the end for "CADUSD", i get an
error, but it works fine up to "GBPUSD"

=IF(P2="AUDCAD","AS",IF(P2="EURJPY","EJ",IF
(P2="EURGBP","GB",IF(P2="EURAUD","RA",IF
(P2="EURCHF","RZ",IF(P2="GBPJPY","SY",IF
(P2="AUDUSD","AD",IF(P2="GBPUSD","BP",IF
(P2="CADUSD","C1","")))))))))

if statement
 

From Excel Help on If Statements:

Remarks

a.. Up to seven IF functions can be nested as value_if_true and
value_if_false arguments to construct more elaborate tests.

if statement
 

Functions may only be nested 7 deep.

Instead, create a table (perhaps on a different sheet, say SHeet2,
Columns A and B) with your equivalents:


A B
1 AUDCAD AS
2 EURJPY EJ
...

etc.

Then in your original sheet, use:

=VLOOKUP(P2, Sheet2!A:B, 2, 0)

If it's possible that the value in P2 will not exist in the table, use

=IF(ISNA(MATCH(P2, Sheet2!A:A, 0)),"",VLOOKUP(P2, Sheet2!A:B, 2, 0)

to display a null string instead of the #NA! that VLOOKUP returns.


In article ,
"Bryan" wrote:

is there a limit on how many possibilities you can have in
an if statement.

i'm trying to write a statement like this.

=IF(P2="AUDCAD","AS",IF(P2="EURJPY","EJ",IF
(P2="EURGBP","GB",IF(P2="EURAUD","RA",IF
(P2="EURCHF","RZ",IF(P2="GBPJPY","SY",IF
(P2="AUDUSD","AD",IF(P2="GBPUSD","BP","")))))))

and this works fine, but when i try to add more if's i get
an this formula contains and error messege. for example if
i add one more case at the end for "CADUSD", i get an
error, but it works fine up to "GBPUSD"

=IF(P2="AUDCAD","AS",IF(P2="EURJPY","EJ",IF
(P2="EURGBP","GB",IF(P2="EURAUD","RA",IF
(P2="EURCHF","RZ",IF(P2="GBPJPY","SY",IF
(P2="AUDUSD","AD",IF(P2="GBPUSD","BP",IF
(P2="CADUSD","C1","")))))))))

if statement
 

Awesome..Tks


-----Original Message-----
Up to 7 IFs can be nested.

Try something like: =VLOOKUP(P2,CurrencyExchange,2,0)

where CurrencyExchange is a named range referring to a 2
column table
in say, another sheet with col1 containing: AUDCAD,
EURJPY etc
and col2 containing the correspondings: AS, EJ etc

Besides overcoming the limitations of nested IFs,
a VLOOKUP is much easier to maintain

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <atyahoo<dotcom
----
"Bryan" wrote in
message
...
is there a limit on how many possibilities you can
have in
an if statement.

i'm trying to write a statement like this.

=IF(P2="AUDCAD","AS",IF(P2="EURJPY","EJ",IF
(P2="EURGBP","GB",IF(P2="EURAUD","RA",IF
(P2="EURCHF","RZ",IF(P2="GBPJPY","SY",IF
(P2="AUDUSD","AD",IF(P2="GBPUSD","BP","")))))))

and this works fine, but when i try to add more if's i
get
an this formula contains and error messege. for
example if
i add one more case at the end for "CADUSD", i get an
error, but it works fine up to "GBPUSD"

=IF(P2="AUDCAD","AS",IF(P2="EURJPY","EJ",IF
(P2="EURGBP","GB",IF(P2="EURAUD","RA",IF
(P2="EURCHF","RZ",IF(P2="GBPJPY","SY",IF
(P2="AUDUSD","AD",IF(P2="GBPUSD","BP",IF
(P2="CADUSD","C1","")))))))))


.

if statement
 

You're welcome !
Thanks for the feedback.
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <atyahoo<dotcom
---
wrote in message
...
Awesome..Tks