2

The latest version of Numbers has a great feature called Smart Categories that are kind of like Excel's pivot tables. I'm using it to group a table by a label and then sum up spending and revenue numbers.

The next thing I'd like to do is calculate the ratio between these two, i.e. a new column that has the value revenue / spending. How do I do this? If I create a new column, the only options are to use built-in summary functions (see below) - I can't enter my own formula.

Example of smart categories:

example of smart categories

Options for "Ratio" column:

options for Ratio column

1

While it's not (currently) possible to place custom values in the column you desire, it is possible to refer to the operands elsewhere, such as by using a secondary table to refer to the cells in question. To make a formula point to a certain category’s subtotal, hold ⌥ option and click on the desired cell.

Unfortunately, this method does require the categories to be entered manually, so it may not be suitable for all situations.

Here is a demonstration using your example table, with a secondary table holding ratio values for each of the categories:

Table 1::$Revenue $test (Subtotal)÷Table 1::$Spend $test (Subtotal)

0

It looks like Numbers doesn't yet support custom formulas here. You can reference those cells from other locations, but you can't actually use them for grouping unfortunately.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .