IF/THEN/ELSE statements can be added to any valid calculation for use with the CAL format. Enter IF/THEN/ELSE calculation formulas in the cell in the Related Formulas/Rows/Units column according to the following format:
IF <true/false statement> THEN <formula> ELSE <formula>
The ELSE <formula> portion of the statement is optional.
The statement that follows the IF statement can be any statement that can be evaluated as true or false. The statement that follows the IF statement can appear as follows:
IF A.200>0 (simple evaluation)
IF A.200>0 AND A.200<10,000 (complex statement)
IF A.200>10000 OR ((A.340/B.1200)*2 <1200) (complex statement containing multiple expressions)
The term Periods in an IF statement means the number of periods for the report. This term is commonly used for calculating a year-to-date average. For example, when running a report for period 7 YTD, B.150/Periods means to divide the value in row 150 of column B by 7.
THEN and ELSE formulas
The THEN and ELSE formulas can be any valid calculation from the simplest value assignments to complex formulas. Note the following formula:
IF A.200>0 THEN A=B.200
The statement specifies that "if the value in the cell in column A of row 200 is greater than zero, then place the value from the cell in column B of row 200 into the cell in column A of the current row."
In this statement, you can also use the at sign (@) in either TRUE/FALSE evaluations or in the formula to represent all columns. For example, the formula described in the preceding IF/THEN statement places a value in one column of the current row. Other examples are as follows:
IF A.200 >0 THEN B.200: When the value in cell A.200 is positive, Microsoft Office PerformancePoint 2007 Management Reporter places the value from cell B.200 into every column of the current row.
IF A.200 >0 THEN @200: When the value in cell A.200 is positive, Management Reporter places the value from each column in row 200 into the corresponding column in the current row.
IF @200 >0 THEN @200: If the value in row 200 of the current column is positive, Management Reporter places the value from row 200 into the same column in the current row.