3

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

That-Guy
  • 31
  • 1
  • 2

2 Answers2

1

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.

Marc D Anderson
  • 9,686
  • 2
  • 36
  • 51
0

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.

John Chapman
  • 11,941
  • 6
  • 36
  • 62
  • 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
  • There you go. When dealing with the elusive calculated column, it is best to start simple and build from there. Looks like you hit some magical limit that the calculation engine arbitrarily has. – John Chapman Oct 16 '12 at 19:00
  • Since I have only been able to to get it to calculate using 7 IF statements I have made 2 columns. One with 7 IF statements and 1 with 3 IF statements. The columns are named Exchange Rate 1 and Exchange Rate 2. How do I have pull values from both columns into a single column? I have tried this. =IF([Exchange Rate 1]>0,[Exchange Rate 1],IF([Exchange Rate 2]<>[Exchange Rate 1],[Exchange Rate 2],0)) ---- This didn't work. LOL – That-Guy Oct 16 '12 at 19:04
  • Thinking back on the issue, I don't think the issue is 7 IF statements, I think it is a character limit on the formula. Try changing your two column names to "Cur" and "Cost". Then see if the shortened formula works. – John Chapman Oct 16 '12 at 19:08
  • 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
  • I wonder if an alternate approach might work. For instance, what if you tried the "REPLACE" function. You could have it replace the currency with the appropriate multiplier and then do the math. http://office.microsoft.com/en-us/windows-sharepoint-services-help/replace-function-HA001161055.aspx – John Chapman Oct 16 '12 at 19:19