Formula types
There are four types of formulas in the cell-based workbook:
- MPCData. A formula that reads values from the dataset. Values specified over this formula are written to the dataset. Values specified over the other three formula types are top down spread to MPCData formulas and Excel value cells. This formula type can be locked or constrained. For more information, see "MPCData Formulas."
- MPCPushData. A formula that
is created by specifying an Excel formula over an MPCData formula and then
selecting
Yes to a prompt to confirm you want the results
written to a corresponding cell in the dataset.
- The syntax of the
MPCPushData formula is the same as the MPCData formula except that it has an
additional parameter, called the value formula, after the dataset name. The
value formula is the Excel formula you specified over the MPCData formula.
=MPCPushData("Product Sales",(E16+E17),"[6]",$B$1,"[10]",$B$2,"[7]","Actual","[8]","Actual Rate","[11]","Catalog","[9]","Periodic","[12]","Reported","[5]","USD","[1*2]",E$3,"[4]",$A15) - This type of formula does not read values from the dataset but rather writes the value formula results to the dataset, anytime the cell is recalculated. A value specified over this formula is top down spread, based on the value formula, to MPCData formulas or Excel value cells. When the MPCData formulas and Excel value cells change, the MPCPushData formula recalculates, which saves the result of the value formula to the dataset. This formula type can be locked or constrained.
- The syntax of the
MPCPushData formula is the same as the MPCData formula except that it has an
additional parameter, called the value formula, after the dataset name. The
value formula is the Excel formula you specified over the MPCData formula.
- Excel Type 1. An Excel
database formula created for downfoot lines when a user inserts a block of data
with
Create Database Formulas selected. This type of
formula looks like any other Excel formula but is associated with the dataset.
A value specified over this formula is not written to the dataset but is top
down spread to MPCData formulas and Excel value cells. This formula type can be
locked or constrained.
- Insert Block automatically creates Excel Type 1 formulas so that a value specified for a non-leaf dimension member is spread to that dimension's leaf members in the data area when:
- All of the children of a member for that dimension are selected in the dataset
- Create Database Formulas is selected
- Excel Type 2. Any Excel formula created by the user that is not associated with the dataset. A value specified over this formula is not written to the dataset but is top down spread to MPCData formulas and Excel value cells. This formula type cannot be locked or constrained.