Move spreadsheet formulas to Cube – Cube

If your team recalculates the same spreadsheet formulas every month, such as gross profit, commissions, or a bonus accrual, move them into Cube. A Cube formula is a dimension member whose value Cube calculates from other members. It updates whenever the numbers it uses change, and everyone who fetches it gets the same answer.

One Cube formula also replaces a whole block of copied cells. You write it once, and Cube applies it to every department, entity, month, and scenario. In a spreadsheet you'd have to drag it across each one.

Before you start

You need the same permissions you use to create formulas in Cube today.
Write the business rule down in a sentence first, for example "Gross profit is revenue minus cost of goods sold" or "Sales gets a 15% bonus, everyone else 7.5%." That sentence usually tells you which pattern below to use.
Note what the spreadsheet formula points at. Cell references become dimension member names in Cube, so
B5
becomes something like:
"Revenue"

Translate your spreadsheet formula

Find the pattern that matches what your spreadsheet does, then write the Cube version. Member names go in straight double quotes, and a Cube formula doesn't start with =.

Your spreadsheet does this	For example	Write this in Cube	Learn more
Math between rows	`=B5-B6`	`"Revenue" - "Cost of Goods Sold"`	Common formulas
Multiply by a fixed rate	`=B5*0.05`	`"Revenue" * 0.05`	Common formulas
Point at one fixed cell, such as a total or an assumptions tab	`=B5/B$20`	`"Revenue" / "Revenue"("Entity": "Global Co")`	Criteria
IF on a value	`=IF(B5>0, B5*0.1, 0)`	`IF("Revenue" > 0, "Revenue" * 0.1, 0)`	IF
IF on which row you're in, such as the department	`=IF($A5="Sales", C515%, C57.5%)`	`IF("Department" = "Sales", "Salary" * 0.15, "Salary" * 0.075)`	IF
IF on a group of rows	A list of departments in an IF	`IF(ISCHILDOF("Sales + Marketing"), "Salary" * 0.15, "Salary" * 0.075)`	IF
AND / OR	`=IF(AND($A5="Sales", $B5="UK"), ...)`	`IF("Department" = "Sales" AND "Entity" = "GCo UK", ..., ...)` AND and OR go between the conditions, not around them.	IF
Different formulas for actuals and plan columns	One formula in the Actuals columns, another in the Forecast columns	`IF("Scenario" = "Actuals", "Revenue" / "Customer Count", "ARPU Driver")`	Scenario-based formulas
Formula cells someone types over	Typed numbers on top of a formula	`IF("Scenario" = "Actuals", SKIP, "Bookings" * 0.85)`	SKIP
VLOOKUP or XLOOKUP	`=VLOOKUP(A5, Rates!A:B, 2, FALSE)`	`TABLE_LOOKUP("Department Bonus Breakdown", "Bonus %", "Department")`	Tables
SUMIFS or COUNTIFS over a list	`=SUMIFS(Roster!D:D, Roster!B:B, A5)`	`TABLE_SUM("Employee Roster", "Annual Salary", "Department")` Over cube data, you usually don't need one. Parents already add up their children.	Tables
Previous month	`=C5` (the column to the left)	`<MONTHS_AGO("Customer Count", 1)>`	Common formulas
Year to date	`=SUM($C5:N5)`	`<YEAR_TO_DATE("Revenue")>`	Common formulas

What doesn't carry over

Text results. A Cube formula always returns a number. Keep labels and flags in your spreadsheet.
Circular references. A formula can't depend on itself, directly or through other formulas. Split the logic into separate accounts.
One-off exceptions typed over a formula. Don't type over the formula. Use
SKIP
for the cells you want to enter by hand. See Mix Calculated and Manual Values With SKIP.

Add the formula in Cube

In your Workspace, go to Formulas and select + Formula. You can also add a formula member from the Dimensions page.
Enter a Formula Name. This is how the formula appears in reports and in your spreadsheet.
Select a Parent. The formula sits under this member in your hierarchy, and it can reference other members in the same dimension.
(Optional) Add a Description. Paste in the business rule you wrote down so the next person knows what the formula is for.
Write the formula in Formula Logic. As you type, Cube suggests members, tables, columns, and functions. You can also use Insert Dimension and Insert Functions to add them for you. Names that match turn green, and names Cube doesn't recognise turn red.
Leave Apply To set to All Scenarios unless you have a reason to change it. See Formulas that differ by scenario.
Choose the Rollup logic. See Choose the rollup logic.
(Optional) Choose a Rollup Type: sum, average, ending balance, or beginning balance. This controls how the formula adds up over time. Learn more about rollup types.
Select Save Formula.

For more on the editor, including suggestions, Insert Dimension, and cleaning up long formulas, see Use the Formula Editor.

Choose the rollup logic

Rollup logic decides whether Cube adds up first and then calculates, or calculates at the lowest level and then adds up. It matters most for totals, such as All Departments or a full year.

If the formula...	Choose
Is a ratio, rate, or margin, such as `"Gross Profit" / "Revenue"`	Sum leaf values, then calculate
Reads a fixed member with criteria, such as a share of total	Sum leaf values, then calculate
Uses IF on a dimension, SKIP, or MONTHS_AGO	Calculate leaf values, then sum
Uses a Table function	Always Calculate leaf values, then sum. Cube sets it for you.
Only adds and subtracts, such as `"Revenue" - "Cost of Goods Sold"`	Either. Both give the same totals.

If the cells look right but the totals don't, the rollup logic is usually the reason. Learn more in the Guide to formulas rollup logic.

Formulas that differ by scenario

In a spreadsheet you might use one formula in your Actuals columns and another in your plan columns. In Cube, put both in one formula with an IF on Scenario:

IF("Scenario" = "Actuals", "Revenue" / "Customer Count", SKIP)

Other formulas can reference a formula like this, so use it for anything else depends on, such as revenue, drivers, or ARPU.

You can also limit a formula to one scenario with Apply To, but other formulas can't reference a formula set up that way. Keep Apply To for final reporting lines that nothing else uses. Apply To can't target Actuals or any scenario with write protection enabled. Learn more about scenario-based formulas and write protection.

After you save

Cube calculates in the background. Values appear once the calculation finishes. Complex formulas can take a little longer.
Fetch it like any other member. Add the formula to a range in your spreadsheet, a report, or a board. You can then delete the old spreadsheet formula.
You can't publish to formula cells, except where the formula returns:
SKIP
Manage it from the Formulas page. Use the menu on each formula to recalculate, edit, or clear it.
Move it in your hierarchy from the Dimensions page, the same way you re-order any other member.

If a formula won't save, or saves but the numbers look wrong, see Troubleshooting Formula Errors.

Related to