I have the following in a calculated field. I need to add many more IF statements but when I start adding more to the code I begin to get Syntax Error. I need a workaround to make this work.

=IF(Currency="USD",[Total Cost (Local Currency)],SUM(IF(Currency="ARS",[Total Cost (Local Currency)]*0.212271),SUM(IF(Currency="SGD",[Total Cost (Local Currency)]*0.819601)),SUM(IF(Currency="CNY",[Total Cost (Local Currency)]*0.15957))))


Create a new list with the currency codes and conversion values, and use a lookup column to pull the right values. This will scale far better, anyway, since conversion rates are far from constant.

I'd also suggest that you implement the lookup column as a Site Column so you can use it later in additional locations in your Site Collection.

Just because we use flat lists in SharePoint, it doesn't mean that we can't attempt to follow good relational database approaches.

ADDENDUM: I'm so used to using Data View Web Parts (DVWPs) that I didn't think enough about this answer. It turns out that the value in the second list won't be available for use in a calculated column. You can use a DVWP to do the calculations, though. You can either pull the exchange rate value across with the lookup column in 2010 or use an AggregateDataSource in 2007 to look it up in your XSL in the DVWP.

| improve this answer | |

You are most likely running into issues with your column name. Having special characters (the parenthesis) in your column name usually messes with the calculation engine. Try remove those.

| improve this answer | |
  • Thanks for the suggestion, but that doesn't seem to be it. I am getting The formula contains a syntax error is not supported when I enter =IF(Currency="USD",[Total Cost - Local Currency], SUM(IF(Currency="ARS",[Total Cost - Local Currency]*0.212271), SUM(IF(Currency="SGD",[Total Cost - Local Currency]*0.819601)), SUM(IF(Currency="CNY",[Total Cost - Local Currency]*0.15957))), SUM(IF(Currency="GBP",[Total Cost - Local Currency]*1.610581)))), SUM(IF(Currency="CAD",[Total Cost - Local Currency]*1.015700))))), SUM(IF(Currency="PHP",[Total Cost - Local Currency]*0.024210)))))) – That-Guy Oct 16 '12 at 17:51
  • Simplify it for testing. For instance, simply try =[Total Cost - Local Currency] * 0.212271. Then try adding a layer of complexity, such as =IF([Currency]="USD", "It's USD!", "It's not USD1" – John Chapman Oct 16 '12 at 17:58
  • I would also remove the SUM functions. Unless I am missing something, it doesn't look like you are adding any values together. – John Chapman Oct 16 '12 at 18:07
  • 1
    I can get it to work if I use 7 or less IF statements. – That-Guy Oct 16 '12 at 18:57
  • 1
    Thanks John. I have done some research and in a calculated field you can only have 7 nested IF statements. Character limit is 1024, which I am well below. – That-Guy Oct 16 '12 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.