syntax error

Dec 10, 2012 at 9:41 PM

Hi.

 

Please help a newbie. I'm trying to add a formula to an OLAP derived pivot table in Excel and create a formula that, depending on a dimension called [Spot Length], will multiply a calculation based on measures by a certain percentage. I'm trying to nest IIF functions without success. When I try the following:

 

IIf([Measures].[Purchased Spot Count] = 0,null,IIf ([Spot Length] = 60,[Measures].[Net Cost]/[Measures].[Purchased Spot Count]),IIf ([Spot Length] = 30,[Measures].[Net Cost]/[Measures].[Purchased Spot Count]*.55),IIf ([Spot Length] = 15,[Measures].[Net Cost]/[Measures].[Purchased Spot Count]*.30),IIf ([Spot Length] = 10,[Measures].[Net Cost]/[Measures].[Purchased Spot Count]*.2),IIf ([Spot Length] = 05,[Measures].[Net Cost]/[Measures].[Purchased Spot Count]*.1))

I'm getting an error message back which states that 'Too few arguments were passed to the IIF function. The minimum argument count for the function is three".

 

Am I completely barking up the wrong tree in trying to do this in this way? I'm an Excel developer who would solve this by nesting if statements and have only discovered that you can't insert formulas into OLAP based pivot tables recently.

 

Thanks in advance for any help.

Coordinator
Dec 20, 2012 at 3:05 PM

IIf requires three arguments:

IIf(condition,if_true,if_false)

So in your calculation above, just put a third parameter of null in all your IIF functions. For example:

IIf ([Spot Length] = 05,[Measures].[Net Cost]/[Measures].[Purchased Spot Count]*.1, null)

Sorry for the delay in replying.

Marked as answer by furmangg on 6/9/2014 at 12:49 AM