Use SKIP in an IF formula to calculate some cells and leave others open for your team to publish or import, so one account can hold calculated values in some places and manual inputs in others.
Required permission: SKIP uses the same permissions as creating and editing any formula in Cube. Entering values into SKIP cells uses your existing publish and import permissions.
How SKIP works
SKIP goes in either the true or false part of an IF formula. Wherever the IF lands on SKIP, Cube doesn't calculate the cell.
IF("Department" = "Sales", "Bookings" * 0.05, SKIP)
Sales bonus is calculated. Every other department's bonus is left open, so budget owners can enter their own.
| Cell | What it holds | Can you publish or import into it? |
|---|---|---|
| Calculated (the IF didn't land on SKIP) | The formula result | No. Calculated cells can't be written to. |
| Skipped (the IF landed on SKIP) | Blank until someone enters a value | Yes, from a spreadsheet publish or an import. |
- A skipped cell is blank, not 0. Any formula that references it is blank there too, until a value is entered.
- Recalculation never overwrites what you entered. Values in skipped cells stay put when the formula recalculates.
- Mixed publishes still go through. If a publish includes both skipped and calculated cells, Cube writes the skipped cells and drops the calculated ones.
Set up a formula with SKIP
- Go to Formulas in your Workspace.
- Select New, or edit an existing formula.
-
Write an IF formula with
SKIPas the true or false value. You can also select Insert Functions and choose SKIP from the Logical category.
- Under Rollup Logic, select Calculate leaf values, then sum.
- Select Save Formula.
Note: Always use Calculate leaf values, then sum for SKIP formulas. With Sum leaf values, then calculate, a value you enter shows in its own cell but is missing from every total above it, with no error. Cube recalculates the formula at the total, lands on SKIP, and returns blank.
Enter values into skipped cells
- Build a range in your spreadsheet that includes the formula account and the skipped cells you want to fill.
- Enter your values.
- Publish the range.
You can also load values into skipped cells with an import. Totals above the cells update once the publish or import finishes.
Calculate in Actuals, plan by hand
The most common use of SKIP is a driver that's calculated from actual results but entered by hand in your plans.
IF("Scenario" = "Actuals", "Revenue" / "Customer Count", SKIP)
| Actuals | Forecast | |
|---|---|---|
| Revenue | 7,931,620 | |
| Customer Count | 1,000 | 1,100 |
| ARPU | 7,931.62 (calculated) | 8,000 (entered) |
A separate Planned Revenue formula, "ARPU Driver" * "Customer Count", then returns 8,800,000 in Forecast.
This also works the other way around. IF("Scenario" = "Actuals", SKIP, "Bookings" * 0.85) keeps Actuals open for imports and calculates every other scenario.
Tip: Use this pattern instead of a scenario-based formula for any account that other formulas depend on. Other formulas can't reference a scenario-based formula, but they can reference a formula that uses IF and SKIP.
Keep imported actuals and plan with a formula
An account that receives data from your accounting system can also have a formula, as long as the formula skips every scenario that receives imported data. Actuals keep the imported balance, and your plans calculate.
For example, on a 120000 - Accounts Receivable account that imports its Actuals balance:
IF("Scenario" != "Actuals", "Revenue" * 45 / 365, SKIP)
Actuals show the imported balance. Budget and Forecast calculate A/R as 45 days of revenue.
- The formula has to skip every scenario that receives imported data, usually Actuals and any other scenario with Actuals write protection. If it would calculate over imported values, it won't save.
- The same rule applies when you map a source account to an account that already has a formula.
Avoid circular references in driver models
Cube checks for circular references across all scenarios at once. So if Account A uses Account B in Actuals, and Account B uses Account A in Forecast, Cube rejects the pair even though the two never calculate in the same scenario.
Split the model into three accounts instead:
- An input account that holds the driver you enter, with no formula.
- A calculation account that uses the input.
- A reporting account that uses IF on Scenario to show the calculated value in Actuals and the planned value everywhere else.
Things to know
- SKIP works in Account formulas. A formula set on a Scenario member doesn't accept publishes, even in skipped cells.
- Write-protected scenarios stay protected. SKIP doesn't change write protection, permissions, or data access.
If something is not right
| What happened | What to check |
|---|---|
| A value you entered shows in its cell but not in totals | The formula uses Sum leaf values, then calculate. Switch it to Calculate leaf values, then sum. |
| Some rows were left out of a publish | Those cells are calculated by the formula. Only skipped cells accept values. |
| A formula that uses the SKIP account is blank | Nothing has been entered in the skipped cell yet. Enter a value, or have the other formula handle the blank with IF. |
| Dimension formula cannot reference itself or its parent | Two formulas reference each other, possibly in different scenarios. Use the three-account pattern. |
| Scenario based formulas cannot be referenced in other formulas | Replace the scenario-based formula with IF on Scenario and SKIP. See Calculate in Actuals, plan by hand. |
| Cannot be a formula since it is mapped to a General Ledger dimension | The account receives imported data and the formula would calculate over it. Make sure the formula skips every scenario that receives imported data. See Keep imported actuals and plan with a formula. |
| Is a formula dimension and cannot be written to | The publish targets a formula on a Scenario member, or a calculated cell. Publish into skipped cells of an Account formula. |